Poniżej przedstawiam sposób w jaki można wyciągnąć z SQL Server listę najbardziej kosztownych zapytań T-SQL. Zapytanie to zwraca do czterej list w podziale na kategorie (kryteria) tego czego szukamy:
- Frequently ran query
- High Disk Reading query
- High CPU query
- Long Running query
Gdy znajdziemy już na liście interesujące nas zapytanie można przejść do ostatniej kolumny tego zapytania, otworzyć plan wykonania i rozpocząć analizę i dostrajanie zapytania.
W zapytaniu tym ustawiamy odpowiednie wartości do uzyskania kategorii oraz możemy określić ile najcięższych zapytań chcemy mieć na liście. Definiujemy to na początku skryptu.
DECLARE @frq bit = 0, -- Frequently ran query
@hdr bit = 1, -- High Disk Reading query
@hc bit = 0, -- High CPU query
@lr bit = 1 -- Long Running query
DECLARE @nTop int = 5
Poniżej został przedstawiony cały skrypt. Powodzenia 😉
/*
-- List most expensive queries
-- Author: Dariusz Brejnak
*/
DECLARE @frq bit = 0, -- Frequently ran query
@hdr bit = 1, -- High Disk Reading query
@hc bit = 0, -- High CPU query
@lr bit = 1 -- Long Running query
DECLARE @nTop int = 5
DECLARE @expquery INT
SET @expquery = POWER(2*@frq,1) -- 2
+ POWER(2*@hdr,2) -- 4
+ POWER(2*@hc,3) -- 8
+ POWER(2*@lr,4) -- 16
PRINT @expquery
IF ( (2 & @expquery) = 2 ) PRINT 'Frequently ran query'
IF ( (4 & @expquery) = 4 ) PRINT 'High Disk Reading query'
IF ( (8 & @expquery) = 8 ) PRINT 'High CPU query'
IF ( (16 & @expquery) = 16 ) PRINT 'Long Running query'
;WITH MostExpensive
AS
(
SELECT
[Execution Count] = qs.execution_count
, [Total Logical Reads (MB)] = (qs.total_logical_reads)*8/1024.0
, [Avg Logical Reads (MB)] = (qs.total_logical_reads/qs.execution_count)*8/1024.0
, [Total Worker Time (ms)] = (qs.total_worker_time)/1000.0
, [Avg Worker Time (ms)] = (qs.total_worker_time/qs.execution_count)/1000.0
, [Total Elapsed Time (ms)] = (qs.total_elapsed_time)/1000.0
, [Avg Elapsed Time (ms)] = (qs.total_elapsed_time/qs.execution_count)/1000.0
, [Creation Time] = qs.creation_time
, [Complete Query Text] = t.text
, [Query Plan] = qp.query_plan
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE t.dbid = DB_ID()
)
, frq as
(
SELECT TOP(@nTop)
des = 'frequently ran query', *
FROM MostExpensive
ORDER BY [Execution Count] DESC -- Frequently ran query
)
, hdr as
(
SELECT TOP(@nTop)
des = 'High Disk Reading query', *
FROM MostExpensive
ORDER BY [Total Logical Reads (MB)] DESC -- High Disk Reading query
)
, hc as
(
SELECT TOP(@nTop)
des = 'High CPU query', *
FROM MostExpensive
ORDER BY [Avg Worker Time (ms)] DESC -- High CPU query
)
, lr as
(
SELECT TOP(@nTop)
des = 'Long Running query', *
FROM MostExpensive
ORDER BY [Avg Elapsed Time (ms)] DESC -- Long Running query
)
SELECT * FROM frq WHERE ( (2 & @expquery) = 2 )
UNION ALL
SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL WHERE ( (2 & @expquery) = 2 )
UNION ALL
SELECT * FROM hdr WHERE ( (4 & @expquery) = 4 )
UNION ALL
SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL WHERE ( (4 & @expquery) = 4 )
UNION ALL
SELECT * FROM hc WHERE ( (8 & @expquery) = 8 )
UNION ALL
SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL WHERE ( (8 & @expquery) = 8 )
UNION ALL
SELECT * FROM lr WHERE ( (16 & @expquery) = 16 )
OPTION (RECOMPILE)
