Meet sp_who1

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.

Neither sp_who nor sp_who2 provides the several wait-related columns that you can use to locate blocks. But sp_who1 has enhanced block-finding capabilities. In the sysprocesses table's blocked column, non-zero values indicate processes that are blocked. If you have very many blocked processes, tracing the initial blocking SPIDs can be tough. Sp_who1 automatically traces and reports which SPIDs are at the head of each blocking chain it finds.

If you've used sp_who, you're likely unimpressed by the inefficient way it displays results in text mode. Sp_who leaves too much blank space in columns, so users have to scroll through big empty spaces to get the information they need. This problem is compounded by the fact that most companies use human-friendly names (e.g., short names instead of names of 128 characters). Sp_who1 retrieves the maximum size of the current data in the columns and uses it to dynamically adjust column width. This feature ensures both column integrity and width efficiency.

I included a parameter-validity checking feature that I hope will give users more informative messages than sp_who and sp_who2 do. To see what I mean, try running sp_who 123456. The input value is outside the allowed range (between 0 and 32767), so the stored procedure returns an empty result. In sp_who1, the range violation generates an error and stops the code from executing, which saves system resources.

I included several features to make sp_who1 more user-friendly than its predecessors. One bonus feature is a summary heading for each sp_who1 session. The heading shows the date and time you executed sp_who1, the SQL Server name, and the total number of connections and runnable processes. I reasoned these data are of general interest and always good to have. In addition, sp_who1 displays eight more columns than sp_who and five more columns than sp_who2. The increase in the number of available columns is helpful when you're investigating database slowing and blocking is involved. And sp_who1 displays duplicate spid columns at the left, middle, and right positions of the query result so that scrolling back and forth through results is less painful. In this sense, sp_who1 deserves a Mr. Considerate title.

I have evaluated the performance of all three stored procedures, and they demonstrate nearly equal performance. But given sp_who1's added features, its performance is impressive. Several factors contribute to sp_who1's good performance. One is that sp_who and sp_who2 access the sysprocesses table without any index; query-execution plans for both stored procedures show that they require a table scan. And sp_who2 stores sysprocesses's content in a local temp table, whose life cycle starts from a SELECT INTO statement and ends at a DROP TABLE command. In contrast, sp_who1 uses two global temporary tables in a different way. If sp_who1 finds an existing global temporary table, it will try to use the table by executing a TRUNCATE TABLE command. You know that truncating a table is a nonlogged operation, which affords better performance than creating a new table. Using a global temporary table instead of a local one has an additional performance benefit: If several DBAs are executing sp_who1 at the same time, they can share the same set of global temporary tables to minimize frequent hits in the tempdb database. Sp_who1 also minimizes blocking on sysprocesses by specifying the NOLOCK locking hint. In a system table such as sysprocesses, the effect of rolling back an uncommitted transaction isn't significant, so sp_who1 doesn't care about dirty reads. And finally, SQL Server 2000 introduced a new table variable that can reduce temporary table usage, so if you're using sp_who1 in a SQL Server 2000–only environment, table variables might replace temporary tables and further boost the stored procedure's performance.

Seeing is Believing

It's time to get your hands dirty. Open Query Analyzer and run sp_who1. You can start by comparing sp_who, sp_who2, and sp_who1 in parallel. Note that the total SPID count should match for all three stored procedures. Now, let's run some code to simulate a block and see how sp_who1 works. Open five Query Analyzer windows. In the first Query Analyzer window, run the following statements:

BEGIN TRANUPDATE pubs..authors SET phone = '212 575-3378   WHERE au_fname = 'Sheryl' AND au_lname = 'Hunter'

In the second Query Analyzer window, execute the following statements:

BEGIN TRANINSERT INTO pubs..employee   VALUES ('ABC12345M', 'Richard', ', 'Ding',   10, 100, 1389, '2004-05-17')UPDATE pubs..authors SET phone = '212 575-3368'   WHERE au_fname = 'Sheryl' AND au_lname = 'Hunter'

In the third Query Analyzer window, run this command:

DELETE FROM pubs..employee   WHERE pub_id = 'ABC12345M'

In the fourth Query Analyzer window, query the employee table by using the following statement:

SELECT * FROM pubs..employee   WHERE emp_id = 'ABC12345M'

You'll notice that in the second, third, and fourth Query Analyzer windows, the query-execution button stays red, indicating that the query isn't finished. In the fifth window, execute the statement:

sp_who1 @blocked = 1

Voila! Figure 1 shows the Query Analyzer results you should see. Pay attention to the SPIDs that are circled in red. What has happened is that SPID 62 updated the authors table by changing one author's phone number in an uncommitted transaction. Thus, SQL Server couldn't release the exclusive lock on the row. SPID 63 opened a new transaction and inserted a new employee record without a problem but was blocked by SPID 62 when it tried to correct a typo in the author's phone number. Because the transaction was open, the lock on the employee table was still in place, which blocked the delete from SPID 64 and select from SPID 65. In the series of blocks that Figure 1 shows, SPID 62 was the head of the blocking chain. The other three blocked connections were listed under SPID 62. To solve the blocking problem, you should first focus on getting SPID 62 to commit its transaction so that SPID 63 and subsequent SPIDs can commit their transactions.

Panning for Gold

As I mentioned earlier, I included in sp_who1 a feature that I call multiple filter panning, which lets you apply filters in any combination you choose to narrow down the range of returned results you're interested in and exclude the results you don't want. Let's look at how this feature works. Imagine that user John Woo reports that his trading report is stalled; he'd noticed that the report had been running sluggishly since he left the office yesterday. Other traders aren't having any trouble running the report, so you run sp_who1 @blocked = 1, but find no existing blocks. You decide to take a look at the trading database, and you try to limit your search to information related to John Woo's report by combining the following parameters:

sp_who1 @program = 'Trading Report',   @loginame = 'BostonJwoo', @hostname = 'Johnwoo',    @dbname = 'trading', @lastbatch = 'Oct 20 2004 8:30:00:000AM'

Your query returns one SPID that meets all your search conditions, as Figure 2 shows. You notice that John last ran the trading report at 8:40 am. The process in question tried to perform an INSERT operation on a table in the trading database in an open transaction. You can see that the waittime is enormous (more than 2 hours) and the lastwaittype shows NETWORKIO. These details suggest a problem in the network connection between John's PC and the SQL Server. It turns out that the network administrator did some rewiring work last night in the server room, and when you ask him to help troubleshoot John's faulty PC connection, the administrator finds a malfunctioning hub port. Once he switches John's cable to another free port, the problem goes away. This is just one scenario in which you can use the power of multiple-filter panning in sp_who1.

Make Yourself Unique

DBAs often need exclusive access to a database. For example, imagine you're trying to restore a database called target_db. You keep getting the error Exclusive access could not be obtained because the database is in use. With sp_who1, you can easily see who is accessing target_db. Run the following statement:

sp_who1 @dbname = 'target_db'

Then, you can use the KILL command to clean up the SPIDs that the statement returns and safely perform the restore of target_db.

Just a Click Away

Nowadays, database professionals are over tasked, and flexible and efficient tools help improve their productivity. Sp_who1 has several unique features that can save a DBA's time and effort. To make the stored procedure quick and easy to use when you need it, I recommend you save in a file a list of sp_who1 statements containing the parameters or combined parameters you use most. Leave this file open in a Query Analyzer session while you work, and you'll be just one click away from investigating abnormalities in your SQL Server databases.