Identify the most expensive queries running on table

I was investigating SQL Server performance issue on a table there i was getting more locks, query timeout expire, on a table that have more than hundred columns. So I was just wanted to know what are the most expensive queries running on that SQL Server table. 

I would like to share my script that will return most expansive query running on a table, Hare i am using DVM to find most expansive query. Suppose we have an table called "calls"

SELECT TOP 15 SQLQueries=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),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
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
where 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) like '% calls %'
-- ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
ORDER BY qs.total_worker_time DESC -- CPU time

Above script will return the most expansive query running on "calls" table. you can change order by clause on Logical reads, Logical writes or on CPU time.

Please note: Running in your environment replace "calls" from script to your table name.  ie % calls %

 

Comments are closed