|
|
| 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 |  | | |
|
|