SQL Server isn’t always perfect

Published On: 2012-02-16By:

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:

1. SSMS Tool Pack

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.

2. SQLFool’s Index Rebuild / Defrag Script

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.

3. sp_whoisactive

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.

4. sp_who3

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.


Contact the Author | Contact DCAC

2 responses to “SQL Server isn’t always perfect”

  1. Eyespi says:

    I can’t download your sp_3 file because Trend Micro has your site classified as pornography. You might want to get that corrected…

  2. Mrdenny says:

    That’s awesome, thanks for the info. If it’s your company install of Trend the sysadmins can fix that on their side.


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
American Business Award 2021 Gold    American Business Award 2021 Bronze    FT Americas’ Fastest Growing Companies 2021   INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award
FT Americas’ Fastest Growing Companies 2020       Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award
Share via
Copy link