前置條件
- 用戶有查詢數(shù)據(jù)統(tǒng)計(jì)權(quán)限
GRANT VIEW DATABASE STATE TO database_user;
CPU性能問題
正在發(fā)生
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;
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
歷史發(fā)生
- 統(tǒng)計(jì)某個(gè)時(shí)間段內(nèi)前X CPU耗時(shí)查詢
DECLARE @nums INT = 15;
DECLARE @start_hours INT = -8;
DECLARE @end_hours INT = -0;
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,
SUM (count_executions * avg_logical_io_reads) / SUM ( count_executions ) AS avg_io_reads,
MAX ( max_logical_io_reads ) max_logical_reads,
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 rsi.start_time >= DATEADD( HOUR,@start_hours,GETUTCDATE())
AND rsi.start_time <= DATEADD( HOUR,@end_hours,GETUTCDATE())
AND count_executions > 10
GROUP BY
q.query_hash
),
OrderedCPU AS (
SELECT
query_hash,
total_cpu_millisec,
avg_cpu_millisec,
max_cpu_millisec,
avg_io_reads,
max_logical_reads,
total_executions,
sampled_query_text,
ROW_NUMBER ( ) OVER ( ORDER BY avg_cpu_millisec DESC, query_hash ASC ) AS RN
FROM
AggregatedCPU
) SELECT
OD.total_cpu_millisec,
OD.avg_cpu_millisec,
OD.max_cpu_millisec,
OD.avg_io_reads,
OD.max_logical_reads,
OD.total_executions,
OD.sampled_query_text,
OD.RN
FROM
OrderedCPU AS OD
WHERE
OD.RN <= @nums
ORDER BY
avg_cpu_millisec DESC;
IO性能問題
當(dāng)前發(fā)生
- 查看一個(gè)小時(shí)內(nèi)每15S , IO使用情況
SELECT end_time, avg_data_io_percent, avg_log_write_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;
歷史發(fā)生
- 統(tǒng)計(jì)時(shí)間段IO等待情況
DECLARE @begin_hours INT = - 48;
DECLARE @end_hours INT = - 0;
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, @begin_hours, GETUTCDATE( ) )
AND rsi.start_time <= DATEADD( HOUR, @end_hours, 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.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
DECLARE @begin_hours INT = - 45;
DECLARE @end_hours INT = - 0;
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,
SUM ( count_executions * avg_logical_io_reads ) / SUM ( count_executions ) AS avg_logical_io_reads,
MAX ( max_logical_io_reads ) max_logical_reads,
SUM ( count_executions ) AS total_executions,
MAX ( 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, @begin_hours, GETUTCDATE( ) )
AND rsi.start_time <= DATEADD( HOUR, @end_hours, GETUTCDATE( ) )
GROUP BY
q.query_hash
),
OrderedLogUsed AS (
SELECT
query_hash,
total_log_bytes_used,
total_executions,
avg_logical_io_reads,
max_logical_reads,
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.total_executions,
OD.avg_logical_io_reads,
OD.max_logical_reads,
OD.sampled_query_text,
OD.RN
FROM
OrderedLogUsed AS OD
WHERE
OD.RN <= 15
ORDER BY
total_log_bytes_used DESC;
GO
連接數(shù)查詢
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;
服務(wù)器使用情況
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個(gè)消耗最多平均CPU時(shí)間查詢
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;
