November 23, 2010 at 5:51 pm
If i need to find which queries have run on the database in a particular time period lets say 3 pm and 8 pm, will the following query fullfill the requriement? Please confirm
SELECT DB_Name(qp.dbid) as dbname , qp.dbid , qp.objectid , qp.number
, qt.text
, SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text
, qs.creation_time , qs.last_execution_time , qs.execution_count
, qs.total_worker_time / qs.execution_count as avg_worker_time
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE last_execution_time >= '20101123 15:00'
AND last_execution_time <'20101123 20:00'
Linked to above, is it possible to find the worst performing query running on the db server in particular time period for example 3 pm and 8 pm?
November 24, 2010 at 2:50 am
sys.dm_exec_query_stats gives you cummulative stats for query plans that have been executed.
Trace will suit your requirement. Run a trace during that time and sort on duration to see which all queries took maximum time. You can also group them to find out which all queries were run for maximum number of times and avg duration taken by them.
April 14, 2011 at 7:36 am
Hi pradeep
Can you tell me how to enable trace and all that you mentioned above
April 14, 2011 at 7:46 am
http://qa.sqlservercentral.com/stairway/72363/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply