0

I keep seeing rows in "Recent Expensive Queries" for queries that have not been run for hours.

This confuses me when trying to figure what is snarling things up.

Is there a way to add start time column to view - or somehow filter old queries out?

1

There are no such options!

However, you can create your own result set as shown in the following snippet.

Please, notice a couple of things.

First, you can change a period of time you are interested in by changing the first line of the snippet.

Second, the WHERE clause is applied on the last_execution_time column.

And, finally you can change the order if you are interested in expensive queries based on CPU or logical writes.

DECLARE @filterDateTime DATETIME = '2019-10-16 11:09:00.000'

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), 
              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 last_execution_time >= @filterDateTime
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
  • That is very useful! The total_worker_time is a very large number, probably because execution_count is high. If I divide total_worker_time by execution_count, does that give me the average cpu time for running the query once? – ManInMoon Oct 16 at 11:53
  • Also, is there anyway to add columns that shows what app or command line, initiated the query? – ManInMoon Oct 16 at 12:05
  • sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans in SQL Server. So, there is no details. – Darko Martinovic Oct 16 at 12:25
  • I didn't notice your first comment. The answer is yes! – Darko Martinovic Oct 16 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.