Posted by: Denny Cherry
sp_who3, SQL Server
SQL Server is a damn good product, but it sure isn’t perfect. Like any good product out there people have come up with things that can be bolted onto the core to make SQL Server even better. Without these bolt on parts SQL Server looks a little dull. But these bolt on parts may not make the engine run better, but they make it look a lot better and that makes us want to make the SQL engine run better.
Some of my favorites (in no particular order) include:
The SSMS Tool Pack is a great add on for SQL Server Management Studio. It’ll save you if SSMS crashes by auto saving all those unsaved SQL Scripts for you. It’s got a great feature to help you read execution plans, a way to easily run a script against multiple databases, various templates, and much more.
Michelle (aka SQL Fool) has written a great Index rebuild and defrag stored procedure that anyone who is walking into a shop which isn’t going maintenance can take and throw onto the servers and happily know that the SQL Server will have some good maintenance being done automatically. The script will do rebuilds online when possible, offline when it must and figures out the order that things should be done in.
Adam was written sp_whoisactive and this is probably the gold standard is looking at what is causing SQL statements to wait, getting their execution plans, and a lot more. I’m pretty sure that there is a switch in there somewhere that will tell sp_whoisactive to make me breakfast. Adam has included loads of ways to filter the output so you can quickly and easily filter out all the spids that you don’t care about and get into the ones that you want. You can even control the formatting of the output in a variety of ways so that it fits your needs.
I’m cheating a little on this one, as I’m the one that wrote sp_who3, but it’s my list and I’m allowed to do that. sp_who3 will normally show the same output as it’s mild mannered cousin sp_who2. But when you call sp_who3 and pass it a spid that you are looking for a massive dump of information about that spid is returned. This dump includes the current statement which is being processed, the entire batch which is being processed, all the information formatted like the old sysprocesses table about all the threads for the SPID (very useful when seeing CXPACKET waits) and a ton of locking information. While the output isn’t very pretty, its functional. Personally I use sp_who3 to dig into parallel queries after I’ve done the initial identification of the problem using sp_whoisactive. (While the site only says SQL 2005 as the newest version that version works just fine on anything newer than SQL 2005.
Now go download and install these bolt-ons to your SQL Servers. I’ll wait…
Now that you’ve got all these bolt on parts installed, can’t you see how much nicer it is to work on the SQL engine. It’s easier to get at the information that you need. It’s easier to keep the system up and running. And you want to work on the system more now that it’s prettier and easier to work on. Much like my motorcycle is much prettier now that it has all those shiny parts bolted onto it.