SQL Server Troubleshooting: Who is Active

    I checked ReplTalk’s post on “waiting” statements, realizing that I had to write a similar query when troubleshooting performance of running queries in general, respectively blocking issues in particular. The novelty resides in Adam Machanic’s comment to the respective post, the link leading me to sp_whoisactive stored procedure which provides a collection of wait stats, lock information, outer command/batch, etc. The procedure is impressive from the point of view of its complexity (and number of lines), the author spending many hours on this piece of art. The parameters are well documented but not the logic, some additional information could be gathered from the several posts on this topic, however that won’t make easier reviewer’s work. After a first review I’m missing a few examples and scenarios in which to use the procedure, without them I’m kind of starting from 0. 

     I am a little reticent in using other people’s code when the code is too complex and requires too much time to be understood, especially when the respective code is not documented, requires a whole manual and/or the code isn’t easy to use. These aspects don’t decrease the merit of the person who wrote it but it decreases the overall utility of the respective code, because when people don’t understand the usability and/or range of applicability of a piece of code, the risk of people misusing the respective piece of code is quite high. From my perspective I find it more useful to have in place a set of queries and procedures that help you troubleshoot performance issues than having a stored procedure that attempts to do everything, requires many parameters, time to be debugged and understood. 

   SQL Server lacks in documentation describing in detail how its internal actually work. The stored procedure could be used to understand how various situations were handled, one of the first problems residing in identifying the piece of code corresponding to each scenario, “isolate” it and make most of it. Most probably a developer who’s trying to understand the respective code will need to break down the code in chunks, like I did, and use them in isolation in order to prove code’s logic. Sure, the followed approach depends also on each person’s skills, level of understanding and exploration techniques. As too much philosophy is not good, especially when divagating from the subject, I will end by inviting the reader to look over the respective stored procedure and, why not, to improve it, eventually derive more knowledge out of it.