Categories:

One of the important things that we look at to improve performance in database environments that have performance problems is the ones to find out queries that use the most CPU and Disk.

The queries that use the CPU most can be identified and make the necessary improvements.
Use the query:

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) as 'query'
,qs.execution_count as 'exec count'
,qs.total_logical_reads as 'total logical reads'
,qs.total_logical_writes as 'total logical writes'
,qs.total_worker_time as 'total worker time'
,qs.last_logical_reads as 'last logical reads'
,qs.last_logical_writes as 'last logical writes'
,qs.last_worker_time as 'last worker time'
,qs.total_elapsed_time/1000000 as 'total elapsed time(s)'
,qs.last_elapsed_time/1000000 as 'last elapsed time(s)'
,qs.last_execution_time as 'last execution time'
,qp.query_plan as '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 qs.total_logical_reads DESC

Tags:

4,011 Responses

Leave a Reply