SQL Server Performance Queries

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