This is no work of mine but I wanted to highlight it since it´s such a good thing to utilise when optimizing your database and queries.

SELECT
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.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qs.creation_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
qt.encrypted = 0
ORDER BY
qs.total_logical_reads DESC

You get all recently executed queries (which in itself can be useful for a legacy database), the execution count and times aswell as the last query plan.

Sql query stats example

Something you need to pay attention to is that the timed values here, like total_elapsed_time, is saved in microseconds instead of the usual millisecond. This means that to get the average time you would need to:

SELECT (qs.total_elapsed_time / qs.execution.count) / 1000000
Why not join the newsletter?

Why not join the newsletter?

If you're not a fan of facebook or twitter, sign up to the newsletter and I'll send you a quick update every so often with what's going on and new posts coming out.

You have Successfully Subscribed!