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.