Tracing Performance

You can monitor your SQL Server system's performance in many ways. Here are six helpful system stored procedures and a function that you can use to monitor your SQL Server system and trace your applications' performance.

7. sp_trace_create

Calling SQL Server's built-in tracing stored procedures from your T-SQL scripts is a good way to set up and run recurring traces. Sp_trace_create returns the trace ID that's in the output parameter that the other sp_trace* stored procedures use (@tid). To set up a trace that writes to the file C:\mytrace.trc, use the following command:

EXEC sp_trace_create @traceid = @tid OUTPUT ,
 @options = 0 , @tracefile = N'c:\mytrace'

6. sp_trace_setevent

After you create a trace, you can use sp_trace_setevent to specify the events you want to trace. (See SQL Server Books Online—BOL—for a complete list of the event IDs that you can add to the trace.) The following example shows how to add an event to trace all the SQL Server logins (@eventid = 14):

EXEC sp_trace_setevent @traceid = @tid, 
@eventid = 14, @columnid = 6, @on = @on

5. sp_trace_setfilter

Filters in a trace refine the data you capture. BOL documents the accepted values for the @columnid, @logical_operator, and @comparison_operator parameters. To filter for login IDs that are like mikeo, enter

EXEC sp_trace_setfilter @traceid = @tid, 
@columnid = 6, @logical_operator = 1, 
@comparison_operator = 6, @value = N'mikeo'

4. sp_trace_setstatus

Sp_trace_setstatus starts and stops a trace. A value of 1 for @status starts the trace; 0 stops the trace; and 2 closes the trace. To start a trace for the example above, enter

EXEC sp_trace_setstatus @traceid = @tid, @status = 1

3. fn_trace_getinfo

Although this item is a function, not a system stored procedure, you can use the following syntax to see which traces are running:

SELECT * FROM ::fn_trace_getinfo(default)

2. sp_lock

To track down application-blocking problems, sp_lock lists the current process ID, the database object requesting the lock, and the requested lock type. Sp_lock returns database ID numbers, but you can use the OBJECT_NAME() function to translate the ID numbers to their database names. To list all current SQL Server locks, enter

EXEC sp_lock

1. sp_who and sp_who2

Sp_who and sp_who2 provide information about running processes. Sp_who shows the system's current process IDs, status, and the associated login, host, and database name. In addition to that information, sp_who2 lists the CPU, disk, and memory utilization and shows the process names. Use the syntax

EXEC sp_who2