It’s 4:30 P.M. 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 percent. You check Profiler, but no high CPU commands seem to be 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 to 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. You can use these columns 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 sysprocesses table to create an automated job to monitor another common performance bottleneck — blocking. Until then, enjoy your Friday.
]]>