Inside Sysprocesses

The jury's still out about whether directly accessing SQL Server system tables is a good thing. The SQL Server 7.0 documentation states explicitly that you shouldn't access system tables, but the SQL Server 2000 documentation loosens up that advice a bit: Reference of documented columns in system tables is permissible. However, many of the columns in system tables are not documented. Applications should not be written to query undocumented columns directly.

Those of you who read a lot of my writing know that I don't always follow this recommendation. Lots of undocumented columns contain useful information, and if I need to use that information for analysis or troubleshooting, I don't hesitate. Let's look at some of my favorite system tables, then I'll describe how I use information from one of them to troubleshoot system problems.

What about updating the system tables? The documentation stresses that "system tables should not be altered directly by any user." I completely agree with this advice. With older versions of SQL Server, I sometimes had to directly update a system table because they offered no other way to do what I needed. But in SQL Server 7.0 and 2000, Microsoft supplies enough system procedures and other tools to do any system-table modification I've required.

Although the documentation suggests that querying system tables is OK, your applications should use one of the following tools when possible to retrieve information that's stored in system tables:

However, as much as I try to use the supplied tools to access system-table information, I use three system tables extensively when tuning and troubleshooting a system—syslockinfo, syscacheobjects, and sysprocesses. And none of these system tables is a real table at all, but rather they're pseudo tables. A pseudo table is one that doesn't take up any space. You might look at the number of rows to try to determine whether a table takes up any space, but that might not tell you for sure whether the table is a pseudo table. In the master database on my SQL Server, both sysreferences and sysfulltextcatalogs have 0 rows just because I haven't used those tables yet.

To determine whether a table is a pseudo table, you can look at the 11th bit (with a value of 1024) in the sysstat column of sysobjects. You can perform the bitwise AND operation sysobjects.sysstat & 1024, which in binary is all zeros except for the 11th bit. Because 0 AND anything is 0, all the bits except the 11th will be zeros in the output. If the 11th bit is also 0, the result will be 0, but if the 11th bit is 1, it will stay 1 in the output, and the result will be 1024. The following query returns the name of each table, the number of rows in the table, and the result of ANDing the table's sysstat value with 1024:

SELECT name = CONVERT(CHAR(30), o.name), rows,PseudoTable = o.sysstat & 1024, o.typeFROM sysindexes i JOIN sysobjects o ON o.id = i.idWHERE o.id < 100 AND (indid = 0 OR indid = 1)

Of course, the sysstat column in sysobjects is undocumented, so I shouldn't use it, no matter how much fun it is to twiddle with bits. Instead, you can use a system property function called OBJECTPROPERTY as follows to examine the property called TableIsFake:

SELECT Name = object_name(id)FROM sysobjectsWHERE OBJECTPROPERTY(id , 'TableIsFake') =1

If the function returns a 1, the table is a pseudo table.

3 Favorites

Because pseudo tables aren't stored on disk like real tables, you can't perform INSERT, UPDATE, or DELETE operations on pseudo tables no matter how much privilege you have on the system. So, you don't have to worry about being unable to resist temptation. The three pseudo system tables that I use most often—syslockinfo, syscacheobjects, and sysprocesses—all contain information that you can retrieve only by directly accessing them.

Syslockinfo contains a wealth of information about locks that have been acquired or requested, but it's generally unreadable because all the columns are made up of code numbers. I prefer to use the sp_lock procedure whenever possible to get lock information. When I need information from the syslockinfo table that sp_lock doesn't directly provide, I usually write my own procedure based on the code from sp_lock.

The pseudo system table syscacheobjects is relatively readable, but only a couple of its columns, such as usecounts and sql, contain information that's really useful for the troubleshooting I do. I've discussed this table in several previous columns, including "Stored Procedure Plans," October 1999.

The pseudo system table sysprocesses is my favorite. Although the system procedures sp_who and sp_who2 return information from sysprocesses, sysprocesses contains lots of other useful information for which you have to directly query the table. And if you want to do anything with the data other than look at it, going to the table itself is easier than trying to capture the output from sp_who.

Using Sysprocesses

Now let me tell you about columns in sysprocesses that you can do cool things with, based on real troubleshooting scenarios that I've been involved in. Before you can look at the relevant columns, you have to be able to find the right row in sysprocesses. The table's primary key is the server process ID (SPID). You can find the SPID of your current connection by looking at the value for the function @@spid:

SELECT @@spid

If you're running queries from Query Analyzer, the status bar at the bottom of each query window tells you the SPID for that connection. If you're running the problem queries from an application, you have to be creative to determine which row in sysprocesses contains the data you want. With luck, by examining the HostName or Login values that sp_who2 returns, along with the ProgramName, you can isolate one relevant row and note the SPID value in it. When you have the SPID, you can select from sysprocesses only the row for the process you want:

SELECT * FROM sysprocessesWHERE spid = 

A couple of the returned columns I've found valuable are physical_io and cpu.

Sometimes when it appears that a process isn't doing anything, the culprit could be a maintenance job, a long-running query, or some type of utility. Unless the process was designed to return status information, you might not know whether the task is proceeding normally when it doesn't return a response for a long time. If you can determine the SPID, you can find the row in sysprocesses that corresponds to the task and look at the columns that keep track of how much work a process is doing. I realized the value of this technique during one of my first consulting assignments when I was running a RESTORE command and couldn't tell whether the restore was taking place. I watched the physical_io value for the process changing. When I realized that each I/O operation was reading one extent (8 contiguous pages), I could predict to within 5 minutes how much longer the restore would take, based on the size of the database.

Another column in sysprocesses that can indicate that a process is working is the cpu column, which measures the accumulated CPU utilization in milliseconds for a process. This value is accumulated no matter whether the user running the process has set STATISTICS TIME ON to observe the CPU utilization. I used this column in my most recent consulting job when the system I'd been tuning for 3 days suddenly experienced a dramatic drop in overall throughput. The client was scheduled to start running a live auction within 10 minutes, and this slowdown would have been disastrous if I couldn't have quickly resolved it. Rather than take the time to define a trace and capture data that I would then need to analyze, I wrote a quick T-SQL script to capture basic information from sysprocesses, including cpu data, and save it in a temporary table. The script, which Listing 1 shows, then immediately looks at the values in sysprocesses again and computes the change in cpu from the saved values in the temporary table to the current values. The script then reports the change for each process, sorting to show the process with the biggest CPU utilization first.

Because I was in a hurry, I didn't have time to write a driver for this script. I just ran it repeatedly in a Query Analyzer window from the DROP to the ORDER BY, noting which process was at the top of the list each time I ran it. Sure enough, I found that one process was in one of the first two positions every time I ran this script. Because I had captured both the loginame and hostname columns, I could isolate the culprit as someone in the next office. We found that he had just started running a report to which he had made "a few changes." When he canceled the task, the whole system perked up immediately, and the auction went on without a hitch. (Tuning the queries involved in the report then took the rest of the day, but we did that on a development server.)

Next month, I'll look at a few more sysprocesses columns that have proven useful for troubleshooting, especially for finding locking and blocking problems. In addition, I'll look at some new columns that Microsoft added to the sysprocesses table in SQL Server 2000 Service Pack 3 (SP3).