今天线上SQLServer数据库的CPU被打爆了，紧急情况下，分析了数据库阻塞、连接分布、最耗CPU的TOP10 SQL、查询SQL并行度配置、查询SQL 重编译的原因等等

整理了一些常用的SQL

1. 查询数据库阻塞

SELECT * FROM  sys.sysprocesses WHERE blocked<>0　　

查询结果中，重点看Blocked这一列，先找出最多的SID，然后循环找出Root的阻塞根源SID

查询阻塞根源Session的SQL

DBCC Inputbuffer(sid)

2. 查询SQL连接分布

SELECT Hostname FROM  sys.sysprocesses WHERE hostname<>\'\'

3. 查询最消耗CPU的SQL Top10

select top(10) st.text as Query, qs.total_worker_time, qs.execution_count from 
sys.dm_exec_query_stats as qs CROSS Apply sys.dm_exec_sql_text(qs.sql_handle) AS st
order by qs.total_worker_time desc

4. 查看SQLServer并行度

SELECT value_in_use  FROM sys.configurations WHERE name = \'max degree of parallelism\'

并行度如果设置为1，To suppress parallel plan generation, set max degree of parallelism to 1

将阻止并行编译生成SQL执行计划，最大并行度设置为1

设置策略和具体设置方法，请参考：https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-2017

USE DatabaseName ;  
GO   
EXEC sp_configure \'show advanced options\', 1;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  
EXEC sp_configure \'max degree of parallelism\', 16;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO

　　

5. 查询SQL Server Recompilation Reasons

select dxmv.name, dxmv.map_key,dxmv.map_value from 
sys.dm_xe_map_values as dxmv where dxmv.name=\'statement_recompile_cause\' order by dxmv.map_key

6. 将SQL Trace文件存入一张表，做聚合分析（CPU、IO、执行时间等）

SELECT * INTO TabSQL
FROM fn_trace_gettable(\'C:\Users\***\Desktop\Trace\sql05trace20180606-业务.trc\', default);
GO

对上述表数据进行聚合分析最耗时的SQL

select  top 100 	
	    replace(replace(replace(  substring(Textdata,1,6600) ,char(10),\' \'),char(13),\' \') ,char(9),\' \')  as \'名称\',
		--substring(Textdata,1,6600)  as old,
       count(*) as \'数量\',
       sum(duration/1000) as \'总执行时间ms\',
       avg(duration/1000) as \'平均执行时间ms\',
       avg(cpu) as \'平均CPU时间ms\',
       avg(reads) as \'平均读次数\',
       avg(writes) as \'平均写次数\', LoginName
from TabSQL   t
group by   replace(replace(replace(  substring(Textdata,1,6600) ,char(10),\' \'),char(13),\' \') ,char(9),\' \') , LoginName
order by sum(duration) desc

最耗IO的SQL

select  TOP 100 replace(replace(replace(  substring(Textdata,1,6600) ,char(10),\' \'),char(13),\' \') ,char(9),\' \') as \'名称\' ,LoginName, 
       count(*) as \'数量\',
       sum(duration/1000) as \'总执行时间ms\',
       avg(duration/1000) as \'平均执行时间ms\',
       sum(cpu) as \'总CPU时间ms\',
       avg(cpu) as \'平均CPU时间ms\',
       sum(reads) as \'总读次数\',
       avg(reads) as \'平均读次数\',
       avg(writes) as \'平均写次数\'
from TabSQL
group by replace(replace(replace(  substring(Textdata,1,6600) ,char(10),\' \'),char(13),\' \') ,char(9),\' \')  ,LoginName 
order by  sum(reads) desc

最耗CPU的SQL

SELECT TOP 100 replace(replace(replace(  substring(Textdata,1,6600) ,char(10),\' \'),char(13),\' \') ,char(9),\' \')  as \'名称\',LoginName,
       count(*) as \'数量\',
       sum(duration/1000) as \'总执行时间ms\',
       avg(duration/1000) as \'平均执行时间ms\',
       sum(cpu) as \'总CPU时间\',
       avg(cpu) as \'平均CPU时间\',
       avg(reads) as \'平均读次数\',
       avg(writes) as \'平均写次数\'
from TabSQL
group by replace(replace(replace(  substring(Textdata,1,6600) ,char(10),\' \'),char(13),\' \') ,char(9),\' \')   ,LoginName
order by avg(cpu) desc

 

　　

 

周国庆

2019/7/8

版权声明：本文为tianqing原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/tianqing/p/11152799.html