SQL SERVER – High_CPU、阻塞请求、请求锁

High_CPU、阻塞请求、请求锁

1. 占用CPU的是那些进程

该查询判断当前使用CPU的时SQL Server进程还是其它进程。该脚本提供过去60分钟之内的详细信息

DECLARE @ms_ticks_now BIGINT
SELECT @ms_ticks_now = ms_ticks
FROM sys.dm_os_sys_info;
SELECT TOP 60 record_id
    ,dateadd(ms, - 1 * (@ms_ticks_now - [timestamp]), GetDate()) AS EventTime
    ,[SQLProcess (%)]
    ,SystemIdle
    ,100 - SystemIdle - [SQLProcess (%)] AS [OtherProcess (%)]
FROM (
    SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
        ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
        ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcess (%)]
        ,TIMESTAMP
    FROM (
        SELECT TIMESTAMP
            ,convert(XML, record) AS record
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%'
        ) AS x
    ) AS y
ORDER BY record_id DESC

2. 当前占用CPU高的查询

查询结果根据CPU时间排序.

SELECT
    r.session_id,r.start_time,object_name(st.objectid,st.dbid) 'ObjectName'
    ,st.TEXT AS batch_text
    ,SUBSTRING(st.TEXT, statement_start_offset / 2 + 1, (
            (
                CASE
                    WHEN r.statement_end_offset = - 1
                        THEN (LEN(CONVERT(NVARCHAR(max), st.TEXT)) * 2)
                    ELSE r.statement_end_offset
                    END
                ) - r.statement_start_offset
            ) / 2 + 1) AS statement_text
    ,qp.query_plan AS 'XML Plan'
    ,r.cpu_time AS 'cpu_time_ms', r.total_elapsed_time
    ,r.logical_reads, r.writes, r.dop
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY cpu_time DESC

sys.dm_exec_requests

sys.dm_exec_requests：返回 SQL Server 正在执行中的每个请求的信息。

-- 请求相关信息
**select session_id,status,command,sql_handle,plan_handle,database_id,cpu_time,total_elapsed_time,reads,writes,logical_reads,transaction_isolation_level 
from sys.dm_exec_requests**
		status：
			Background
			Running
			Runnable
			Sleeping
			Suspended
		command:
			SELECT
			INSERT
			UPDATE
			DELETE
			BACKUP LOG
			BACKUP DATABASE
			DBCC
			FOR
			内部系统进程将基于它们所执行任务的类型来设置该命令。 这些任务可以包括：
			LOCK MONITOR
			CHECKPOINTLAZY
			WRITER

-- 阻塞相关信息
**select blocking_session_id,wait_type,wait_time,last_wait_type,wait_resource,open_transaction_count,transaction_id,percent_complete,lock_timeout,row_count,prev_error 
from sys.dm_exec_requests

SELECT session_id ,status ,blocking_session_id,wait_type ,wait_time ,wait_resource,transaction_id
FROM sys.dm_exec_requests WHERE status = N'suspended';**
		blocking_session_id:
		-- 正在阻塞请求的会话的 ID。 如果此列为 NULL 或等于 0，则请求不会被阻止.
				-2 = 阻塞资源由孤立的分布式事务拥有。
				-3 = 阻塞资源由延迟的恢复事务拥有。
				-4 = 由于内部闩锁状态转换而导致此时无法确定阻塞闩锁所有者的会话 ID。
				-5 = 无法确定阻塞闩锁所有者的会话 ID，因为它未针对此闩锁类型进行跟踪 (例如，对于 SH 闩锁) 。
		wait_type：
		-- 如果请求当前被阻塞，则此列返回等待类型。参阅 [sys.dm_os_wait_stats](https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql?view=sql-server-ver15#WaitTypes)。
		transaction_isolation_level
		-- 创建此请求的事务时使用的隔离级别。
				0 = 未指定
				1 = 未提交读取
				2 = 已提交读取
				3 = 可重复
				4 = 可序列化
				5 = 快照

-- 查看当前请求所持有的锁信息
**select * from sys.dm_tran_locks where request_owner_type = N'TRANSACTION' and request_owner_id = 事务ID(transaction_id);**

3. 历史占用CPU高的查询

如果想知道为什么SQL Server 过去的CPU非常高，可以运行以下脚本来确定过去哪个查询占用了最多的 CPU。

SELECT TOP(10) qs.execution_count AS [Execution Count],
    (qs.total_logical_reads)/1000.0 AS [Total Logical Reads in ms],
    (qs.total_logical_reads/qs.execution_count)/1000.0 AS [Avg Logical Reads in ms],
    (qs.total_worker_time)/1000.0 AS [Total Worker Time in ms],
    (qs.total_worker_time/qs.execution_count)/1000.0 AS [Avg Worker Time in ms],
    (qs.total_elapsed_time)/1000.0 AS [Total Elapsed Time in ms],
    (qs.total_elapsed_time/qs.execution_count)/1000.0 AS [Avg Elapsed Time in ms],
    qs.creation_time AS [Creation Time]
    ,t.text AS [Complete Query Text], qp.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE t.dbid = DB_ID()
ORDER BY (qs.total_logical_reads/qs.execution_count) DESC;

上面的查询将从内存缓存中检索数据。 这意味着如果已经清除缓存或重新启动服务器，数据将被删除。 不应该 100% 依赖于此进行调查。 但是，根据我的经验，我发现结果非常可靠。

4. 监视以下性能指标

• Wait statistics of the session
• Scheduler workload
• IO stalling queries
• Memory grant for session
• Blocking scenarios
• Optional Max degree of parallelism for query
• Execution plan operators consuming a lot of CPU
• Ad-hoc workload of the server
• Parameter sniffing configuration
• etc.
  https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql?view=sql-server-ver15