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:
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:
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.
34 Comments. Leave new
wow, this looks great. THANK YOU
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 (https://www.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.
Thanks Stefano. Helps dig further with this pointer.
follow-up: damn, this is awesome.
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.
Doh! Sorry about that, uploaded v8 that fixes it. That line actually needs to be removed altogether.
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’.
Hi Calvin. Sorry about that, v8 fixes it. You can download it now. Thanks!
Thanks!!!
This is pretty cool… But when I attempt to use any of the links from the URL field (e.g. https://www.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 https://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.
Nice work, and I’m awarding you 3 points for the Happy Fun Ball reference…
Nice work! Well done and thanks for sharing it with the community.
Thanks, my pleasure!
Does this work on SQL2005?
When I click anything on HowToStopIt I dont get the link that was mentioned inside.
Thanks!
Luanne – yep, it works on SQL 2005, but not as completely as it works with newer versions of SQL Server.
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!
hmmm… some of the content was stripped out. And it was the content that mattered! I’ll send it via email!
Did I miss this? What SQL Server versions are supported?
thanks
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 ?
Monamy – “busy environment” – can you be a little more specific?
is it free?
Yes.
but where can i download it?
it isn’t in the dowload pack
https://www.brentozar.com/archive/2016/06/first-responder-kit-updated-sp_askbrent-now-sp_blitzfirst/
thanks a lot
fantastic
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…
Unfortunately no, that isn’t available at this time, sorry!
Thank you for answering!
Hello Brent , I am having same issue while running on remote linked server using latest version . Kindly advise . Thank you
Xml data type is not supported in distributed queries. Remote object ‘LinkedServer.sp_Blitz_Log.dbo.BlitzResults’ has xml column(s).
Arun – make sure you read the readme on where to get support.