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 = 'Boston\Jwoo', @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.