Collect and Baseline Wait Statistics (Dear SQL DBA Episode 14)

What are the best tools to collect and baseline wait statistics? Should you write your own? Watch the 18 minute video or read the episode transcript below. Prefer a podcast?   Dear SQL DBA… I am getting into performance troubleshooting on SQL Server. I hear you talk about wait stats a lot, and how important they are to the process of troubleshooting. What ways are there to check the wait stats for a given time? How would you go about creating a baseline for a system you have just taken over? Sincerely, Waiting on Stats I do love wait stats! If you listened to the performance tuning methodology I outlined in an earlier episode, you saw how important I think wait stats are for troubleshooting performance. If you missed that episode, it’s called Lost in Performance Tuning. (I’ve got an outline of the discussion in the blog post, as always.)…
Read More

Why an Upgrade can Cause Performance Regressions (Dear SQL DBA Episode 9)

You finally got approval to move to new hardware and a fresher version of SQL Server. After months of work,  you do the migration and then… performance gets worse. What can cause this, and what do you look for? This is a “listen-able” 27 minute video. Prefer a podcast instead? Find it at Show notes with clickable links are below the video. A free poster of the concepts is here. Here’s this week’s question: Dear SQL DBA, I recently went through a process of migrating databases on a SQL Server 2008 R2 stand alone instance to a SQL Server 2012 SP2 cluster. The cluster’s servers and SQL Server configurations were built to be as close to identical as possible to the previous instance (memory, cores, disk, maxdop, CTP, etc). After the migration, I noticed that CPU utilization jumped from the normal 25% to a consistent 75%. I did several other migrations…
Read More

Lost in Performance Troubleshooting – Kendra’s Perf Triage for SQL Server (Dear SQL DBA)

The SQL Server is slow, what should you do? I answer a reader question and share my strategy for performance troubleshooting. Note: This is a “listen-able” video. You can also listen to this as a podcast – learn how at This week, our question is about troubleshooting performance Dear SQL DBA, is there any step-by-step script that you would use to start your troubleshooting? My production SQL Server has a problem with memory pressure. Page Life Expectancy is low and lazy writes are high. I heard that Microsoft engineers will use SQL Diag then Nexus to troubleshoot, but I have not had success using those tools. Lost in Performance Troubleshooting You need a strategy Tools change over time – for example, Query Store is a huge new tool in SQL Server 2016 (awesome!) Develop a strong performance troubleshooting strategy Plug tools into your strategy based on the SQL Server version, and whether…
Read More

The #1 Thing to Never Do to Fix a Performance Problem

Never, ever, disable backups to fix a performance problem. If you’re not having a performance crisis, you might look at that sentence and say, “Wow, that’s super dumb! Who would ever do that?” Well, a lot of people, actually. It’s pretty common. When performance gets really bad, people get confused. Are all the backups running making it worse? It’s very common for people to think that disabling backups or making them less frequent might lessen the load on the server. It’s worth testing, right? It’s not.  It’s absolutely possible that backups could be part of a performance problem. You might have hit an IO situation or a bug where backups are exacerbating the issue. But backups exist to protect against data loss. And data loss is worse than poor performance in most environments. (If you’re in the rare environment where data loss isn’t an issue, why were you doing backups anyway?) If…
Read More

Be Prepared: Collecting Data from sp_WhoIsActive in a Table

I am a fangirl of Adam Machanic‘s free sp_whoisactive stored procedure. One of the great things about sp_whoisactive is that it’s very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect the data you want over an interval. What’s sp_WhoIsActive? Sp_whoisactive is a procedure written by Adam Machanic. It can be found at What to Do Today Do your future self a favor: Today, set yourself up the following: Create the sp_whoisactive stored procedure in a user database you use only for administration Play with the script I provide below and look at the  parameters of the stored procedure Save a script which creates a table for your preferred version of sp_whoisactive, then runs in a loop to store the data. Why Do This? Even if you’re already running monitoring tools, using sp_whoisactive to…
Read More

Error Configuring DataCollector: A collection set cannot start without a schedule

Update: Based on Bill Ramos’ comment below and a note on Twitter (thanks!!) I have added some more details which hopefully make the situation clearer. Summary When setting up data collection using the GUI, configuration failed halfway through setup. At the point  of failure I found there were SQL Agent jobs for collections visible, but nothing appearing  in Management Studio under Management\Data Collection. The Data Collection icon still had the ‘off’ down red arrow. (I manually refreshed it for good measure to make sure.) Beware: this isn’t an officially endorsed workaround. It’s just how I found I could make it work. I am testing this on a server which isn’t in production use. My Setup I was configuring the data collector on a SQL 2008 R2 instance (clustered), and pointing to a SQL 2008 (NOT R2) Management Data Warehouse. The MDW is on another server of course– that one isn’t…
Read More