We are releasing a new FREE tool for you to use with your SQL Server instances. Our sp_CheckActivity page explains the requirements, parameters, download instructions, and more, and should answer most questions about using the tool. In this post, Andrew Kelly explains a bit more about why he created it, and how he uses it.
What is sp_CheckActivity, and why did I create it?
I initially created the first version of this utility many years ago as an alternative to the immensely popular tool developed by Adam Machanic called sp_WhoIsActive. I wanted something that would suit most of my everyday needs for a snapshot type of view of the current activity on a SQL Server instance.
I know what you are thinking: why couldn’t I just use sp_WhoIsActive?
Well, I have used it for many years and in some cases still do when a special need arises. The main motivator for creating sp_CheckActivity was not for lack of features but…it’s more like there can be too many.
sp_WhoIsActive has always been a great tool and still is. Adam did a fantastic job all those years ago when he gave the SQL Server community a tool like no other at the time. I am sure it is used by many tens (maybe even hundreds) of thousands of users around the world, and that is a testament to just how useful and powerful it is to this day.
However, I found that for my daily work environment it could be a little too intensive on very busy systems. These were 200+ core machines with 4 to 8TB of memory and thousands of users and today’s servers can dwarf even those. What we found was during the peak periods of activity, sp_WhoIsActive could take many minutes to return the results even with mostly default parameters. As such, I needed a way to capture similar data with less overhead and faster results and thus my custom tool was born.
Did it speed up the results? Under low to medium workloads there is little noticeable difference in most cases. However, under more extreme workloads I found it could return results in a fraction of the time such as seconds vs minutes. I purposely didn’t list exact specifications here as workloads vary dramatically and YMMV. Why is this, you may ask? In a nutshell, it is because sp_CheckActivity simply does not have as wide a range of functionality as sp_WhoIsActive, and in some cases does not dig as deep into the DMV’s that can give us so much information as to what is happening.
What were the design goals?
Before I started to develop the tool, I wrote down what metrics I needed to be reported on for most of my daily activity monitoring needs and started from there. Again, keeping in mind that this was never meant to be a full replacement for Adam’s tool that has so much capability. I was looking for lower overall overhead that still reported on the metrics I most cared about day to day.
An example of this is the ability of sp_WhoIsActive to report wait times by thread. While that is awesome and can certainly be useful, I found in most cases I did not need to go down to that level, and thus did not want the extra overhead it took to get that information.
You will notice sp_CheckActivity has a similar look and feel, if you will, to sp_WhoIsActive. But it was written from scratch to meet my immediate needs, and much of that same data is collected and reported on although not always in the same way. For instance, I report current and historical TempDB usage in GBs instead of pages.
I never intended this tool to be public, but I have received many requests to do so and, well, here we are. And more recently I was asked to add functionality which I agreed to, since it did not add any significant overhead and was able to make the tool even more useful for other folks. The most recent additions (outlined by their parameter names) are listed below.
Note that none of these are run by default and thus do not incur any additional overhead unless specifically invoked.
What is included with the “new functionality”?
@ShowProgress – specifically returns results for any operation in which the % Complete metric is populated in the sys.dm_exec_requests DMV. Using @ShowProgress not only shows the % Complete value, but also how long it has been running and the estimated completion time in hh:mm:ss. According to the Microsoft documentation these are the types of command in which this information is exposed, so we can take action:
ALTER INDEX REORGANIZEBACKUP DATABASEDBCC CHECKDBDBCC CHECKFILEGROUPDBCC CHECKTABLEDBCC INDEXDEFRAGDBCC SHRINKDATABASEDBCC SHRINKFILERECOVERYRESTORE DATABASEROLLBACKTDE ENCRYPTION
@ShowBlocking – this is a simplified but useful option to show any blocking that is occurring with the Lead Blockers separated from the sessions being blocked. This serves me well in the majority of blocking cases I deal with.
@ShowTopProc – normally the results will show the individual statement that is currently running along with the Batch / Procedure / UDF in which it was immediately called from. With this option it will also show the outermost object vs. just the parent object. This can help find the calling chain when you have lots of nested calls.
Summary
If you are already familiar with sp_WhoIsActive, please try sp_CheckActivity on its own terms. Think of it as a general-purpose lighter-weight tool for daily activity monitoring tool rather than a direct replacement. It should fit many environments, but you may may need a specific functionality this may not have and that is fine. No single tool fits every need otherwise we would not be here today.
I would like to hear what features you’d find useful and I would love to hear about them. You can leave a comment in this post, and if you discover a bug or would like to request a feature you can note it on the Issues page in the GitHub repository. However, please keep in mind the original design goals were to be relatively lightweight, so we want to ensure this doesn’t get bloated over time.
I hope many of you find this tool useful.