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 (click on the latest blog post version)
- 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
Jeffrey Langdon September 9, 2010 | 8:30 am
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.
Oscar Zamora September 9, 2010 | 8:30 am
Excellent tool Brent. You can identify offending processes pretty quickly with one command.
THhanks
John Sansom September 9, 2010 | 8:52 am
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!
Michael J Swart September 9, 2010 | 8:53 am
Filmed yourself in a mirror? Or did Pass reverse their Logos on T-Shirts?
Brent Ozar September 9, 2010 | 4:41 pm
Michael – good catch! I flipped the video during production because it made more sense from that side of the screen.
Max Trinidad September 9, 2010 | 9:21 am
Excellent video and like to try out the tool. Good job!
Frank Fernandez September 9, 2010 | 11:08 am
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!!!!
Hussein Yousef September 9, 2010 | 11:22 am
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 !
Brent Ozar September 9, 2010 | 4:40 pm
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!
Adam Machanic September 9, 2010 | 9:21 pm
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
Adam Machanic September 9, 2010 | 11:29 am
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!
Shaun September 9, 2010 | 11:30 am
Wow.. I had heard about this but never used it. Seeing it in action made me a convert. Thanks!
Dugi September 9, 2010 | 11:38 am
BrentO, it’s nice presentation, thanks!
Pingback: Build your own PC TP. 2: Find the most? These n? Necessarily
Fabricio Lima September 9, 2010 | 8:55 pm
Excelent information. I will use this in my environment.
Thanks
Dugi September 10, 2010 | 4:52 am
Thanks BrentO, short, nice & valuable presentation!
Pingback: SQL Server Links and news the week 10/09/10 | John Sansom - SQL Server DBA in the UK
Matt September 10, 2010 | 9:34 am
The query plan for me comes up as XML – how do you get it to show as the iconic exe plan?
Thanks
Adam Machanic September 10, 2010 | 12:56 pm
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.
Thirster42 September 10, 2010 | 12:38 pm
boo at not being able to see youtube videos at work!
Michael J Swart September 10, 2010 | 1:17 pm
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).
Adam Machanic September 10, 2010 | 2:18 pm
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
Roberto Reynoso September 11, 2010 | 1:35 pm
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.
Vel September 12, 2010 | 4:14 pm
I will be attending SQL 2008 Admin. training this week.
Is it OK if I share this with the class?
Brent Ozar September 12, 2010 | 4:55 pm
Vel – sure, absolutely! Thanks, glad you liked it.
Guy September 13, 2010 | 6:30 am
Great for SQL2005 and 2008, but what would be a equivalent for us poor souls still supporting SQL 2000?
Brent Ozar September 13, 2010 | 7:12 am
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.
Jesse September 13, 2010 | 3:16 pm
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!
Brent Ozar September 13, 2010 | 3:24 pm
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.
frank September 16, 2010 | 11:01 pm
Why I can not see the video?
Can you post the url of video too.
Brent Ozar September 20, 2010 | 5:11 pm
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.
tayyeb September 23, 2010 | 4:24 pm
excellent post thanks
can i have disk space alert script for sql 2008 please ?
Brent Ozar September 25, 2010 | 10:34 am
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.
tayyeb September 26, 2010 | 12:21 pm
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 
Shaun Stuart September 25, 2010 | 11:43 am
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.)
tayyeb September 26, 2010 | 12:22 pm
Thank you shaun
will wait for that
David Wetherell October 16, 2010 | 2:32 pm
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 …
Brent Ozar October 17, 2010 | 7:26 am
Ha! You’re welcome, glad I could help.
Pingback: SQL Server Links and news for the week 05/11/10 | John Sansom - SQL Server DBA in the UK
Pingback: SQL Server Links and news for the week 05/11/10
Pingback: Kai Borgolte » Blog Archive » How to Find Slow SQL Server Queries