Announcing sp_BlitzFirst® for Troubleshooting Slow SQL Servers

When someone tells you the SQL Server is slow, what do you do?

  • Run sp_who looking for queries that might be blocking someone
  • Check the SQL Agent jobs to see if there’s a backup job running
  • Fire up Activity Monitor looking for problems
  • Remote desktop into the server to look at CPU use
  • Open Perfmon to check your favorite metrics
  • Run a wait stats sampling query looking for the biggest bottleneck

That’s all a lot of work, and I’m lazy. So right now, as we speak, I’m onstage at the PASS Summit unveiling a single stored procedure that does all that in ten seconds, plus more.

You already know how our free sp_Blitz® gives you a prioritized list of configuration and health problems on your SQL Server. Now when your SQL Server is slow, you can find out what’s going on just by asking Brent – with sp_BlitzFirst®. Here’s how it looks:

sp_BlitzFirst® in Action

sp_BlitzFirst® in Action

In this example, I’ve got three problems, and I can click on links to get more details about the specific finding, how to stop it, and the query text.

I can also turn on Expert Mode and see some of the raw data that sp_BlitzFirst® checked for diagnostics. Here’s Expert Mode turned on with a server having a whole mess of problems:

sp_BlitzFirst with Expert Mode Enabled

sp_BlitzFirst® with Expert Mode Enabled

It returns results including:

  • Which wait stats were the biggest during the last five seconds
  • Which data and log files are getting the most reads & writes, and how fast they’re responding
  • Which queries consumed the most resources during the sample

Using the @OutputDatabase parameters, you can also log the results to table. This means you can set up a SQL Agent job to run it every few minutes, and then when someone wants to know why the server was slow last night, you can run it with the @AsOf parameter to get the results as of a past date and time.

To learn more, check out sp_BlitzFirst®’s download page.

Previous Post
#SQLPASS #Summit13 Women in Technology Lunch – Live!
Next Post
Index Hints: Helpful or Harmful?

32 Comments. Leave new

  • wow, this looks great. THANK YOU

    Reply
  • very cool.

    one thing that immediately caught my attention was the “Sleeping Query with Open Transactions”–we have an application that loves to generate these, and while i know instinctively that this is bad, a clear explanation of why would be of particular help right now. i saw this in the screenshot, so naturally i tried the link shown in the output (http://brentozar.com/go/sleeping), but it appears to be dead…

    Reply
    • Well, Mike. I can try to enumerate a few: if the transaction was left uncommitted, it might generate locks in your SQL Server. If you don’t have concurrency issues, but there are MANY open/sleeping transactions and your SQL is targeted by a lot of new connections, you might start to experience problems with the thread limits (mostly in a 32-bit environment). Also, each connection consumes a little bit of memory. Regards.

      Reply
  • follow-up: damn, this is awesome.

    Reply
  • Simon Kingston
    October 21, 2013 10:23 am

    This is fantastic! Thanks for providing great resources like this to the SQL Server community.

    I did have to modify line 637 of the script (it appears that the wrong aliased table reference was used) to change it from “AND s.open_transaction_count > 0” to “AND sessions_with_transactions.open_transaction_count > 0” to get it to successfully run.

    Reply
  • I just downloaded v7, 2013/10/21. I get this error running it on SQL Server 2008 R2 sp2:

    Msg 207, Level 16, State 1, Line 28
    Invalid column name ‘open_transaction_count’.

    Reply
  • This is pretty cool… But when I attempt to use any of the links from the URL field (e.g. http://BrentOzar.com/go/topqueries, or /compile), I get redirected to the home page. Do you want a cookie (I don’t usually accept food from strangers, except on Halloween)? And I’m REALLY curious what wisdom lurks on the hidden pages.

    Thanks.

    Reply
    • I’m gonna hazard a guess that you didn’t read the documentation. 😉

      Reply
      • You’d be correct, but that’s mostly because I wasn’t able to find any (other than inline comments, which are helpful for some things). I did find the following in the comments:

        “#AskBrentResults has a CheckID field, but there’s no Check table. As we do
        checks, we insert data into this table, and we manually put in the CheckID.
        We (Brent Ozar Unlimited) maintain a list of the checks by ID#. You can
        download that from http://www.BrentOzar.com/askbrent/documentation/ if you
        want to build a tool that relies on the output of sp_AskBrent.”

        However, when I followed that link, it got redirected to the /blitz/documentation page – so I assumed the analog for AskBrent wasn’t created yet… Honest, I did look, just didn’t find anything. I figured this was a work in progress, and that’s still progressing.

        Besides, it’s obvious that you’ve gone to extraordinary effort to make this intuitive and self-documenting and idiot-proof (which I applaud). I’m just trying to be the test idiot… maybe production idiot… 😉

        Still cool, still playing with it.

        Reply
      • BTW, I noticed that Mike made the same observation in his comment on Oct. 18 (at 3:26), above.

        Reply
  • Nice work, and I’m awarding you 3 points for the Happy Fun Ball reference…

    Reply
  • Nice work! Well done and thanks for sharing it with the community.

    Reply
  • Does this work on SQL2005?
    When I click anything on HowToStopIt I dont get the link that was mentioned inside.
    Thanks!

    Reply
  • Hey Brent, long time follower, first time responder! This is awesome to say the least! I did make one minor change to my version of the procedure though, as I like to be able to click the query to pop it open in a new window. On line 1473 of sp_Alert, I returned the QueryText back as XML like so:

    CAST(” AS XML) AS QueryText

    Figured I’d toss this out there and let you determine if it’s something you want to incorporate in your version. Keep feeding me great stuff!

    Reply
  • Did I miss this? What SQL Server versions are supported?
    thanks

    Reply
  • Hi Brent,

    I am wondering to know if we run this wonderful SP every like 2 hour for 20 sec and save result in a table ,, in busy environment wouldn’t be affect performance ?

    Reply
  • is it free?

    Reply
  • but where can i download it?
    it isn’t in the dowload pack

    Reply
  • thanks a lot
    fantastic

    Reply
  • Thanks a lot!

    It will help me troubleshoot some incidents on one of my server.

    Would it be possible to execute the sp_BlitzFirts from another server using a Linked Served? When executing it returns the followong error message:

    « Msg 9514, Level 16, State 1, Line 1
    Xml data type is not supported in distributed queries. Remote object ‘IROWSET’ has xml column(s). »

    I guess it would involve rewriting the stored proc using some CAST…

    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":""}