DB/MS SQL
[DMV] Slowest queries
charom
2011. 9. 27. 10:19
Here is example to find which query is the slowest query in my db.
This is the result of my db.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [Total Elapsed Duration (s)] , qs.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) + 1) AS [Individual Query] , qt.text AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY total_elapsed_time DESC |
This is the result of my db.