SQL Server database administrators need to be able to quickly find out what queries and stored procedures are running slow. Microsoft includes sp_who and sp_who2 in SQL Server 2005 and 2008, but there’s a much better tool, and it’s completely free.
In this five minute tutorial video, I explain how to use sp_WhoIsActive from Adam Machanic (Blog – @AdamMachanic). Sorry about the audio – I’ve ordered a new microphone and it hasn’t arrived yet.
Where to Download sp_WhoIsActive
- You can download sp_WhoIsActive for free
- Read Adam’s posts about sp_WhoIsActive
- If you like it, tell Adam by leaving a comment on his blog
- How to collect sp_WhoIsActive results into a table over time for troubleshooting history
Brent Ozar
Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.
Follow Me:
Excellent post. Thanks Brent and Adam.
BTW…Video quality at full screen was fantastic. I had no problems reading all the info on the screen.
Excellent tool Brent. You can identify offending processes pretty quickly with one command.
THhanks
Great walk-through Brent and a big thank you to Adam for the considerable development effort that has gone into creating this awesome tool over the years.
sp_WhoIsActive is an awesome tool for the busy Production DBA and is often my first port of call when troubleshooting server. It gives you the power to get an immediate insight into what’s happening on any given server within seconds.
Best of all, it’s FREE!
Filmed yourself in a mirror? Or did Pass reverse their Logos on T-Shirts?
Michael – good catch! I flipped the video during production because it made more sense from that side of the screen.
Excellent video and like to try out the tool. Good job!
Great job!!! We did put this to work right away at work!!! Awesome tool!
And no problem with the video… keep up the good work for the rest of us… accidental DBAs!!!!
Great job!!! Thanks, but didn’t give me any results ! I don’t understand why, although sp_who and sp_who2 gives me 50 rows !
Hussein – if you rewatch the video again closely, you’ll notice that sp_WhoIsActive only returns results for users that are – well, active. SP_Who and SP_Who2 return all sessions, even if they’re not doing anything. Hope that helps!
Not only that, but Who is Active “aggregates” the data, returning only a single row PER REQUEST. Not so for sp_who or sp_who2, both of which return one row PER TASK. Meaning that if your request has 20 tasks spun up in parallel, you’ll get back 20 rows for it, and almost all of that data will be identical. Noise.
One of the primary goals of Who is Active is to eliminate noise, chatter, and cross-talk and give you the data that really matters. No news is good news in this case; if you see no results when you run the thing, it means that you have nothing to worry about
Thanks, Brent, for putting the video together!
One thing I’ve added to help new users is online help:
EXEC sp_whoisactive @help = 1
Enjoy!
Wow.. I had heard about this but never used it. Seeing it in action made me a convert. Thanks!
BrentO, it’s nice presentation, thanks!
[...] SQL Server DBA Scripts: How to Find Slow SQL Server Queries … [...]
Excelent information. I will use this in my environment.
Thanks
Thanks BrentO, short, nice & valuable presentation!
[...] SQL Server DBA Scripts: How to Find Slow SQL Server Queries – Brent is back and is it just me or does he seem to be a tad bit more cocky. Dam, so would I be had I landed an awesome gig with the clever folks at SQLSKills! Glad to have you back bogging regularly. [...]
The query plan for me comes up as XML – how do you get it to show as the iconic exe plan?
Thanks
If you’re using the 2008 version of Management Studio, simply click on the XML. If you’re still in 2005, upgrade!
Or… Click on the XML, click “save as,” save it to a file with a .sqlplan extension, close the XML, find the file in Explorer, double-click it…
Much easier to upgrade.
boo at not being able to see youtube videos at work!
Adam, you know what would make sp_whoisactive even more awesome?
More testimonials (like this blog post) or something that helps demonstrate that sp_whoisactive is a great tool to use on production dbs (especially on production dbs).
More awesome because it would help increase adoption, especially with those that raise an eyebrow at anything-not-developed-here (third party stuff on prod).
Hah! Couldn’t agree more. Thanks to Brent’s video I’ve received 200 downloads in the last two days, which is approaching a record. These are probably all new users too. Good stuff. Feel free to post your own video
I am a DBA to-be and from just watching the video I have confident in the tool. Thanks a lot Brent for putting together the video with such a great clarity and simplicity. Also, thanks a lot Adam for developing such a wonderful tool.
I will be attending SQL 2008 Admin. training this week.
Is it OK if I share this with the class?
Vel – sure, absolutely! Thanks, glad you liked it.
Great for SQL2005 and 2008, but what would be a equivalent for us poor souls still supporting SQL 2000?
Guy – unfortunately, most of this data wasn’t available in SQL Server 2000. This is one of those scenarios you can use to sell your manager on upgrading to a supported version. There’s not a lot of people writing utility code for unsupported versions of SQL Server anymore.
excellent post – this proc is great! I was using a simple combination of sys.dm_exec_requests and sys.dm_exec_sql_text to get this stuff, but Adam’s proc is so much better. I love that it can get the plans too!
Thanks everybody, glad you liked it. Hats off to Adam for building such a slick tool and giving it to the community. He’s a true MVP.
Why I can not see the video?
Can you post the url of video too.
Frank – if YouTube and Vimeo are blocked by your company firewall or if you don’t have Flash installed, you won’t be able to view the video.
excellent post thanks
can i have disk space alert script for sql 2008 please ?
Tayyeb – sorry, I don’t have one of those. I tend to do disk space alerting at the OS level, not inside SQL Server. The Windows admins need to be monitoring that for *all* servers, not just SQL Servers, and I want to avoid reinventing the wheel.
Hi Brent,
Thank you for the information
i am a regular visitor for your site.i enjoy reading all your articles.hope you enjoyed your ALASKA trip 
and will be bothering you whenever i need help on SQL Server 
Tayyeb – As luck would have it, I wrote an article about that that will be published at SQL Server Central a week from Monday (on 10/4). I’ll link to it on my blog when it comes out. (I’d post it now, but they’ve got a 90 day exclusive.)
Thank you shaun
will wait for that
This is very cool, trims out all the fat … Thanks to Brent for the heads up and Adam for the great script … definitely evil genius status …
Ha! You’re welcome, glad I could help.