July 18, 2012 at 12:12 pm
HI all, Below is 2 query by which i want to fetch query by it execution time for example today is 18 July 2012
i want the query which had run on a particular duration like (between '2012-07-18 10:30:00.000' and '2012-07-18 11:00:00.000'.)BUT both of the Query are not able to do so as the Duration column(Start_time,creation_time&last_execution_time) always changes can u provide me any other (Dmv or DMO )where the query execution time doesn't differ or change at all still the sql server restarts or any updates of statics or Re-indexing happens.
NOTE-I know we can do this by sql server profiler.BUT IF any one have a solution by Any DMV or DMO I would appreciate or any other suggestion that how to make it done are WelCome,
SELECT top 50 ER.Start_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
,qs.total_elapsed_time as total_elapsed_time_BY_Micro_SEC
, qs.total_elapsed_time/1000 as total_elapsed_time_BY_MILI_SEC
,qs.total_elapsed_time / execution_count as avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
JOIN sys.dm_exec_requests ER ON qs.sql_handle = ER.sql_handle
where ER.Start_time between '2012-07-18 10:30:00.000' and '2012-07-18 11:00:00.000'
and qs.total_elapsed_time/1000 >= 20000
ORDER BY ER.Start_time DESC;
SELECT top 50 creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
,total_elapsed_time total_elapsed_time_BY_Micro_SEC
, total_elapsed_time/1000 total_elapsed_time_BY_MILI_SEC
, total_elapsed_time / execution_count avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where Creation_time between '2012-07-18 10:00:00.000' and '2012-07-18 11:00:00.000'
and total_elapsed_time/1000 >= 20000
ORDER BY Last_execution_time DESC;
July 20, 2012 at 4:51 am
The DMOs related to query performance are basically, what is absolutely executing right now, sys.dm_exec_requests, or, an aggregate of queries that have executed for the queries currently in cache, sys.dm_exec_query_stats (and in 2008+ sys.dm_exec_procedure_stats). You can't get a specific execution of a query in the past from any of the DMOs. It's current execution or an aggregation of past executions from the cache. Those are your choices. If you need to capture executions, a server-side trace is the way to go (until you upgrade to 2008+ when you can start using Extended Events).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply