SQLServer常用DBA运维SQL整理（1）

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

整理了一些常用的SQL

1. 查询数据库阻塞

SELECT * FROM  sys.sysprocesses WHERE blocked<>0　　

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

查询阻塞根源Session的SQL

DBCC Inputbuffer(215)

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，将阻止并行编译生成SQL执行计划；为了阻止并行编译生成SQL执行计划，可设置为1。

设置策略和具体设置方法，请参考：Configure the max degree of parallelism Server Configuration Option - SQL Server | Microsoft Docs

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重编译的原因

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('d:\sql20220721.trc', default);
GO

 a.对上述表数据进行聚合分析最耗时的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

b.最耗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

c.最耗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

 7.查询数据库中记录行数超过100万的表有哪些？

select distinct obj.name ,ind.rows from sysindexes ind left join sysobjects obj on ind.id = obj.id
where ind.rows>=1000000
order by ind.rows desc

往后会分享一些数据库优化相关文章，欢迎关注，谢谢查阅~