|
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.
ActiveSPIDs
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.
Bill
|