4 Free Things to Do with a Slow SQL Server

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:

sp_Blitz results

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:

The @SinceStartup parameter tells you what SQL Server’s wait stats have been since it started up:

sp_BlitzFirst showing wait stats since startup

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:

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.

Previous Post
What Kinds of Raises do DBAs Get?
Next Post
sp_DatabaseRestore: Open Source Database Restore Stored Procedure

13 Comments. Leave new

  • Steve Austin
    March 3, 2017 11:34 am

    “in 1.9 hours, SQL Server has spent 116.8 hours waiting”

    Am I missing something here? Those figures seem a little… Off 🙂

    Reply
  • I probably missed something, but I can’t find sp_BlitzFirst listed in your replied email after I registered my email address (selected all 3 options) and clicked ‘Send me the goods’. Even I received your email reply, but I still can’t find any download link in that email, 3 links in it all point me back to the very similar pages that I find in your ‘Free Stuff’ area. And, this link is also dead: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit

    Reply
  • Are you aware of any issue running this on SQL 2008R2 (10.50.4000)? I was running the latest version of sp_blitz across my Prod servers today to check for problems and when I got to one running 2008R2 the server crashed within a minute of me running it!

    I have just tried the same on the dev, test and UAT versions of that server and UAT crashed as well.

    Prod:
    SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
    SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).
    Error: 17311, Severity: 16, State: 1.
    Problem creating symptom dump file due to internal exception

    UAT managed to create a dump and had a slightly different error:
    SqlDumpExceptionHandler: Process 21200 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}