People think that we who work with databases are boring people -I say NO, we´re…
Yet another flaw in the DMVs. Apparently the designer of the dm_exec_requests has it’s own visions and ideas about how time flows. 🙂 Let me explain.
The following script:
select R.session_id , R.blocking_session_id , T.text , r.status , DB = db_name(R.database_id) ,R.total_elapsed_time , R.percent_complete , R.estimated_completion_time from sys.dm_exec_requests R cross apply sys.dm_exec_sql_text(R.sql_handle) T
Shows the cumulative statistics for sessions and their total_elapsed_time. (total_elapsed_time is measuring the performance statistics for a cached plan, and specifically its total elapsed time in microseconds for the completed executions of the chached plan). Hm, a quick note comes to mind: wait a second – how does this deal with parallelism? After all, we can have 2 plans for the same query – one for parallel execution and one for single CPU. What time is accounted for in this DMV in the total_elapsed_time in either case?
The parallelism aside, I’d like to point out the paradox of this field though. I find it remarcable that the field may contain a negative value. Apparently, the DMVs designers’ idea of time is a bit crooked and futuristic: if we have a query whose plan remains cached for a long time and the query keeps executing at times, eventually the value in the total_elapsed_time column will overflow its limit and will roll over and start flowing up from the negative values. I could go on a rant here and ask philosophical questions at large: what is up, what is down and how do you consider measuring time in negative values. But I think noone will care to read the rant, so I will spare it. Instead, I will give a real life example of the occurence of this case: a BizTalk server.
