Current Activity from Query Analyzer

By Bill Wunder


One always interesting and often lively discussion among SQL Server administrators and developers centers on the preferred use of GUI tools or scripts to interact with the database. I have more than a little to say on that topic. First let me settle the question: Scripts.


So now that you know I’m not undecided in the debate I’d better confess that from time to time I use Enterprise Manager to find answers. It is a rare occurrence when using Enterprise Manager is faster than running a system stored procedure or query to get an answer but it is without a doubt much less effort, almost mindlessly easy, to do research using Enterprise Manager. Browsing the Distributed Management Objects (DMO) collections in Enterprise Manager (EM) is primarily a “click and wait” operation while researching issues in Query Analyzer requires a somewhat deeper understanding of the database architecture and quite a few more keystrokes. Even the most adept EM GUI dependent DBA is limiting their ability to be a strong problem solver.


To actually make changes is another thing all together. If you work in a software lifecycle environment with a development, test and production environment you cannot afford the cost of not being able to reproduce any change you make. Using Enterprise Manager as a promotion tool is the short path to chaos. Only scripts can assure repeatability, yada... yada…yada….


On the Fly

The point for this article is that I’m usually in Query Analyzer when something goes awry with the systems. For me to load Enterprise Manager and connect to the server in crisis, open the Current Activity and see what’s happening isn’t usually quick or convenient. Admittedly, I usually do have it loaded so that part doesn’t take all that long. If I do have to load it, I can expect to set and wait a few minutes with I connect to the server in trouble and wait for DMO collections to enumerate on my local system.  Instead I find it more useful to connect to the server in Query Analyzer and hit [CTRL][F1] to execute a stored procedure that produces a distilled snapshot of what’s happening on that server. [CTRL][F1] is a hot key I have defined to execute my preferred view of the current activity on a server. Query Analyzer has significantly less overhead.


The Query Analyzer connection is just a connection, not a download into the DMO object model. The query is just a query not a refresh of some portion of that object model. On some rare occasions the difference between connecting and getting to the first useful bits of detail in Query Analyzer and Enterprise Manager is small. If the server’s CPU is pegged or I’m hitting the server over a VPN connection the Query Analyzer will always help get me to the problem faster.


Many times I can come to a conclusion with only the information I have in the Query Analyzer result pane. If I need more information I’m in a good place to run additional queries to further research the problem. If I want to refresh I just hit [CTRL][F1]. Conversely, if I don’t see the problem right away in Current Activity I’m often left to click and wait my way around Enterprise Managers it wanders through the DMO collections that must be traversed to get from Current Activity to my next avenue of research. Furthermore, if I want to refresh the Current Activity I have to reload the entire collection and reposition my view to see what ahs changed in the last few seconds. For me, the flexibility and speed of Query Analyzer as compared to Enterprise Manager a compelling reason to do most of my research in Query Analyzer.



What I include in the query executed by the [CTRL][F1] hot key suits me. As I always try to convey, what you use should suit you. My hot key executes a standard stored procedure I have deployed to the admin database on all my SQL Servers. (Please see my earlier discussion concerning my use of an admin database in “T-SQL Tool House”) In that stored procedure I like to show everything that’s “runnable” or blocking or blocked. Combined, these three spid qualities seem to do a good gob of defining the active processes on the system so I call the stored procedure “ActiveSPIDs”. I like to be able to tell what is consuming resources, and if something is blocking I find it useful to know what that spid is executing. Naturally all this info is in master.dbo.sysprocesses virtual system table and can be displayed using sp_who or sp_who2, but when you have hundreds of spids connected to a server, dumping the system table to the screen or running the system stored procedures that do a better job of formatting the information can be a lot to wade through – akin to Current Activity but without the sort capability of Current Activity. The stored procedure I use tries to drill right to the salt dome of the issue.


In order to determine if something is consuming resources it is often necessary to get two samples and calculate the difference between them. I find CPU and disc I/O of particular interest in this regard. One trick I’ve used in the past to see if a SPID is consuming resources is to provide that spid as a parameter to sp_who2, execute a few times, and note if those values are changing or not. In the ActiveSPIDs stored procedure I use a permanent table to store the first sample and then update that table with the next sample. There are a couple of reasons for using a permanent table. First, it allows more than one person to see what is consuming resources without everyone having to execute the procedure multiple times. Second, it gives me something to query if I want to send an email about what is happening or if I want to look back after the fire is out to better assess a long term strategy for the crisis.


I’ve tried to keep ActiveSPIDs from consuming any more server resources than necessary. I specify the with (nolock) hint when I query the system tables. Only when blocking is a current symptom will ActiveSPIDs even attempt to produce useful details about blocks. Only with blocking am I’m interested in knowing how long processes have been waiting and what object is the source of contention. Also only with blocking will I want to know what the blocking process is trying to do. When ActiveSPIDs does detect blocking it produces a second row set in the result pane that shows the current blocking chain with the relevant details about processes involved in the block. Then, and only in the case where the block hasn’t resolved and the blocking process disconnected, the input buffer for the blocking SPID is dumped to the end of the two row sets. As you can see, a lot of query processing happens if there are blocks, but only if there are blocks.


Custom Shortcuts in Query Analyzer

Adding ActiveSPIDs as a “hot key” in Query Analyzer requires that you compile the procedure (duh) and then in Query Analyzer Open the Tools menu, go to the Custom tab, and place a fully qualified call to the procedure in any blank input box. While you're in there you may want to put calls - or scripts if they are short - for some of your other frequently used DBA tools in the other empty input boxes. The only fixed hot keys are calls to sp_help - [ALT][F1], sp_who[CTRL][1], and sp_lock[CTRL][2]. That leaves 9 that you can customize as you like. I wish they didn’t force me to keep sp_who as a hot key because I am so used to sp_who2. Some procedures that you call from a hot key will accept what ever you have highlighted in the query pane or the results pane as a parameter (for example sp_help and sp_helptext) other things will only execute as entered in the input box with no regard to what is highlighted (for example xp_readerrorlog or any user defined stored procedure). You’ll just have to play around with what you put in a hot key to see how it behaves.


You can check out my script to create the stored procedure ActiveSPIDs. As a reminder, please note that this script expects a database named admin to already be there for it as do most of the other tools I have posted.