SQLServer常用运维SQL整理 - 好文

今天线上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

<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

热门工具换一换