Using Master..Sysprocess to Isolate Performance Issues - Part 1
It's 4:30 on Friday and your boss comes to you in
a panic. The e-commerce system is down, and the database is to
blame. You go through your usual what's-going-on checklist and all you can
tell is the CPU on the SQL Server is pegged at 100%. You check Profiler,
but no high CPU commands seem to hitting the database. Looks like it's
going to be a late Friday.
Checking the Sysprocesses table
Finding the solution to this weekend killing
scenario is often quite simple. The sysprocesses table in the master
database holds information about each unique process, or SPID, running on the
SQL Server. To view information about these processes, run the following
command in Query Analyzer:
select * from master..sysprocesses
The output will be a row for each
unique SPID on the server. SPIDs 1 thru 50 are reserved for internal SQL
Server processes, while SPIDs 51 and above are external connections.
Finding high CPU activity
Since the issue appears to be a
CPU intensive process, you will be particularly interested in the CPU
column in the result set. The value in this column is cumulative over the
period the process has been connected to the server, so a high value doesn't necessarily
indicate a problem. However, combined with the status
column, you may be able to narrow down on the particular process that is causing
you to stay late on Friday. If the sysprocesses
table holds many rows, narrowing down to processes that are currently active as
follows might be helpful:
select * from master..sysprocesses where status = 'runnable' order by CPU desc
The status = 'runnable' clause
will return only processes that are currently running. You can now look at
the top couple of rows that were returned. Do any have values in the CPU
column that seem excessively high? Generally, a value in the thousands
might indicate a problem.
Assigning blame
Now that we have isolated the
process that is causing the pegged CPU scenario we can look further into exactly
what this process is. The following columns in the master..sysprocesses
table will help shed light on the culprit:
loginname
- The login used to connect to SQL for this process. This can be a SQL
Server login, or a Windows domain account.
hostname
- The NetBios name of the computer where the process originated.
program_name
- The name of the application that opened the connection
Furthermore, the exact command
currently being run can usually be determined. On SQL 7.0 or SQL 2000 sp2
or below servers, the following command will output the first 255 characters of
the command currently being run for a particular SPID:
DBCC INPUTBUFFER (spid) -- replace spid with the actual numeric value
The above command will also work
on SQL 2000 sp3 and SQL 2005 servers (with SQL 2005 up to 4000 characters may be
returned). These SQL editions have three additional columns in the master..sysprocesses
table: sql_handle,
stmt_start, and
stmt_end. These
columns can be used to obtain additional
information on the command
being run as follows:
DECLARE@handle binary(20) SELECT @handle = sql_handle FROM master..sysprocesses WHERE spid = SPID -- replace with actual numeric value SELECT [text] FROM ::fn_get_sql(@handle)
Now that you know who is running what from where
and why it is pegging your CPU, you may choose to contact them and ask them to
stop their process, or if you are really ready to start your weekend, terminate
the process using the KILL command:
KILL SPID -- replace with actual numeric value
Conclusion
The sysprocesses
table holds valuable information on the processes running on your server.
There are several SQL management tools on the market today that make great use
in leveraging this information to generate all kinds of useful reports on your
databases - Periscope for SQL Server by Highwire Development and i/3 by Veritas
are two examples.
In Part 2 we will examine how to
use the sysprocess
table to create an automated job to monitor another common performance
bottleneck - blocking. Until then, enjoy your Friday.
