SQL Server 日常维护之八：查询性能消耗高的语句

SQL Server执行过程中，我们可能会需要调查服务器运行缓慢的原因。此时，我们需要通过一些SQL语句来调查数据库运行的SQL性能，比如CPU消耗高的SQL语句、查询时间长的SQL语句、消耗IO资源多的SQL语句、大批量检索数据的SQL语句等。

[操作步骤]

1. CPU消耗高前五的SQL语句
   以下示例返回有关按平均CPU时间排名的前五个查询的信息。在此示例中，查询是根据查询哈希值聚合的，逻辑上等效的查询按累积的资源使用情况进行分组。

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],  
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
        ((CASE qs.statement_end_offset  
          WHEN -1 THEN DATALENGTH(st.text)  
         ELSE qs.statement_end_offset  
         END - qs.statement_start_offset)/2) + 1) AS statement_text  
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
ORDER BY total_worker_time/execution_count DESC; 

2. 查询最消耗时间的SQL语句
   /* 查看哪些SQL语句消耗，找出有问题的SQL语句进行优化或者索引优化*/

SELECT
(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
,creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
FROM
sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like 'tch%'
ORDER BY
total_elapsed_time / execution_count DESC;

3. 查找SQL阻塞进程
/* 高并发情况下，容易产生进程阻塞，查看阻塞的SQL */

SELECT  t1.request_session_id AS 'wait_sid' ,
        t1.resource_type AS '锁类型' ,
        DB_NAME(resource_database_id) AS '库明称' ,
        t1.request_mode AS 'wait锁类型' ,
        t2.wait_duration_ms AS 'wait_time_ms' ,
        ( SELECT    text
          FROM      sys.dm_exec_requests AS r
                    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
          WHERE     r.session_id = t1.request_session_id
        ) AS 'wait_run_batch' ,
        ( SELECT    SUBSTRING(qt.text, r.statement_start_offset / 2 + 1,
                              ( CASE WHEN r.statement_end_offset = -1
                                     THEN DATALENGTH(qt.text)
                                     ELSE r.statement_end_offset
                                END - r.statement_start_offset ) / 2 + 1)
          FROM      sys.dm_exec_requests AS r
                    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
          WHERE     r.session_id = t1.request_session_id
        ) AS 'wait 运行的SQL语句' ,
        t2.blocking_session_id AS '锁定sid' ,
        ( SELECT    text
          FROM      sys.sysprocesses AS p
                    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
          WHERE     p.spid = t2.blocking_session_id
        ) AS '锁定SQL'
FROM    sys.dm_tran_locks AS t1
        INNER JOIN sys.dm_os_waiting_tasks AS t2 ON t1.lock_owner_address = t2.resource_address

4. 对查询返回行计数聚合
   以下示例返回查询的行计数聚合信息（总行数、最小行数、最大行数和上一次行数）。

SELECT qs.execution_count,  
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1,   
                 (CASE WHEN qs.statement_end_offset = -1   
                       THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2   
                       ELSE qs.statement_end_offset end -  
                            qs.statement_start_offset  
                 )/2  
             ) AS query_text,   
     qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid,   
     qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows  
FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt   
WHERE qt.text like '%SELECT%'   
ORDER BY qs.execution_count DESC;  

5. SQLServer连接情况查询

# 请求信息
select s.session_id, s.status,db_name(r.database_id) as database_name,
s.login_name,s.login_time, s.host_name,
c.client_net_address,c.client_tcp_port,s.program_name, 
r.cpu_time, r.reads, r.writes,c.num_reads,c.num_writes,
s.client_interface_name,
 s.last_request_start_time, s.last_request_end_time,
c.connect_time, c.net_transport, c.net_packet_size,
r.start_time, r.status, r.command,
r.blocking_session_id, r.wait_type,
r.wait_time, r.last_wait_type, r.wait_resource, r.open_transaction_count,
r.percent_complete,r.granted_query_memory
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s  with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c  with(nolock)
on s.session_id = c.session_id
where s.session_id >50
order by s.session_id

# 按用户统计连接数
select login_name,COUNT(0) user_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s  with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c  with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by login_name 
order by 2 DESC

# 按机器统计连接数
select s.host_name,c.client_net_address,COUNT(0) host_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s  with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c  with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by host_name,client_net_address 
order by 3 DESC

[参考信息]
MSDN sys.dm_exec_query_stats (Transact-SQL)
https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15