前置条件

• 用户有查询数据统计权限

GRANT VIEW DATABASE STATE TO database_user;

CPU性能问题

正在发生

• 查看前X个CPU消耗查询 (汇总)

SELECT TOP 10 GETDATE() runtime, * FROM (
	SELECT query_stats.query_hash,
				SUM (query_stats.cpu_time) "Total_Request_Cpu_Time_Ms",
				SUM (logical_reads) "Total_Request_Logical_Reads",
				MIN (start_time) "Earliest_Request_start_Time",
				COUNT (*) "Number_Of_Requests",
				SUBSTRING (REPLACE(REPLACE(MIN (query_stats.statement_text),CHAR (10)," "),CHAR (13)," "),1,256) AS "Statement_Text" FROM (
				SELECT req.*,
				SUBSTRING (ST.text,(req.statement_start_offset /2)+1,((CASE statement_end_offset WHEN-1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END-req.statement_start_offset)/2)+1) AS statement_text 
				FROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) AS ST) AS query_stats 
				GROUP BY query_hash) AS t 
ORDER BY Total_Request_Cpu_Time_Ms DESC;

• 查看前X个CPU消耗查询(按会话)

PRINT "--top 10 Active CPU Consuming Queries by sessions--"; 
SELECT TOP 10 req.session_id,req.start_time,cpu_time "cpu_time_ms",OBJECT_NAME(ST.objectid,ST.dbid) "ObjectName",
SUBSTRING (REPLACE(REPLACE(SUBSTRING (ST.text,(req.statement_start_offset /2)+1,((CASE statement_end_offset WHEN-1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END-req.statement_start_offset)/2)+1),CHAR (10)," "),CHAR (13)," "),1,512) AS statement_text 
FROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) AS ST 
ORDER BY cpu_time DESC;
Go

历史发生

• 统计某个时间段内前X CPU耗时查询

DECLARE @nums int = 15;
DECLARE @beginTime datetime2 = DATEADD(DAY, -1,GETUTCDATE());
DECLARE @endTime datetime2 = GETUTCDATE();

WITH AggregatedCPU AS (
	SELECT q.query_hash,
	SUM (count_executions*avg_cpu_time/1000.0) AS total_cpu_millisec,
	SUM (count_executions*avg_cpu_time/1000.0)/SUM (count_executions) AS avg_cpu_millisec,
	MAX (rs.max_cpu_time /1000.00) AS max_cpu_millisec,
	MAX (max_logical_io_reads) max_logical_reads,
	COUNT (DISTINCT p.plan_id) AS number_of_distinct_plans,
	COUNT (DISTINCT p.query_id) AS number_of_distinct_query_ids,
	SUM (CASE WHEN rs.execution_type_desc="Aborted" THEN count_executions ELSE 0 END) AS Aborted_Execution_Count,
	SUM (CASE WHEN rs.execution_type_desc="Regular" THEN count_executions ELSE 0 END) AS Regular_Execution_Count,
	SUM (CASE WHEN rs.execution_type_desc="Exception" THEN count_executions ELSE 0 END) AS Exception_Execution_Count,
	SUM (count_executions) AS total_executions,MIN (qt.query_sql_text) AS sampled_query_text 
	FROM sys.query_store_query_text AS qt 
	JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id 
	JOIN sys.query_store_plan AS p ON q.query_id=p.query_id JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_id 
	JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=rs.runtime_stats_interval_id 
	WHERE rs.execution_type_desc IN ("Regular","Aborted","Exception") AND rsi.start_time>= @beginTime AND rsi.start_time < @endTime AND count_executions > 1
	GROUP BY q.query_hash),OrderedCPU AS (
		SELECT query_hash,
		total_cpu_millisec,
		avg_cpu_millisec,
		max_cpu_millisec,
		max_logical_reads,
		number_of_distinct_plans,
		number_of_distinct_query_ids,
		total_executions,Aborted_Execution_Count,
		Regular_Execution_Count,Exception_Execution_Count,
		sampled_query_text,ROW_NUMBER () OVER (ORDER BY total_cpu_millisec DESC,query_hash ASC) AS RN 
		FROM AggregatedCPU) 


SELECT OD.query_hash,OD.total_cpu_millisec,OD.avg_cpu_millisec,OD.max_cpu_millisec,OD.max_logical_reads,OD.number_of_distinct_plans,OD.number_of_distinct_query_ids,OD.total_executions,OD.Aborted_Execution_Count,OD.Regular_Execution_Count,OD.Exception_Execution_Count,OD.sampled_query_text,OD.RN 
FROM OrderedCPU AS OD 
WHERE OD.RN <= @nums
ORDER BY avg_cpu_millisec DESC;

IO性能问题

当前发生

• 查看一个小时内每15S , IO使用情况

SELECT end_time, avg_data_io_percent, avg_log_write_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

历史发生

• 统计时间段IO等待情况

-- top queries that waited on buffer
-- note these are finished queries
WITH Aggregated AS (SELECT q.query_hash, SUM(total_query_wait_time_ms) total_wait_time_ms, SUM(total_query_wait_time_ms / avg_query_wait_time_ms) AS total_executions, MIN(qt.query_sql_text) AS sampled_query_text, MIN(wait_category_desc) AS wait_category_desc
                    FROM sys.query_store_query_text AS qt
                         JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
                         JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
                         JOIN sys.query_store_wait_stats AS waits ON waits.plan_id=p.plan_id
                         JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=waits.runtime_stats_interval_id
                    WHERE wait_category_desc="Buffer IO" AND rsi.start_time>=DATEADD(HOUR, -24, GETUTCDATE())
                    GROUP BY q.query_hash), Ordered AS (SELECT query_hash, total_executions, total_wait_time_ms, sampled_query_text, wait_category_desc, ROW_NUMBER() OVER (ORDER BY total_wait_time_ms DESC, query_hash ASC) AS RN
                                                        FROM Aggregated)
SELECT OD.query_hash, OD.total_executions, OD.total_wait_time_ms, OD.sampled_query_text, OD.wait_category_desc, OD.RN
FROM Ordered AS OD
WHERE OD.RN<=15
ORDER BY total_wait_time_ms DESC;
GO

• 写入日志最多查询

-- Top transaction log consumers
-- Adjust the time window by changing
-- rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
WITH AggregatedLogUsed
AS (SELECT q.query_hash,
           SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec,
           SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_millisec,
           SUM(count_executions * avg_log_bytes_used) AS total_log_bytes_used,
           MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec,
           MAX(max_logical_io_reads) max_logical_reads,
           COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans,
           COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids,
           SUM(   CASE
                      WHEN rs.execution_type_desc = "Aborted" THEN
                          count_executions
                      ELSE
                          0
                  END
              ) AS Aborted_Execution_Count,
           SUM(   CASE
                      WHEN rs.execution_type_desc = "Regular" THEN
                          count_executions
                      ELSE
                          0
                  END
              ) AS Regular_Execution_Count,
           SUM(   CASE
                      WHEN rs.execution_type_desc = "Exception" THEN
                          count_executions
                      ELSE
                          0
                  END
              ) AS Exception_Execution_Count,
           SUM(count_executions) AS total_executions,
           MIN(qt.query_sql_text) AS sampled_query_text
    FROM sys.query_store_query_text AS qt
        JOIN sys.query_store_query AS q
            ON qt.query_text_id = q.query_text_id
        JOIN sys.query_store_plan AS p
            ON q.query_id = p.query_id
        JOIN sys.query_store_runtime_stats AS rs
            ON rs.plan_id = p.plan_id
        JOIN sys.query_store_runtime_stats_interval AS rsi
            ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
    WHERE rs.execution_type_desc IN ( "Regular", "Aborted", "Exception" )
          AND rsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
    GROUP BY q.query_hash),
     OrderedLogUsed
AS (SELECT query_hash,
           total_log_bytes_used,
           number_of_distinct_plans,
           number_of_distinct_query_ids,
           total_executions,
           Aborted_Execution_Count,
           Regular_Execution_Count,
           Exception_Execution_Count,
           sampled_query_text,
           ROW_NUMBER() OVER (ORDER BY total_log_bytes_used DESC, query_hash ASC) AS RN
    FROM AggregatedLogUsed)
SELECT OD.total_log_bytes_used,
			 (OD.total_log_bytes_used / OD.total_executions) avg_log_bytes_used,
       OD.number_of_distinct_plans,
       OD.number_of_distinct_query_ids,
       OD.total_executions,
       OD.Aborted_Execution_Count,
       OD.Regular_Execution_Count,
       OD.Exception_Execution_Count,
       OD.sampled_query_text,
       OD.RN
FROM OrderedLogUsed AS OD
WHERE OD.RN <= 15
ORDER BY total_log_bytes_used DESC;
GO

连接数查询

SELECT
    c.session_id, c.net_transport, c.encrypt_option,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = @@SPID;

服务器使用情况

SELECT  
    AVG(avg_cpu_percent) AS "Average CPU use in percent",
    MAX(avg_cpu_percent) AS "Maximum CPU use in percent",
    AVG(avg_data_io_percent) AS "Average data IO in percent",
    MAX(avg_data_io_percent) AS "Maximum data IO in percent",
    AVG(avg_log_write_percent) AS "Average log write use in percent",
    MAX(avg_log_write_percent) AS "Maximum log write use in percent",
    AVG(avg_memory_usage_percent) AS "Average memory use in percent",
    MAX(avg_memory_usage_percent) AS "Maximum memory use in percent"
FROM sys.dm_db_resource_stats;

前X个消耗最多平均CPU时间查询

SELECT TOP 10 query_stats.query_hash AS "Query Hash",
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
     MIN(query_stats.statement_text) AS "Statement Text"
FROM
    (SELECT QS.*,
        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) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

转载请标明出处 : https://www.cnblogs.com/WilsonPan/p/13153400.html