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, InstantDoc ID 6113.
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 sysprocesses
WHERE spid = <spid of interest>
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).
End of Article
Prev. page
1
[2]
next page -->
