The sp_who stored procedure is one of the most commonly used items in a SQL Server DBA's toolbox. Sp_who provides information about many crucial SQL Server user connections and processes such as login name, host name, database, command, and process ID. But sp_who displays results in a cumbersome format that contains excessive white space and forces users to waste time looking for the information that's important to them. Since SQL Server 7.0, a similar but undocumented stored procedure, sp_who2, has sneaked into the master database. Sp_who2 targets some of sp_who's weaknesses by removing certain columns, adding others, and displaying them in a more compact format. Thanks to these improvements, sp_who2 has gradually gained popularity. However, both sp_who and sp_who2 have a couple of serious drawbacks: They have limited filtering capabilities and they don't show blocks clearly. In an effort to streamline my own process analysis, I decided to write my own, modified version of sp_who.
Any DBA who works with sp_who and sp_who2 must work within the limitations of those stored procedures. First, the procedures provide only limited filtering. For example, sp_who lets you filter by login name, but sp_who2 doesn't. However, on large, busy SQL Servers, a DBA can't (and doesn't need to) look at all connections. Filtering out unwanted information lets DBAs quickly get the information they're interested in. Second, neither member of the sp_who family can show blocks clearly. A blocking chain usually includes one or more culprit server process IDs (SPIDs) that cause all subsequent blocks. (For information about detecting blocked processes, see Kalen Delaney's article "Track Down Troublemakers," August 2003, InstantDoc ID 39453 and the SQL Server Books Online—BOL—section "Understanding and Avoiding Blocking.") Detecting and fixing the blocked process at the head of the chain is essential to solving blocking-related database-performance problems. (For an explanation of how to solve blocking problems, see the Microsoft article "Resolving blocking problems that are caused by lock escalation in SQL Server" at http://support.microsoft.com/default.aspx?scid=kb;en-us;323630.)
Sp_who and sp_who2 also have other minor limitations. For instance, the parameter sp_who 'active' lists what BOL calls "active processes," which always include a dozen or so system processes such as Task Manager, Signal Handler, Log Writer, and Lazy Writer—processes that aren't necessarily important to daily administrative tasks but are vital to SQL Server, so DBAs can't leave them out of sp_who's results. Taking these SPIDs out of the list of runnable processes gives you a clearer look at what's going on in your databases. And I was surprised to find that sp_who2's loginame parameter doesn't filter results by login name, as its name implies. In fact, it doesn't filter anything. A close inspection of the code revealed that the login filtering part of the stored procedure is commented out. Well, quirks like this one are probably why sp_who2 is undocumented.
The stored procedure I wrote, sp_who1, is a modified version of sp_who and sp_who2 that works with SQL Server 2000 and 7.0 and has more input parameters to filter out unwanted information. In addition to inheriting the beneficial features of its predecessors, the new stored procedure provides a flexible way to investigate SQL Server connections by SPID, status, login, command, database, host, last batch, program, or any combination of these factors. Sp_who1 also helps troubleshoot performance by detecting waiting processes and potential blocking.
Introducing sp_who1
Web Listing 1 contains the complete sp_who1 stored procedure. When you download the listing at InstantDoc ID 45034, you'll notice that the code looks significantly different from the sp_who and sp_who2 code you're used to. Let's take a look at the features I included.
An impressive feature of sp_who1 is that it takes more than 10 input parameters for filtering. Table 1 shows summary descriptions of these parameters and how to use them. The parameter @spidpool has a variable capacity, allowing an average of 100 SPIDs, which is more than enough for a typical DBA's use. In my experience, if I need to check more than half a dozen SPIDs, I regard the task as more than spot-checking, and I can usually find a better filter to help me. For example, I might filter the SPIDs by the name of the database the SPIDs are connected to or by one user-machine name (hostname) where the SPIDs originate. Also, note that the variables @waittime and @blocked have different meanings when you supply them with a 0 value. When you use @waittime = 0, you see all the waiting processes, whereas @blocked = 0 lists all the nonblocked processes.
Table 2 compares the three members of the sp_who family. Without input parameters, all of these stored procedures will query the master.dbo.sysprocesses table with no filtering or sorting. When you want to sort with sp_who and sp_who2, you're limited to sorting by SPID. But I think sorting on columns other than the spid column makes more sense because a SPID is less meaningful than other identifiers such as database name or login name. I wrote sp_who1 so that it sorts results by database, login name, status, and command—an order that covers most of my routine needs. I decided to filter out system background processes because I believe few DBAs are really interested in them. I used @status = runnable to paint a cleaner picture of processes that are more important than the active value that sp_who and sp_who2 use. And filtering a pool of selected SPIDs that all users are interested in eliminates the need to run the stored procedure repeatedly. Sp_who and sp_who2 have to run once for every SPID. So, if you have five SPIDs, the stored procedure has to run five times. With sp_who1, you need to run the stored procedure only once, and you get the same results as you would with sp_who's and sp_who2's five separate runs. And sp_who1 displays the SPIDs you're most interested in on one screen.
The power of filters increases when you combine them, so I made sure sp_who1 had this capability. I call this feature multiple filter panning, and you'll love the "gold" that the panning turns up. You can vary the combinations of filters you use, depending on your situation. For example, some users run programs that connect to multiple databases. I can use @loginame and @dbname together to filter user connections by login name, then further filter by specifying the database that has a problem. Sometimes I use @status, @command, and @program together to find out which users are using our Asset Inventory application to perform inserts. Using combined filters like this gives you the advantage of convenience and efficiency. With the right combination of filters, a DBA can find a specific SPID in a second instead of wasting time searching for a needle in a haystack.
Prev. page
[1]
2
3
next page 
