1

I am running the following script to find most expensive queries.

However I want to narrow it down to only queries ran on Saturday 18th October 2014 Is there any way to do this?

SELECT TOP 50 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
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

ps I got this script from http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/

The server is SQL Express 2012

3

No, the DMVs track aggregates, not individual executions, so you can't pinpoint things like that.

Want to drill in, you'll need to capture queries using server-side trace*, extended events, management data warehouse or one of many 3rd party monitoring tools. The latter cost money but, IMHO, you get what you pay for. We have one I could recommend...

* Note: trace and profiler are deprecated, so it is highly recommended to use extended events going forward.

  • Is there anyway I can reset the stats the DMVs use at the start of a day so I can then just look at them over that day? – DermFrench Oct 20 '14 at 12:18
  • Aaaron can you go ahead and recommend one. – DermFrench Oct 20 '14 at 12:29
  • Sure, you can clear out the procedure cache nightly using DBCC FREEPROCCACHE; - just note that it will cause a performance hit the first time any of these folks run queries because the plans will have to be recompiled. As for recommendations, SQL Sentry Performance Advisor. Disclaimer: I work for SQL Sentry. – Aaron Bertrand Oct 20 '14 at 12:59

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.