今天线上SQLServer数据库的CPU被打爆了，紧急情况下，分析了数据库阻塞、连接分布、最耗CPU的TOP10 SQL、查询SQL并行度配置、查询SQL 重编译的原因等等
整理了一些常用的SQL
1. 查询数据库阻塞
?| 1 |
SELECT * FROM sys.sysprocesses WHERE blocked<>0
|
查询结果中，重点看Blocked这一列，先找出最多的SID，然后循环找出Root的阻塞根源SID
查询阻塞根源Session的SQL
?| 1 |
DBCC Inputbuffer(sid)
|
2. 查询SQL连接分布
?| 1 |
SELECT Hostname FROM sys.sysprocesses WHERE hostname<>''
|
3. 查询最消耗CPU的SQL Top10
?| 1 2 3 |
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并行度
?| 1 |
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
?| 1 2 3 4 5 6 7 8 9 10 |
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
?| 1 2 |
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、执行时间等）
?| 1 2 3 |
SELECT * INTO TabSQL
FROM fn_trace_gettable('C:\Users\***\Desktop\Trace\sql05trace20180606-业务.trc', default);
GO
|
对上述表数据进行聚合分析最耗时的SQL
?| 1 2 3 4 5 6 7 8 9 10 11 12 |
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
?| 1 2 3 4 5 6 7 8 9 10 11 12 |
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
?| 1 2 3 4 5 6 7 8 9 10 11 |
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
2024-05-28 10:41:56【出处】：https://www.cnblogs.com/tianqing/p/11152799.html
=======================================================================================
标签：10,运维,SQLServer,replace,char,SQL,avg,CPU From： https://www.cnblogs.com/mq0036/p/18217402