SQL Server CPU 利用率毛刺的分析定位与解决

当我们遇到DB性能问题或DBServer监控指标异常时，以下四个SP，可以帮忙我们快速定位SQL语句。

3.1 查看当前阻塞排队的情况
复制代码
/*

uspm_Block
功能：查看阻塞和锁,阻塞源头
参数：无

*/
create PROCEDURE [dbo].[uspm_Block]
as

–查找有关被阻塞的请求的信息(含用户)
SELECT s.loginame
,[Individual Query] = SUBSTRING (qr.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qr.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)
,qs.session_id ,s.counts AS [进程个数],qs.status ,qs.blocking_session_id
,qs.wait_type ,qs.wait_time ,qs.wait_resource
,qs.transaction_id
FROM SYS.DM_EXEC_REQUESTS qs (nolock)
LEFT JOIN (
SELECT spid,MAX(loginame)AS loginame,COUNT(0)AS counts FROM SYS.SYSPROCESSES (nolock) GROUP BY spid
) s ON qs.session_id=s.spid
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qr
WHERE qs.status = N’suspended’
–and s.loginame<>’’
ORDER BY qs.wait_time DESC

–查找阻塞源头v3.0
SELECT SP.spid
,CASE WHEN ST1.text IS NULL THEN ST2.text
ELSE SUBSTRING (ST1.text,SR.statement_start_offset/2,
(
CASE WHEN SR.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), ST1.text)) * 2
ELSE SR.statement_end_offset
END - SR.statement_start_offset)/2
)
END AS [T-sql]
,SP.loginame
,DB_NAME(SP.dbid) AS [db_name]
,SP.open_tran,SP.hostname,SP.program_name,SP.waitresource,SP.*
FROM SYS.SYSPROCESSES SP (nolock)
LEFT JOIN SYS.DM_EXEC_REQUESTS SR (nolock) ON SP.spid=SR.session_id
LEFT JOIN SYS.DM_EXEC_CONNECTIONS SC (nolock) ON SP.spid=SC.session_id
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SC.most_recent_sql_handle) AS ST2
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SR.sql_handle) AS ST1
WHERE SP.spid IN
(
SELECT BLOCKED FROM SYS.SYSPROCESSES (nolock) WHERE BLOCKED<>0
)
AND SP.BLOCKED=0
GO
复制代码
3.2 查看 当前最消耗CPU的SQL
复制代码
/*

uspm_perf_topcpu
功能：取当前N个最耗CPU的SQL
参数：@topCount --N的具体数量,默认取前20条
示例：
1.取前10条
exec uspm_perf_topcpu

 2.取前20条最耗CPU的SQL
  exec uspm_perf_topcpu @topCount=20 

---

/
CREATE PROCEDURE [dbo].[uspm_perf_topcpu]
(@topCount int=10)
as
set nocount on
declare @cmd varchar(1000)
select @cmd=’
SELECT TOP ‘+ CAST(@topCount AS VARCHAR)+’ SUBSTRING(text, (statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1
) AS query_text
,b.hostname
,b.loginame
,a.
,qr.text
,qt.query_plan
FROM sys.dm_exec_requests a (nolock)
INNER JOIN sys.sysprocesses b (nolock) on a.session_id=b.spid
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as qr
CROSS APPLY sys.dm_exec_query_plan(a.plan_handle)as qt
ORDER BY a.total_elapsed_time DESC’

exec(@cmd)

GO
复制代码
3.3 查看执行时间最长的SQL
复制代码
/*

uspm_perf_topduration
功能：取N个执行时间最长的SQL
参数：@topCount --N的具体数量,默认取前50条
示例：
–1.取前50条
exec uspm_perf_topduration

  --2.取前10条执行时间最长的SQL
  exec uspm_perf_topduration @topCount=10

---

/
CREATE PROCEDURE [dbo].[uspm_perf_topduration]
(@topCount int=50)
as
set nocount on
declare @cmd varchar(600)
select @cmd=’
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top ‘+ cast(@topCount as varchar)+’
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs (nolock)
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc’
exec(@cmd)
GO
复制代码
3.4 当前SQL执行概览
复制代码
/

uspm_perf_cpudetail
功能：查看CPU的任务数量,使用率，CPU瓶颈
参数：无参

*/
CREATE PROCEDURE [dbo].[uspm_perf_cpudetail]
as
set nocount on
–1.Cpu_Task
SELECT ‘查看cpu任务’
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers (nolock)
WHERE scheduler_id < 255

—2.CUP_USING
SELECT ‘查看cpu使用情况’
declare @ts_now bigint
–select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info (nolock)
select @ts_now = cpu_ticks/(cpu_ticks/ms_ticks) from sys.dm_os_sys_info (nolock)

select top 50 record_id,
dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,
SQLProcessUtilization as [CPU使用率,不能始终处于高位],
SystemIdle,
100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
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 SQLProcessUtilization,
timestamp
from (
select timestamp, convert(xml, record) as record
from sys.dm_os_ring_buffers (nolock)
where ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
and record like ‘%%’) as x
) as y
order by record_id desc

–3.CPU_NECK
SELECT ‘查看CPU瓶颈’
select cast([signal_wait_time_ms] as decimal(30,2))/[wait_time_ms] as [百分比] ,*
from sys.dm_os_wait_stats (nolock)
where [wait_time_ms]<>0
and cast([signal_wait_time_ms] as decimal(30,2))>([wait_time_ms]*0.25 )
order by 1 desc

SELECT ‘查看百分比是否>10%,如果大于10%，考虑降低并行度’
select cast([signal_wait_time_ms] as decimal(30,2))/[wait_time_ms] as [百分比],*
from sys.dm_os_wait_stats (nolock)
where [wait_time_ms]<>0 AND wait_type=‘CXPACKET’
GO
mosfet驱动芯片 https://www.zg886.cn