--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))
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.
BEGIN
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