October 27, 2017 at 2:13 pm
Hi,
Is it possible to find a query execution count in certain time period?
For example, a user executes "select *from emp where id=10" several times a day. Can I find how many times he executed the query in 2 pm to 4 pm for a particular day?
October 27, 2017 at 2:57 pm
SQL Server only stores execution information for a query for all users for the duration the execution of the plan is cached in sys.dm_exec_query_stats. If you need specific user for specific times, you would probably have to do an extended events session or a trace.
October 28, 2017 at 1:21 am
Chris Harshman - Friday, October 27, 2017 2:57 PMSQL Server only stores execution information for a query for all users for the duration the execution of the plan is cached in sys.dm_exec_query_stats. If you need specific user for specific times, you would probably have to do an extended events session or a trace.
Hi,
I got some output with below query-
SELECT a.last_execution_time AS [Time], dest.text AS [Query]FROM sys.dm_exec_query_stats AS aCROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS destwhere a.creation_time > '2017/9/25' and a.last_execution_time < '2017/9/26'ORDER BY a.last_execution_time DESC
Dosn't the information is historical that has been saved in sql server/cache?
It match with my required information. Can you verify?
October 28, 2017 at 7:52 am
sys.dm_exec_query_stats stores aggregate information about queries currently in cache. It won't show executions for any given moment in time. It's only ever aggregate information. It's not historical. Further, anything that removes a query from cache (statistics updates, age, memory pressure, FREEPROCCACHE, failovers, reboots, etc.) will remove all the data in sys.dm_exec_query_stats.
In short, that DMV, as well as sys.dm_exec_procedure_stats, is very useful for looking at recent behavior. It's not good for historical behavior or behavior during a particular time period. For specific and historical information, you should be using extended events. If you're on 2016 or better, there's also Query Store. It's also aggregate information, but aggregated based on time factors, so you can see some historical data with it, just not specific calls.
"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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply