Friday, August 12, 2011

One way to monitor long running queries

--Create table
CREATE TABLE  longrunning(session_id int,start_time datetime,status varchar(2000),cpu_time int,[Elapsed time(ms)] int,sql_statement nvarchar(4000),host_name varchar(100),program_name varchar(100))

--Schedule the below  code in SQL Server job(may be recurring every 1 or 2 minutes).You can then use the table to analyse the longrunning queries.

INSERT INTO longrunning( ,start_time,status,cpu_time,[Elapsed time(ms)],sql_statement,host_name,
program_name) SELECT p.session_id, start_time, p.status,p.cpu_time,p.total_elapsed_time, --original_login_name,

(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,host_name,program_name
FROM sys.dm_exec_requests p CROSS APPLY sys.dm_exec_sql_text(sql_handle) s2 inner join sys.dm_exec_sessions s on s.session_id=p.session_idAND p.total_elapsed_time > 2000--(2 sec)


No comments:

Post a Comment