SQLServer常用运维SQL整理

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

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

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(replacesubstring(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(replacesubstring(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(replacesubstring(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(replacesubstring(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(replacesubstring(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(replacesubstring(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

=======================================================================================

posted on   jack_Meng  阅读(77)  评论(0编辑  收藏  举报

相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器
历史上的今天:
2021-05-28 博客园中TinyMCE编辑器的快捷键
2021-05-28 23种设计模式娓娓道来
2021-05-28 TotalCommander使用教程

导航

< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8
点击右上角即可分享
微信分享提示

喜欢请打赏

扫描二维码打赏

支付宝打赏

主题色彩