Step 1. Run sp_Blitz and look for urgent warnings. sp_Blitz is one of the free scripts in our First Responder Kit. Just open the sp_Blitz.sql, run that to create the stored procedure, and type:
It returns a prioritized list of things that are wrong, broken, or dangerous about your SQL Server. Focus on things in priority 1 through 50 – these are the big, urgent issues that can get you fired, or can cause showstopper problems:
If you’ve never run sp_Blitz before, the results can look overwhelming. That’s okay – just read through them one by one, copy/pasting the URL into your web browser for any alerts that you don’t understand.
This is like getting a totally free health check on your server. Often, we find huge performance issues in here, like misconfigured memory or parallelism settings. If you can, fix as much of the low-hanging fruit in here first – often, you can make a huge difference in performance just by working through this list.
Step 2. Run sp_BlitzFirst @SinceStartup = 1 to get your top wait type. This is another script in the First Responder Kit, and it’s focused on performance. Install it by running the contents of sp_BlitzFirst.sql, and then run it:
sp_BlitzFirst @SinceStartup = 1
The @SinceStartup parameter tells you what SQL Server’s wait stats have been since it started up:
When SQL Server is working on stuff, it tracks what it’s been waiting on. The columns:
- WAIT STATS – the thing we’re measuring (if you go down further in the results, we show you more stuff in sp_BlitzFirst – ignore that for now)
- Sample Ended – when you ran sp_BlitzFirst
- Hours Sample – how long the server has been up
- Wait_Type – cryptic, more on this in a second
- Wait Time (Hours) – how long SQL Server has waited on this wait type
Here, in 1.9 hours, SQL Server has spent 116.8 hours waiting on something called SOS_SCHEDULER_YIELD. These wait types have a lot of oddball cryptic names, but here are some of the most common ones:
- SOS_SCHEDULER_YIELD – waiting on CPU cycles
- PAGEIOLATCH* – waiting to read data pages from a data file
- WRITELOG – waiting to write delete/update/inserts (DUIs) to the log file
- CXPACKET – parallelism, and this one’s complex enough that I wrote a post for it
Step 3. Run sp_BlitzCache to find the resource-intensive queries. The next script in our arsenal will examine SQL Server’s query stats. Run sp_BlitzCache.sql to install the proc, and then execute it:
sp_BlitzCache @SortOrder = 'cpu'
In that example, I’m sorting by the most CPU-intensive queries, but you can also sort by reads, writes, duration, executions, and more.
Step 4. Ask for help. Now, you’re armed with great configuration data about your SQL Server, your top wait type bottleneck, and the queries causing that bottleneck. You’re much better equipped to post questions at DBA.StackExchange.com, SQLServerCentral’s forums, or SQLteam.com.
And hey, we’re here for you too. We’re in the business of making slow SQL Servers go faster. We’re not free, though – make sure you give the above stuff a shot first, and if you can’t make the progress you want fast enough, that’s where we come in.