Archive for 8. juuni 2016

MSSQL: aeglaste päringute tuvastamine

juuni 8, 2016

Vahel on palju tööd, et leida päringud, mis SQL serverit mõttetult koormavad, SQL trace pole ka alati parim abivahend.
Antud päring aitab aeglaselt jooksvad päringud tuvastada.

SELECT TOP 50
[Average CPU used] = total_worker_time / qs.execution_count,
[Total CPU used] = total_worker_time,
[Execution count] = qs.execution_count,
[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(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)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

Advertisements