thierry/sql-snippets
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
sql-snippets/PERFS - poor queries.sql
Thierry Schork 809b1aa75d sync
2025-07-07 09:09:23 +02:00

153 lines
8.5 KiB
SQL
Raw Permalink Blame History

SELECT TOP 100
'longest query' AS msg,
GETDATE() AS [Collection Date],
[qs].[execution_count] AS [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],
DB_NAME([qt].[dbid]) AS [DB Name],
[qs].[total_worker_time] AS [Total CPU Time],
[qs].[total_worker_time] / [qs].[execution_count] AS [Avg CPU Time (ms)],
[qs].[total_physical_reads] AS [Total Physical Reads],
[qs].[total_physical_reads] / [qs].[execution_count] AS [Avg Physical Reads],
[qs].[total_logical_reads] AS [Total Logical Reads],
[qs].[total_logical_reads] / [qs].[execution_count] AS [Avg Logical Reads],
[qs].[total_logical_writes] AS [Total Logical Writes],
[qs].[total_logical_writes] / [qs].[execution_count] AS [Avg Logical Writes],
[qs].[total_elapsed_time] AS [Total Duration],
[qs].[total_elapsed_time] / 1000000.0 AS [Total Duration seconds],
[qs].[total_elapsed_time] / [qs].[execution_count] / 1000000.0 AS [Avg Duration seconds],
[qp].[query_plan] AS [Plan],
[qs].[creation_time],
[qs].[last_execution_time]
FROM [sys].[dm_exec_query_stats] AS [qs]
CROSS APPLY [sys].dm_exec_sql_text([qs].[sql_handle]) AS [qt]
CROSS APPLY [sys].dm_exec_query_plan([qs].[plan_handle]) AS [qp]
WHERE
--/* most often used */ [qs].[execution_count] > 50
--/* most CPU intensive */ [qs].[total_worker_time] / [qs].[execution_count] > 100 --[Avg CPU Time (ms)]
--/* most read IO */ [qs].[total_physical_reads] / [qs].[execution_count] > 1000 --[Avg Physical Reads]
--/* most intesive in reads */ [qs].[total_logical_reads] / [qs].[execution_count] > 1000 --[Avg Logical Reads]
--/* most IO intensive in write */ [qs].[total_logical_writes] / [qs].[execution_count] > 1000 --[Avg Logical Writes]
/* longest queries */ [qs].[total_elapsed_time] / [qs].[execution_count] > 1000 --[Avg Duration (ms)]
AND DB_NAME([qt].[dbid]) NOT IN ( 'master', 'msdb', 'model', 'tempDb' )
ORDER BY
--[Avg Logical Reads] DESC
--[qs].[execution_count] DESC
--,[qs].[total_worker_time] / [qs].[execution_count] DESC
--,[qs].[total_physical_reads] / [qs].[execution_count] DESC
--,[qs].[total_logical_reads] / [qs].[execution_count] DESC
--,[qs].[total_logical_writes] / [qs].[execution_count] DESC;
/* average duration */ [qs].[total_elapsed_time] / [qs].[execution_count] DESC;
SELECT TOP 100
'most cpu' AS msg,
GETDATE() AS [Collection Date],
[qs].[execution_count] AS [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],
DB_NAME([qt].[dbid]) AS [DB Name],
[qs].[total_worker_time] AS [Total CPU Time],
[qs].[total_worker_time] / [qs].[execution_count] AS [Avg CPU Time (ms)],
[qs].[total_physical_reads] AS [Total Physical Reads],
[qs].[total_physical_reads] / [qs].[execution_count] AS [Avg Physical Reads],
[qs].[total_logical_reads] AS [Total Logical Reads],
[qs].[total_logical_reads] / [qs].[execution_count] AS [Avg Logical Reads],
[qs].[total_logical_writes] AS [Total Logical Writes],
[qs].[total_logical_writes] / [qs].[execution_count] AS [Avg Logical Writes],
[qs].[total_elapsed_time] AS [Total Duration],
[qs].[total_elapsed_time] / 1000000.0 AS [Total Duration seconds],
[qs].[total_elapsed_time] / [qs].[execution_count] / 1000000.0 AS [Avg Duration seconds],
[qp].[query_plan] AS [Plan],
[qs].[creation_time],
[qs].[last_execution_time]
FROM [sys].[dm_exec_query_stats] AS [qs]
CROSS APPLY [sys].dm_exec_sql_text([qs].[sql_handle]) AS [qt]
CROSS APPLY [sys].dm_exec_query_plan([qs].[plan_handle]) AS [qp]
WHERE
--/* most often used */ [qs].[execution_count] > 50
/* most CPU intensive */ [qs].[total_worker_time] / [qs].[execution_count] > 100 --[Avg CPU Time (ms)]
--/* most read IO */ [qs].[total_physical_reads] / [qs].[execution_count] > 1000 --[Avg Physical Reads]
--/* most intesive in reads */ [qs].[total_logical_reads] / [qs].[execution_count] > 1000 --[Avg Logical Reads]
--/* most IO intensive in write */ [qs].[total_logical_writes] / [qs].[execution_count] > 1000 --[Avg Logical Writes]
--/* longest queries */[qs].[total_elapsed_time] / [qs].[execution_count] > 1000 --[Avg Duration (ms)]
AND DB_NAME([qt].[dbid]) NOT IN ( 'master', 'msdb', 'model', 'tempDb' )
ORDER BY
--[Avg Logical Reads] DESC
--[qs].[execution_count] DESC
/* cpu intensive */ [qs].[total_worker_time] / [qs].[execution_count] DESC;
--,[qs].[total_physical_reads] / [qs].[execution_count] DESC
--,[qs].[total_logical_reads] / [qs].[execution_count] DESC
--,[qs].[total_logical_writes] / [qs].[execution_count] DESC;
--/* average duration */,[qs].[total_elapsed_time] / [qs].[execution_count] DESC
SELECT TOP 100
'most physical reads' AS msg,
GETDATE() AS [Collection Date],
[qs].[execution_count] AS [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],
DB_NAME([qt].[dbid]) AS [DB Name],
[qs].[total_worker_time] AS [Total CPU Time],
[qs].[total_worker_time] / [qs].[execution_count] AS [Avg CPU Time (ms)],
[qs].[total_physical_reads] AS [Total Physical Reads],
[qs].[total_physical_reads] / [qs].[execution_count] AS [Avg Physical Reads],
[qs].[total_logical_reads] AS [Total Logical Reads],
[qs].[total_logical_reads] / [qs].[execution_count] AS [Avg Logical Reads],
[qs].[total_logical_writes] AS [Total Logical Writes],
[qs].[total_logical_writes] / [qs].[execution_count] AS [Avg Logical Writes],
[qs].[total_elapsed_time] AS [Total Duration],
[qs].[total_elapsed_time] / 1000000.0 AS [Total Duration seconds],
[qs].[total_elapsed_time] / [qs].[execution_count] / 1000000.0 AS [Avg Duration seconds],
[qp].[query_plan] AS [Plan],
[qs].[creation_time],
[qs].[last_execution_time]
FROM [sys].[dm_exec_query_stats] AS [qs]
CROSS APPLY [sys].dm_exec_sql_text([qs].[sql_handle]) AS [qt]
CROSS APPLY [sys].dm_exec_query_plan([qs].[plan_handle]) AS [qp]
WHERE
--/* most often used */ [qs].[execution_count] > 50
--/* most CPU intensive */ [qs].[total_worker_time] / [qs].[execution_count] > 100 --[Avg CPU Time (ms)]
/* most read IO */ [qs].[total_physical_reads] / [qs].[execution_count] > 1000 --[Avg Physical Reads]
--/* most intesive in reads */ [qs].[total_logical_reads] / [qs].[execution_count] > 1000 --[Avg Logical Reads]
--/* most IO intensive in write */ [qs].[total_logical_writes] / [qs].[execution_count] > 1000 --[Avg Logical Writes]
--/* longest queries */[qs].[total_elapsed_time] / [qs].[execution_count] > 1000 --[Avg Duration (ms)]
AND DB_NAME([qt].[dbid]) NOT IN ( 'master', 'msdb', 'model', 'tempDb' )
ORDER BY
--[Avg Logical Reads] DESC
--[qs].[execution_count] DESC
--,[qs].[total_worker_time] / [qs].[execution_count] DESC
/* most disk activity */ [qs].[total_physical_reads] / [qs].[execution_count] DESC;
--,[qs].[total_logical_reads] / [qs].[execution_count] DESC
--,[qs].[total_logical_writes] / [qs].[execution_count] DESC;
--/* average duration */,[qs].[total_elapsed_time] / [qs].[execution_count] DESC
Reference in New Issue View Git Blame Copy Permalink