Blog

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_AskBrent®. Here’s how it looks:

sp_AskBrent® in Action

sp_AskBrent® 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_AskBrent® checked for diagnostics. Here’s Expert Mode turned on with a server having a whole mess of problems:

sp_AskBrent with Expert Mode Enabled

sp_AskBrent® 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_AskBrent®’s download page.

↑ Back to top
  1. Pingback: ?? SQL Server ? Troubleshotting « ????

  2. wow, this looks great. THANK YOU

  3. 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…

    • 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.

  4. follow-up: damn, this is awesome.

  5. Pingback: My links of the week – October 20, 2013 | R4

  6. 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.

  7. 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’.

  8. Pingback: sp_AskBrent | Simon Learning SQL Server

  9. 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.

    • I’m gonna hazard a guess that you didn’t read the documentation. ;-)

      • 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.

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

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

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

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

  13. 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!

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php