SQL Server Performance Tuning
-- Top 10 sessions caused blocking
SELECT TOP 10
r.session_id,
r.plan_handle,
r.sql_handle,
r.request_id,
r.start_time,
r.status,
r.command,
r.database_id,
r.user_id,
r.wait_type,
r.wait_time,
r.last_wait_type,
r.wait_resource,
r.total_elapsed_time,
r.cpu_time,
r.transaction_isolation_level,
r.row_count,
st.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.blocking_session_id = 0
AND r.session_id IN
(
SELECT DISTINCT (blocking_session_id) FROM sys.dm_exec_requests
)
GROUP BY r.session_id,
r.plan_handle,
r.sql_handle,
r.request_id,
r.start_time,
r.status,
r.command,
r.database_id,
r.user_id,
r.wait_type,
r.wait_time,
r.last_wait_type,
r.wait_resource,
r.total_elapsed_time,
r.cpu_time,
r.transaction_isolation_level,
r.row_count,
st.text
ORDER BY r.total_elapsed_time DESC
-- To identify blockers and victims
;WITH Blockers
AS (SELECT DISTINCT
blocking_session_id AS session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0)
SELECT 'Blocker' AS type_desc,
sys.dm_exec_sessions.session_id,
sys.dm_exec_requests.start_time,
sys.dm_exec_requests.status,
sys.dm_exec_requests.command,
sys.dm_exec_requests.wait_type,
sys.dm_exec_requests.wait_time,
sys.dm_exec_requests.blocking_session_id,
'' AS stmt_text
FROM sys.dm_exec_sessions
LEFT JOIN sys.dm_exec_requests
ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id
INNER JOIN Blockers
ON Blockers.session_id = sys.dm_exec_sessions.session_id
UNION
SELECT 'Victim' AS type_desc,
sys.dm_exec_sessions.session_id,
sys.dm_exec_requests.start_time,
sys.dm_exec_requests.status,
sys.dm_exec_requests.command,
sys.dm_exec_requests.wait_type,
sys.dm_exec_requests.wait_time,
sys.dm_exec_requests.blocking_session_id,
ST.text AS stmt_text
FROM sys.dm_exec_sessions
INNER JOIN sys.dm_exec_requests
ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id
CROSS APPLY sys.dm_exec_sql_text(sys.dm_exec_requests.sql_handle) AS ST
WHERE blocking_session_id > 0
-- Top Blocker (> 1 min blocking)
SELECT w.session_id [Spid],
DB_NAME(r.database_id) [DBName],
s.status,
w.blocking_session_id,
w.wait_duration_ms,
s.[program_name],
s.client_interface_name,
s.[host_name],
s.cpu_time [cpu_time_ms],
s.memory_usage * 8 / 1024 [Memory_usage],
w.wait_type,
r.wait_resource,
s.deadlock_priority,
CASE s.transaction_isolation_level
WHEN 0 THEN
'Unspecified'
WHEN 1 THEN
'ReadUncomitted'
WHEN 2 THEN
'Readcomitted'
WHEN 3 THEN
'Repeatable'
WHEN 4 THEN
'Serializable'
WHEN 5 THEN
'Snapshot'
END AS TRANSACTION_ISOLATION_LEVEL,
s.original_login_name,
s.login_time,
s.last_request_start_time,
s.last_request_end_time,
r.percent_complete,
s.row_count,
t.text,
q.query_plan,
aa.text [BlockingText]
FROM sys.dm_os_waiting_tasks w
INNER JOIN sys.dm_exec_sessions s
ON w.session_id = s.session_id
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS q
OUTER APPLY
(
SELECT S2.text
FROM sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS S2
WHERE sp.spid = w.blocking_session_id
) aa
WHERE s.is_user_process = 1
AND w.blocking_session_id > 0
ORDER BY w.wait_duration_ms DESC
-- Real Time Blocking information
SELECT DB_NAME(Blocking.dbid) AS DBName,
Blocking.spid AS Blocking_SPID,
CASE Blocking.blocked
WHEN 0 THEN
'Head Blocker'
ELSE
CAST(Blocking.blocked AS NVARCHAR(20))
END AS Blocking_BlockedBy,
Blocking.status AS Blocking_Status,
Blocking.last_batch AS Blocking_LastBatch,
Blocking.cmd AS Blocking_Command,
Blocking.nt_username AS Blocking_UserName,
Blocking.login_time AS Blocking_LoginTime,
Blocking.program_name AS Blocking_ProgramName,
Blocking.hostname AS Blocking_HostName,
Blocking.hostprocess AS Blocking_HostProcess,
Blocking.waittime AS Blocking_WaitTime,
Blocking.waittype AS Blocking_WaitType,
Blocking.lastwaittype AS Blocking_LastWaitType,
Blocking.waitresource AS Blocking_WaitResource,
Blocking.open_tran AS Blocking_OpenTrans,
Blocking.cpu AS Blocking_CPU,
Blocking.memusage AS Blocking_MemoryUsage,
Blocking.physical_io AS Blocking_PhysicalIO,
Blocked.spid AS Blocked_SPID,
Blocked.status AS Blocked_Status,
Blocked.last_batch AS Blocked_LastBatch,
Blocked.cmd AS Blocked_Command,
Blocked.nt_username AS Blocked_UserName,
Blocked.login_time AS Blocked_LoginTime,
Blocked.program_name AS Blocked_ProgramName,
Blocked.hostname AS Blocked_HostName,
Blocked.hostprocess AS Blocked_HostProcess,
Blocked.waittime AS Blocked_WaitTime,
Blocked.waittype AS Blocked_WaitType,
Blocked.lastwaittype AS Blocked_LastWaitType,
Blocked.waitresource AS Blocked_WaitResource,
Blocked.open_tran AS Blocked_OpenTrans,
Blocked.cpu AS Blocked_CPU,
Blocked.memusage AS Blocked_MemoryUsage,
Blocked.physical_io AS Blocked_PhysicalIO
FROM sys.sysprocesses AS Blocking WITH (NOLOCK)
INNER JOIN sys.sysprocesses AS Blocked WITH (NOLOCK)
ON Blocking.spid = Blocked.blocked
ORDER BY Blocking.blocked ASC,
Blocked.blocked ASC
-- Blocking Details (millisecond detection)
SELECT sp.spid,
sp.status,
sp.hostprocess,
sp.hostname,
sp.loginame,
sp.cpu,
sp.physical_io,
sp.blocked,
DB_NAME(sp.dbid) AS [Database],
sp.cmd,
sp.program_name,
sp.uid,
sp.memusage,
sp.waitresource,
sp.waittype,
sp.lastwaittype,
sp.login_time,
sp.last_batch,
sp.open_tran,
sp.hostname,
sp.[sql_handle],
S2.text
FROM sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS S2
WHERE spid > 50
AND sp.blocked > 0
ORDER BY cpu DESC
-- select * from Sys.Dm_Exec_Sql_Text(0x0300080028881470db6f25019a9b00000100000000000000)
-- Locks established in current database
SELECT (CASE sys.dm_tran_locks.request_session_id
WHEN -2 THEN
'ORPHANED DISTRIBUTED TRANSACTION'
WHEN -3 THEN
'DEFERRED RECOVERY TRANSACTION'
ELSE
sys.dm_tran_locks.request_session_id
END
) AS session_id,
DB_NAME(sys.dm_tran_locks.resource_database_id) AS database_name,
sys.objects.name AS locked_obj_name,
sys.dm_tran_locks.resource_type AS locked_resource,
sys.dm_tran_locks.request_mode AS lock_type,
st.text AS stmt_text,
sys.dm_exec_sessions.login_name AS login_name,
sys.dm_exec_sessions.host_name AS host_name,
sys.dm_tran_locks.request_status AS request_status
FROM sys.dm_tran_locks
JOIN sys.objects
ON sys.objects.object_id = sys.dm_tran_locks.resource_associated_entity_id
JOIN sys.dm_exec_sessions
ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
JOIN sys.dm_exec_connections
ON sys.dm_exec_connections.session_id = sys.dm_exec_sessions.session_id
CROSS APPLY sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle) AS st
WHERE sys.dm_tran_locks.resource_database_id = DB_ID()
ORDER BY sys.dm_tran_locks.request_session_id