dm_exec_query_stats returning stale data?
- by VoiceOfUnreason
I've been testing my app on a SQL Server 2005 database, and am trying to establish a preliminary picture of the query performance using sys.dm_exec_query_stats.
Problem: there's a particular query that I'm interested in, because total_elapsed_time and last_elapsed_time are both large numbers. When I tickle my app to invoke that query (this runs successfully), then refresh my view of the stats, I find that
1) execution_count has incremented (expected)
2) last_execution_time has updated to now (expected)
3) last_elapsed_time is still a large value (not expected - I anticipated a new value)
4) total_elapsed_time is unchanged (contradiction?)
If last_elapsed_time refers to the execution that happened @ last_execution_time, then the total_elapsed_time should have increased?
This documentation: http://msdn.microsoft.com/en-us/library/ms189741(SQL.90).aspx tells me that last_execution_time is the last time the plan was executed, and last_elapsed_time comes from the "most recently executed plan", but doesn't tell me why those might be different.
The query itself is uncomplicated (SELECT/WHERE/ORDER BY - parameters appearing in the where clause, but no clever operations), the table has maybe 25 rows in it right now.
Questions:
1) What's the real relationship between execution_count, last_execution_time, and last_elapsed_time?
2) Where is the documentation of this relationship (manual, third party book, blog, bug ticket, stone tablets...) ?
