21. 12. 2018
Simple
SELECT DISTINCT
SPID = er.session_id
,STATUS = ses.STATUS
,Login = ses.login_name
,Host = ses.host_name
,BlkBy = er.blocking_session_id
,DBName = DB_Name(er.database_id)
,CommandType = er.command
,ObjectName = OBJECT_NAME(st.objectid)
,CPUTime = er.cpu_time
,StartTime = er.start_time
,TimeElapsed = CAST(GETDATE() - er.start_time AS TIME)
,SQLStatement = st.text
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
WHERE st.text IS NOT NULL
AND st.text not like '%--running sessions%'
ORDER BY 1
Complex
DECLARE @Debug INT = 0,
@RunsLimit INT = 200;
DECLARE
@Session_ID INT = NULL,
@OrderBy TINYINT = 0,
@OpenTranMinDuration TINYINT = 2,
@OpenTranIgnoreCustomHosts TINYINT = 1,
@OnlyMyRequests TINYINT = NULL,
@chain INT = 1,
@chainTemp INT = 0,
@OrderTemp INT = 0,
@BlockedBy INT = NULL,
@SPID INT = NULL,
@Order INT,
@Runs INT = 0;
DECLARE
@ActiveRequest TABLE(
SPID SMALLINT,
blocked_by SMALLINT,
module NVARCHAR(500),
sql_text NVARCHAR(MAX),
host_name NVARCHAR(128),
login_name NVARCHAR(128),
running_time_s INT,
wait_type NVARCHAR(60),
logical_reads_gb NVARCHAR(30),
memory_mb NVARCHAR(30),
dop SMALLINT,
open_transaction_count INT,
program_name NVARCHAR(128),
chain INT,
chain_order INT
);
INSERT INTO @ActiveRequest
(
SPID,
blocked_by,
module,
sql_text,
host_name,
login_name,
running_time_s,
wait_type,
logical_reads_gb,
memory_mb,
dop,
open_transaction_count,
program_name,
chain,
chain_order
)
SELECT
SPID = S.session_id,
blocked_by = R.blocking_session_id,
module =
CASE
WHEN R.session_id IS NULL THEN '!!! (INACTIVE & ACTIVE TRANSACTION) !!!'
ELSE DB_NAME(Q.dbid) + ISNULL( '.' + OBJECT_SCHEMA_NAME(Q.objectid, Q.dbid) + '.','') + COALESCE(OBJECT_NAME(Q.objectid, Q.dbid), '?')
END,
sql_text =
CASE
WHEN R.session_id IS NULL THEN CONCAT('DBCC INPUTBUFFER(', S.session_id, ');')
ELSE
SUBSTRING(Q.text,
CASE when R.statement_start_offset = -1 then 1 ELSE R.statement_start_offset/2 END,
1 + (case when R.statement_end_offset = -1 then DATALENGTH(Q.text) ELSE R.statement_end_offset END - case when R.statement_start_offset = -1 then 1 ELSE R.statement_start_offset END)/2)
END,
S.host_name, S.login_name,
running_time_s = D.Duration, --CONCAT(D.Duration,'', CASE WHEN D.Duration > 3600 THEN CONCAT(' (>',D.Duration/3600,'h)') END),
R.wait_type,
--last_wait_type,
logical_reads_gb = CONCAT(D.Logical_reads_gb,'', CASE WHEN D.Logical_reads_gb > 1024 THEN CONCAT(' (>',D.Logical_reads_gb/1024,'TB)') END),
--reads_mb = R.reads / 128,
memory_mb = CASE WHEN R.granted_query_memory > 0 THEN CONCAT('[',ROW_NUMBER() OVER (ORDER BY R.granted_query_memory DESC), '] ',R.granted_query_memory / 128) END,
dop = (SELECT MAX(G.dop) FROM sys.dm_exec_query_memory_grants AS G WITH(NOLOCK) WHERE G.session_id = S.session_id),
S.open_transaction_count,
S.program_name,
chain = 0,
chain_order = 0
FROM
sys.dm_exec_sessions AS S WITH(NOLOCK)
LEFT JOIN sys.dm_exec_requests AS R WITH(NOLOCK) ON S.session_id = R.session_id
OUTER APPLY sys.dm_exec_sql_text(R.sql_handle) AS Q
OUTER APPLY (
SELECT
Duration = DATEDIFF(SECOND, ISNULL(R.start_time, S.last_request_end_time), GETDATE()),
Logical_reads_gb = R.logical_reads/128/1024
) D
WHERE
S.session_id <> @@SPID
AND
(
(
ISNULL(R.wait_type,'') NOT IN ( 'SP_SERVER_DIAGNOSTICS_SLEEP', 'TRACEWRITE','BROKER_RECEIVE_WAITFOR','XE_LIVE_TARGET_TVF')
AND R.command <> 'WAITFOR'
AND S.is_user_process = 1
)
OR S.session_id IN (SELECT W.blocking_session_id FROM sys.dm_exec_requests AS W WITH(NOLOCK))
OR
(
S.open_transaction_count > 0
--AND S.is_user_process = 1
AND
(
R.start_time < DATEADD(SECOND, -1*@OpenTranMinDuration, GETDATE())
OR
(
@OpenTranIgnoreCustomHosts = 0
--OR
--DATEADD(MINUTE, 1, R.start_time) < GETDATE()
)
)
)
)
AND S.session_id = ISNULL(@Session_ID, S.session_id)
--AND S.host_name LIKE IIF(@OnlyMyRequests = 1, '%' + dbo.GetHostName(), S.host_name)
ORDER BY
IIF(@OrderBy = 1, R.blocking_session_id, NULL) ASC,
IIF(@OrderBy = 2, S.session_id, NULL) ASC,
ISNULL(R.start_time, S.last_request_end_time);
WHILE 1=1
BEGIN
SELECT TOP 1
@BlockedBy = blocked_by,
@SPID = SPID
FROM
@ActiveRequest
WHERE
COALESCE(blocked_by,0) > 0
AND chain = 0;
SET @Order = 1;
IF @Debug > 0
SELECT @BlockedBy AS '@BlockedBy', @SPID AS '@SPID';
IF @SPID IS NOT NULL
BEGIN
WHILE 1=1
BEGIN
UPDATE @ActiveRequest
SET
chain = @chain,
chain_order = @Order
WHERE
SPID = @SPID;
IF COALESCE(@BlockedBy,0) = 0
BREAK;
SET @Order = @Order +1;
SELECT TOP 1
@BlockedBy = blocked_by,
@SPID = SPID,
@chainTemp = chain,
@OrderTemp = chain_order
FROM
@ActiveRequest
WHERE
SPID = @BlockedBy
AND (chain = 0 OR chain <> @chain);
IF @@ROWCOUNT = 0
BREAK;
--pokud jsem jiz narazil na nejakou vetev, tak zmenim chain na puvodni
IF @chainTemp > 0
BEGIN
--SELECT @chainTemp '@chainTemp', @chain '@chain';
UPDATE @ActiveRequest
SET
chain = @chainTemp,
chain_order = chain_order - (@Order-@OrderTemp)
WHERE
chain = @chain;
BREAK;
END;
END;
END
ELSE
BEGIN
PRINT 'OK';
BREAK;
END;
SELECT @chain = MAX(@chain) +1 FROM @ActiveRequest;
SET @SPID = NULL;
SET @Runs = @Runs +1
IF @Runs > @RunsLimit
BEGIN
PRINT 'CYKLES'
BREAK;
END;
END;
SELECT
SPID,
blocked_by,
chain,
chain_order,
module,
sql_text,
wait_type,
host_name,
login_name,
running_time_s,
IIF(running_time_s > 3600, CONCAT(' (>',running_time_s/3600,'h)'), '') AS running_time_h,
logical_reads_gb,
memory_mb,
dop,
open_transaction_count,
program_name,
MAX(IIF(chain = 0, 0, running_time_s)) OVER (PARTITION BY chain) AS ChainMaxRunningTime
FROM
@ActiveRequest
ORDER BY
ChainMaxRunningTime DESC,
chain DESC,
chain_order DESC,
running_time_s DESC;