SQLServer常用运维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. select top(10) st.text as Query, qs.total_worker_time, qs.execution_count from
   
2. sys.dm_exec_query_stats as qs CROSS Apply sys.dm_exec_sql_text(qs.sql_handle) AS st
   
3. 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. USE DatabaseName ;
   
2. GO  
   
3. EXEC sp_configure 'show advanced options', 1;
   
4. GO
   
5. RECONFIGURE WITH OVERRIDE;
   
6. GO
   
7. EXEC sp_configure 'max degree of parallelism', 16;
   
8. GO
   
9. RECONFIGURE WITH OVERRIDE;
   
10. GO

复制代码

5. 查询SQL Server Recompilation Reasons

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

复制代码

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

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

复制代码

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

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

复制代码

最耗IO的SQL

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

复制代码

最耗CPU的SQL

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

复制代码