Troubleshoot a process (SPID) using tools & TSQL queries
First thing a DBA would follow to troubleshoot a query is to look at the process (SPID) ID for that query and with the help of Enterprise Manager tool looking at the Current Activity option under management pane will give you list of processes that are running on the SQL Server instance. Similarly you can take help of SP_WHO or SP_WHO2 to get such information, that may not be easy to understand for most of the started DBAs.
For a while lets talk about using Enterprise Manager, on a particular SPID if you double-click you will get the information of that current SPID batch, but for every SPID you have to double-click which is not feasible in a performance tuning scenario to identify a particular process to resolve. Though using SP_WHO2 by catching corresponding SPID and usage of DBCC INPUTBUFFER will get you the results, again that is limited to 255 characters only.
Here comes another undocumented feature from SQL Server 2000 version onwards, ::fn_get_sql function. It is not that easy to use this statement, to accomplish the complete information for that SPID use:
DECLARE @Handle varbinary(64);
SELECT @Handle = sql_handle
FROM sys.dm_exec_requests WHERE session_id = 52 and request_id = 0;
SELECT * FROM ::fn_get_sql(@Handle);
GO
The output you get will be a TEXT datatype and if using Query Analyzer for the output ensure you have maximized the characters to 8192 length.
If you use DBCC INPUTBUFFER(52) then you will not see beyond 256th character of the results.
Ok, how about using this in SQL Server 2005, as you will see in the BOL that:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_exec_sql_text instead.
You might think here it is simple to run, in fact no. SYS.DM_EXEC_SQL_TEXT uses 'sqlhandle' parameter to obtain the results. Otherwise you will get :
Parameters were not supplied for the function 'sys.dm_exec_sql_text'.
Books online has an excellent code example to refer:
Providing batch-execution statistics
The following example returns the text of SQL queries that are being executed in batches and provides statistical information about them.
|
|
SELECT s2.dbid,
s1.sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;
|
Using CROSS APPLY statements you can get invalueable information about the current running processes on a SQL instance. To get more information on a simple SPID you are aware then you can use:
SELECT ET.text
FROM sys.dm_exec_requests ER
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS ET
WHERE ER.session_id = 99
GO
Review the BOL for more information on the CROSS APPLY and the above referred DMVs. More you dig about DMVs ... more you will be easy to troubleshoot a problem.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.