Use these tools to investigate performance problems and unexpected behavior
When you're troubleshooting query-performance problems or slowdowns, it helps to know exactly what the user or application is doing and which SET options are in effect. If you're anticipating problems, you can start a trace to record all batches and statements from every user, along with full connection details and performance statistics. But what if you get a problem you don't expect—for example, when a user query suddenly hangs and you want to investigate before you kill the connection? Or what if you're dealing with a busy system on which tracing all batches from all connections at all times is impractical because of the disk-space requirements for storing the trace files and the overhead toll on general system performance? Tools for obtaining this information are available in both SQL Server 2005 and SQL Server 2000.As you can probably guess, the options are a lot richer in the newest version of SQL Server.
What Do You Know?
Your first question when investigating a slowdown is,"What was the user doing?" If you look in the sysprocesses table or in Enterprise Manager's Current Activity window and find that a batch is blocking other batches, you'll want to know which statement is causing the block and why the locks aren't being released. If one process is using an unusual amount of processor time or memory or isn't completing its commands, you'll also want to know which operations that process is attempting.
The second question you'll ask is, "Which environment is the process running in?" In general, I'm referring to the SET options that are enabled for the connection.
Certain SET options can have a major affect on query performance. For example, the Isolation level of a connection can drastically affect the types of locks that the process acquires and, more importantly, how long the process holds those locks. And changes in SET options can preclude the use of indexed views in the query plan or prevent the reuse of otherwise identical plans. So how do you answer these questions?
SQL Server 2000
In SQL Server 2000, you can use the sp_lock procedure to determine which locks each process is holding, and you can use the sp_who2 procedure to see how much work each process is doing. However, to see executing commands, you have only two options: the DBCC INPUTBUFFER command or the SQL Server 2000 Service Pack 3 (SP3) fn_get_sql() function. (I discuss both options in "Sysprocesses in SP3," September 2003, InstantDoc ID 39664.) Both options need information from sysprocesses. DBCC INUPUTBUFFER needs the system process ID (SPID) value, and fn_get_sql needs the sql_handle, stmt_start, and stmt_end values.
Both options can provide useful information—in certain situations. Be aware, though, that both return information about only the most recent batch submitted on a connection. Suppose a connection with SPID 51 executes a batch that begins a transaction, then executes an update that acquires and holds locks. The next batch on the connection executes a statement that doesn't access any data—SELECT getdate(), for example. If you run DBCC INPUTBUFFER(51), all you'll see is SELECT getdate(), which doesn't explain the cause of the locks. Looking at the current activity in Enterprise Manager doesn't provide any more detail because Enterprise Manager simply calls DBCC INPUTBUFFER. The fn_get_sql() function has the same drawbacks as DBCC INPUTBUFFER—it shows only the most recent batch sent on a connection (although the function shows batches' full text, whereas DBCC INPUTBUFFER is limited to 255 characters).
For other types of troubleshooting, you might want to know what SET options are enabled. In SQL Server 2000, you can get this information only for a session to which you're connected. To do so, run DBCC USEROPTIONS, which gives you a list of options similar to the list that Figure 1 shows. To discover which isolation level a connection is using, you must change the isolation level from the default by using the SET TRANSACTION ISOLATION LEVEL command.
To get information about enabled SET options for other connections, you have one option, and its usefulness isn't universal.
The syscacheobjects table contains a column called setopts, which is a bitstring that represents a set of options. If you can determine which row in syscacheobjects corresponds to the plan for the connection you're trying to troubleshoot, you can decode the setopts value for that row. The information in "Tracking Recompiles," June 2005, Instant Doc ID 46075, can help you, but no one-to-one mapping exists between syscacheobjects and connections. Multiple connections can use the same plan, and some connections don't have a cached plan at all.
Decoding the setopts bitstring takes a bit of trial and error. On a test system, I turned various options on and off, then looked at syscacheobjects to see how the setopts value had changed. Because I was the only one using the system, I could easily determine which syscacheobjects row referred to the connection I was using.You can use the code in Listing 1 to take a value from the syscacheobjects .setopts column and list most of the SET options that are on. This script won't show you the values of SET options that are set to something other than ON or OFF because those values aren't stored as bits in setopts. In addition, the setopts column tracks only the SET options that can affect compilation (rather than all the ON/OFF options) because those options are the only ones for which different values necessitate the creation of a new plan. Listing 1 decodes the setopts string for a value of 4327, which is a common value for user queries in SQL Server 2000. (Note that the bits setting in the syscacheobjects.setopts column isn't the same as the bits that are set for the user options configuration option.)
SQL Server 2005
SQL Server 2005's options for determining a running process's actions and execution environment are mostly built around the new Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs). The main DMV, sys.dm_exec_requests, contains one row for every currently executing process. The DMV also contains a column called sql_handle, which can be used as a parameter to a DMF called sys.dm_exec_sql_text(). The sys.dm_exec_sql_text() DMF is a table-valued function that can be used only in a FROM clause. One of the columns in the table that this function returns is the text of the batch that corresponds to the supplied sql_handle. To join the sys.dm_exec_sql_text() function with the sys.dm_xec_requests DMV to get the text for every executing process, you can use the new SQL Server 2005 operator, CROSS APPLY.The code in Listing 2 returns the session ID for all executing sessions and the text of the batch running on each session. When I execute this code, I see the information for my session, as Figure 2 shows, because it's the only one currently executing.
This technique is easier than those in SQL Server 2000 and can return the text from multiple executing batches, as opposed to just one session at a time. If the command being executed is the call to a stored procedure, the returned text will be the name of the procedure, and the sys.dm_exec_sql_text() function can also return the object ID and database ID for the procedure.
Even more valuable (in many cases) is the information you get when you expand the SELECT list of the query that Listing 2 shows. If you ask for all the columns from sys.dm_exec_requests, you'll get most of the performance and wait-status values that you'd get from sysprocesses, including blocking_session_id, cpu_time, last_wait_ type, logical_reads, open_transaction_count, reads, wait_resource, wait_time, wait_type, and writes. You'll also get performance information that's unavailable in SQL Server 2000's sysprocesses table, such as estimated_completion_time, percent_complete, and total_elapsed_time.
And for all rows in sys.dm_exec_requests, you'll get more than a dozen columns containing information about the execution environment, including (but not limited to) the values of various SET options such as ansi_defaults, ansi_null_dflt_on, ansi_nulls, ansi_padding, ansi_warnings, concat_null_yields_null, date_first, date_format, deadlock_priority, language, lock_timeout, quoted_identifier arithabort, and transaction_isolation_level. Note that this list includes the value for the session's transaction isolation level—information that can be invaluable in tracking down blocking problems caused by locks of unusually long duration.
Many other Dynamic Management Objects can give you useful information about what's happening on your system. The DMV dm_exec_sessions contains much of the same information as sys.dm_exec_requests but returns a row for every session, not just the ones currently executing a command. Other Dynamic Management Objects provide information about the query plan being used and the indexes being accessed for each query, but discussion of those tools will have to wait for another time.