sqlserver性能排查脚本

 一、CPU问题 1、目前正在发生的cpu问题排查   --累计耗费cpu多统计 PRINT '-- top 10 Active CPU Consuming Queries (aggregated)--'; 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;   ---单次消耗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       2、过去发生的CPU高问题分析 -- Top 15 CPU consuming queries by query hash -- Note that a query hash can have many query ids if not parameterized or not parameterized properly WITH AggregatedCPU AS (     SELECT q.query_hash         ,SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_ms         ,SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_ms         ,MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms         ,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         INNER JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id         INNER JOIN sys.query_store_plan AS p ON q.query_id = p.query_id         INNER JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id         INNER 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, - 2, GETUTCDATE())  ---过去2小时     GROUP BY q.query_hash     )     ,OrderedCPU AS (     SELECT query_hash         ,total_cpu_ms         ,avg_cpu_ms         ,max_cpu_ms         ,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_ms DESC                 ,query_hash ASC             ) AS query_hash_row_number     FROM AggregatedCPU     ) SELECT OD.query_hash     ,OD.total_cpu_ms     ,OD.avg_cpu_ms     ,OD.max_cpu_ms     ,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.query_hash_row_number FROM OrderedCPU AS OD WHERE OD.query_hash_row_number <= 15 --get top 15 rows by total_cpu_ms ---TOP15 ORDER BY total_cpu_ms DESC;   3、CPU统计 --TOP15 CPU查询 SELECT TOP 15 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 Avg_CPU_Time DESC;   ---TOP 累计CPU耗时查询 SELECT     highest_cpu_queries.plan_handle,     highest_cpu_queries.total_worker_time,     q.dbid,     q.objectid,     q.number,     q.encrypted,     q.[text] FROM     (SELECT TOP 15             qs.plan_handle,             qs.total_worker_time         FROM             sys.dm_exec_query_stats AS qs         ORDER BY qs.total_worker_time desc     ) AS highest_cpu_queries CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q ORDER BY highest_cpu_queries.total_worker_time DESC;     二、IO问题 如果已经出现IO问题， 可以使用 sys.dm_exec_requests 或 sys.dm_os_waiting_tasks 查看 wait_type 和 wait_time。   --统计数据和日志IO量 SELECT     database_name = DB_NAME() ,   UTC_time = end_time ,   'Data IO In % of Limit'                   = rs.avg_data_io_percent ,   'Log Write Utilization In % of Limit'     = rs.avg_log_write_percent FROM sys.dm_db_resource_stats AS rs  --past hour only ORDER BY  rs.end_time DESC;   --统计过去2小时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                          INNER JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id                          INNER JOIN sys.query_store_plan AS p ON q.query_id=p.query_id                          INNER JOIN sys.query_store_wait_stats AS waits ON waits.plan_id=p.plan_id                          INNER 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, -2, 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 query_hash_row_number                                                         FROM Aggregated) SELECT OD.query_hash, OD.total_executions, OD.total_wait_time_ms, OD.sampled_query_text, OD.wait_category_desc, OD.query_hash_row_number FROM Ordered AS OD WHERE OD.query_hash_row_number <= 15 -- get top 15 rows by total_wait_time_ms ORDER BY total_wait_time_ms DESC; GO   ---查看 WRITELOG 等待类型的日志 I/O 总计 -- Top transaction log consumers -- Adjust the time window by changing -- rsi.start_time >= DATEADD(hour, -2, GETUTCDATE()) WITH AggregatedLogUsed AS (SELECT q.query_hash,            SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_ms,            SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_ms,            SUM(count_executions * avg_log_bytes_used) AS total_log_bytes_used,            MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms,            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         INNER JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id         INNER JOIN sys.query_store_plan AS p ON q.query_id = p.query_id         INNER JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id         INNER 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, -2, 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 query_hash_row_number     FROM AggregatedLogUsed) SELECT OD.total_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.query_hash_row_number FROM OrderedLogUsed AS OD WHERE OD.query_hash_row_number <= 15 -- get top 15 rows by total_log_bytes_used ORDER BY total_log_bytes_used DESC; GO     三、tempdb性能问题 与 tempdb 问题相关的常见等待类型是 PAGELATCH_*（不是 PAGEIOLATCH_*）。 但是，出现 PAGELATCH_* 等待并不总是意味着发生了 tempdb 争用。 这种等待可能还意味着，由于并发请求面向相同的数据页面，发生了用户对象数据页面争用。 若要进一步确认 tempdb 争用，请使用 sys.dm_exec_requests 确认 wait_resource 值是否以 2:x:y 开头，其中 2 tempdb 是数据库 ID，x 是文件 ID，y 是页面 ID。 对于 tempdb 争用，常用的方法是减少或重写依赖于 tempdb 的应用程序代码。 常见的 tempdb 使用区域包括： 临时表 表变量 表值参数 包含使用排序、哈希联接和 spool 的查询计划的查询   ---使用表变量和临时表的TOP统计 SELECT plan_handle, execution_count, query_plan INTO #tmpPlan FROM sys.dm_exec_query_stats      CROSS APPLY sys.dm_exec_query_plan(plan_handle); GO   WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp) SELECT plan_handle, stmt.stmt_details.value('@Database', 'varchar(max)') AS 'Database' , stmt.stmt_details.value('@Schema', 'varchar(max)') AS 'Schema' , stmt.stmt_details.value('@Table', 'varchar(max)') AS 'table' INTO #tmp2 FROM     (SELECT CAST(query_plan AS XML) sqlplan, plan_handle FROM #tmpPlan) AS p         CROSS APPLY sqlplan.nodes('//sp:Object') AS stmt(stmt_details); GO   SELECT t.plan_handle, [Database], [Schema], [table], execution_count FROM     (SELECT DISTINCT plan_handle, [Database], [Schema], [table]      FROM #tmp2      WHERE [table] LIKE '%@%' OR [table] LIKE '%#%') AS t         INNER JOIN #tmpPlan AS t2 ON t.plan_handle=t2.plan_handle; GO DROP TABLE #tmpPlan DROP TABLE #tmp2     ----长时间运行事务查询 SELECT DB_NAME(dtr.database_id) 'database_name',        sess.session_id,        atr.name AS 'tran_name',        atr.transaction_id,        transaction_type,        transaction_begin_time,        database_transaction_begin_time,        transaction_state,        is_user_transaction,        sess.open_transaction_count,        TRIM(REPLACE(                 REPLACE(                             SUBSTRING(                                         SUBSTRING(                                                     txt.text,                                                     (req.statement_start_offset / 2) + 1,                                                     ((CASE req.statement_end_offset                                                             WHEN -1 THEN                                                                 DATALENGTH(txt.text)                                                             ELSE                                                                 req.statement_end_offset                                                         END - req.statement_start_offset                                                     ) / 2                                                     ) + 1                                                 ),                                         1,                                         1000                                     ),                             CHAR(10),                             ' '                         ),                 CHAR(13),                 ' '             )             ) Running_stmt_text,        recenttxt.text 'MostRecentSQLText' FROM sys.dm_tran_active_transactions AS atr     INNER JOIN sys.dm_tran_database_transactions AS dtr         ON dtr.transaction_id = atr.transaction_id     LEFT JOIN sys.dm_tran_session_transactions AS sess         ON sess.transaction_id = atr.transaction_id     LEFT JOIN sys.dm_exec_requests AS req         ON req.session_id = sess.session_id            AND req.transaction_id = sess.transaction_id     LEFT JOIN sys.dm_exec_connections AS conn         ON sess.session_id = conn.session_id     OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) AS txt     OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS recenttxt WHERE atr.transaction_type != 2       AND sess.session_id != @@spid ORDER BY start_time ASC;   四、内存分配问题   ---统计TOP内存消耗语句 SELECT IDENTITY(INT, 1, 1) rowId,     CAST(query_plan AS XML) query_plan,     p.query_id INTO #tmp FROM sys.query_store_plan AS p     INNER JOIN sys.query_store_runtime_stats AS r         ON p.plan_id = r.plan_id     INNER JOIN sys.query_store_runtime_stats_interval AS i         ON r.runtime_stats_interval_id = i.runtime_stats_interval_id WHERE start_time > '2018-10-11 14:00:00.0000000'       AND end_time < '2018-10-17 20:00:00.0000000';   WITH cte AS (SELECT query_id,         query_plan,         m.c.value('@SerialDesiredMemory', 'INT') AS SerialDesiredMemory     FROM #tmp AS t         CROSS APPLY t.query_plan.nodes('//*:MemoryGrantInfo[@SerialDesiredMemory[. > 0]]') AS m(c) ) SELECT TOP 50     cte.query_id,     t.query_sql_text,     cte.query_plan,     CAST(SerialDesiredMemory / 1024. AS DECIMAL(10, 2)) SerialDesiredMemory_MB FROM cte     INNER JOIN sys.query_store_query AS q         ON cte.query_id = q.query_id     INNER JOIN sys.query_store_query_text AS t         ON q.query_text_id = t.query_text_id ORDER BY SerialDesiredMemory DESC;       --统计TOP10内存分配 SELECT TOP 10     CONVERT(VARCHAR(30), GETDATE(), 121) AS runtime,        r.session_id,        r.blocking_session_id,        r.cpu_time,        r.total_elapsed_time,        r.reads,        r.writes,        r.logical_reads,        r.row_count,        wait_time,        wait_type,        r.command,        OBJECT_NAME(txt.objectid, txt.dbid) 'Object_Name',        TRIM(REPLACE(REPLACE(SUBSTRING(SUBSTRING(TEXT, (r.statement_start_offset / 2) + 1,         (  (             CASE r.statement_end_offset                 WHEN - 1                     THEN DATALENGTH(TEXT)                 ELSE r.statement_end_offset                 END - r.statement_start_offset             ) / 2         ) + 1), 1, 1000), CHAR(10), ' '), CHAR(13), ' ')) AS stmt_text,        mg.dop,                                               --Degree of parallelism        mg.request_time,                                      --Date and time when this query requested the memory grant.        mg.grant_time,                                        --NULL means memory has not been granted        mg.requested_memory_kb / 1024.0 requested_memory_mb,  --Total requested amount of memory in megabytes        mg.granted_memory_kb / 1024.0 AS granted_memory_mb,   --Total amount of memory actually granted in megabytes. NULL if not granted        mg.required_memory_kb / 1024.0 AS required_memory_mb, --Minimum memory required to run this query in megabytes.        max_used_memory_kb / 1024.0 AS max_used_memory_mb,        mg.query_cost,                                        --Estimated query cost.        mg.timeout_sec,                                       --Time-out in seconds before this query gives up the memory grant request.        mg.resource_semaphore_id,                             --Non-unique ID of the resource semaphore on which this query is waiting.        mg.wait_time_ms,                                      --Wait time in milliseconds. NULL if the memory is already granted.        CASE mg.is_next_candidate --Is this process the next candidate for a memory grant            WHEN 1 THEN                'Yes'            WHEN 0 THEN                'No'            ELSE                'Memory has been granted'        END AS 'Next Candidate for Memory Grant',        qp.query_plan FROM sys.dm_exec_requests AS r     INNER JOIN sys.dm_exec_query_memory_grants AS mg         ON r.session_id = mg.session_id            AND r.request_id = mg.request_id     CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS txt     CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp ORDER BY mg.granted_memory_kb DESC;