|
Definitions More Info. | Definition ID | 35 | Title | SQL | Category | SCRIPT | Definition | top 50 IO consuming queries | Definition Description | SELECT highest_cpu_queries.last_execution_time, highest_cpu_queries.execution_count, q.[TEXT], SUBSTRING( q.text, ( highest_cpu_queries.statement_start_offset / 2 )+ 1, (( CASE highest_cpu_queries.statement_end_offset WHEN - 1 THEN DATALENGTH( q.text ) ELSE highest_cpu_queries.statement_end_offset END - highest_cpu_queries.statement_start_offset )/ 2 ) + 1 ) AS statement_text, highest_cpu_queries.total_worker_time, highest_cpu_queries.total_logical_reads, q.dbid, q.objectid, q.number, q.encrypted, highest_cpu_queries.plan_handle FROM ( SELECT top 50 qs.last_execution_time, qs.execution_count, qs.plan_handle, qs.total_worker_time, qs.statement_start_offset, qs.statement_end_offset, qs.total_logical_reads FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC ) AS highest_cpu_queries CROSS apply sys.dm_exec_sql_text( plan_handle ) AS q --WHERE DATEDIFF(hour, last_execution_time, getdate()) < 1 -- change hour time frame order by highest_cpu_queries.total_logical_reads desc Bir başka şekli (Avg Read) SELECT TOP 50 ( qs.total_logical_reads + qs.total_logical_writes ) / qs.execution_count AS [AVG IO], 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 ) AS query_text, qt.dbid, dbname = db_name( qt.dbid ), qt.objectid, qs.sql_handle, qs.plan_handle FROM sys.dm_exec_query_stats qs CROSS apply sys.dm_exec_sql_text( qs.sql_handle ) AS qt ORDER BY [AVG IO] DESC | RecordBy | cunay | Record Date | 15-02-2012 15:04:56 | Düzenle | | Kopyala | | Sil | | |
|
|