CODEWITHSUNDEEP
sql-serversystem-views
CJH
CJH

Reputation: 1594

SQL sys.dm_exec_query_stats Last_Elapsed_Time nonsense

I am working on a script I can deploy to any SQL server that will give some meaningful statistical information on the servers usage. I quite quickly realized though that I was getting some rather suspect results back from the last_elapsed_time column on sys.dm_exec_query_stats. Take the following script for example:

select
    creation_time,
    last_elapsed_time                                       [last_elapsed_time_ms],
    last_worker_time                                        [last_worker_time_ms],
    DATEDIFF(MILLISECOND, creation_time, getdate())         [since_creation_time_ms],


    convert(varchar(20), DATEDIFF(DAY, 0, CONVERT(VARCHAR,DATEADD(ms,last_elapsed_time,0),113))) + ' days ' +
    CONVERT(VARCHAR,DATEADD(ms,last_elapsed_time,0),114)    [last_elapsed_time_format],

    convert(varchar(20), DATEDIFF(DAY, 0, CONVERT(VARCHAR,DATEADD(ms,last_worker_time,0),113))) + ' days ' +
    CONVERT(VARCHAR,DATEADD(ms,last_worker_time,0),114)     [last_worker_time_format],

    convert(varchar(20), DATEDIFF(DAY, creation_time, getdate())) + ' days ' +
    CONVERT(VARCHAR,DATEADD(ms,DATEDIFF(MILLISECOND, creation_time, getdate()),0),114)  [since_creation_time]

from sys.dm_exec_query_stats
order by last_elapsed_time desc

I run this and here is a sample of the result I get:

![enter image description here

Looking specifically at the 2nd, 3rd 4th etc.. rows in my results =- My question is basically, how can the elapsed time be greater than the 'Since Creation Time'??

Surely if it was created only 1 hour ago, how on earth can it have taken 12+ Hrs as reported by the last elapsed time?

I know I am probably missing a fundamental meaning about this column somewhere but I just cannot see where. I have gone through the Microsoft Document for this view:

sys.dm_exec_query_stats (Transact-SQL)

where is describes the last_elapsed_time column as:

Elapsed time, reported in microseconds (but only accurate to milliseconds), for the most recently completed execution of this plan

Surely if it was created only 1 hour ago I shouldn't expect a result any greater than that??

Please could someone help me out here... I am starting to question my own sanity!

Upvotes: 2

Views: 2368

Answers (1)

SQLChao
SQLChao

Reputation: 7847

Last elapsed time is measured in microseconds. Your since_create_time_ms calculation is in milliseconds

DATEDIFF(ms, creation_time, getdate())         [since_creation_time_ms]

Upvotes: 4

Related Questions