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.