| Downloads |
|---|
| 93150.zip |
If you ever had users call to tell you that a SQL Server database is slow, you'll likely find sp_Now a handy tool. This stored procedure determines what processes are currently executing and consuming resources on a database server. This information is helpful when troubleshooting sporadic performance problems, especially in an environment in which applications span multiple servers.
For example, if a user calls and complains that the database is slow, you can run sp_Now to quickly determine what the database server is actually doing. If the database server is under a heavy load due to SQL activity, you'll be able to see the exact SQL code that's causing the load. Perhaps a user submitted a poorly coded query (e.g., the user created a Cartesian product of the three biggest tables). Or perhaps an administrator is running a job that's producing a detailed report, but that job really should be run only during off-hours. If sp_Now shows that not much SQL activity is occurring, you can be pretty sure that the database server isn't to blame. You can then work with the user to determine whether the problem is occurring at the application or network level.
Listing 1 shows an excerpt from sp_Now Now, which I wrote for use on SQL Server 2000. As callout B in Listing 1 shows, sp_Now uses the fn_get_sql system function, which Microsoft introduced in SQL Server 2000 Service Pack 3 (SP3). This function returns the SQL statements that a particular process is currently executing. The sp_Now stored procedure uses fn_get_sql to build a cursor of all currently active processes.
As callout A shows, sp_Now produces a report that shows summary information for each active process, including the process's cumulative disk reads and writes (phys_io), the process's CPU usage (cpu), and the application's name (program_name) from the sysprocesses table. The report also includes the exact SQL statements that the process is executing.
The report is easy to read. At the top, you'll find the total number of active system process IDs (SPIDs) that have open SQL connections and that are currently processing data.The report uses two lines of x's to separate the details of what each SPID is doing.
For each SPID, there are three sections. The first section provides a summary from the sysprocesses table that shows the login name, host name, and other details of the associated SQL connection. This section includes a summary of the connection's CPU usage, the total amount of disk I/O, and the SPID of any other process that's being blocked by this process. (Multiple entries for the same SPID in this section indicates that SQL Server has divided up the query among multiple CPUs in an attempt increase the performance through parallel processing.)
The next section contains the output of DBCC INPUTBUFFER (which displays the last statement sent from a client) for the SPID being examined. The first 255 characters of the query are displayed.
The last section contains the output from the call to the fn_get_sql system function. A much larger portion of the SQL code being executed is displayed here. In the case of a stored procedure, usually all the code is displayed.
When reviewing the sp_Now output, pay attention to the amount of physical I/O and CPU time the processes are using. Also take note of any blocking. Remember that if processes are consuming resources, they'll show up in the results. If nothing shows up in the results, you can be pretty sure that the database server isn't experiencing performance problems.
