elapsed_time value more than query execution time?

Radhai Krish 191 Reputation points
2024-06-20T11:09:53.1166667+00:00

I did take query from here in order to check performance of our queries.

  1. Executed a SP which took execution time of 1 min 45 seconds.
  2. Ran the query from the link which uses the view 'sys.dm_exec_query_stats'
  3. Highlighted rows in the screenshots are from same SP.
  4. Found that the elapsed time for those query parts of the SP taking time more than the execution time of the SP.

Am I missing any understanding? Please help me understand the functioning of the view. - Thanks in advance.

ExecutionTime_QueriesWithinSP

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,340 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 106.8K Reputation points
    2024-06-20T21:48:26.1033333+00:00

    The times reported are in microseconds (but only accurate to the millisecond.) This, query on the first line ran for 21 seconds in the most recent execution. That fits in with the total execution time of 1:45 for the entire SP, or am I missing something?

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. LiHongMSFT-4306 25,651 Reputation points
    2024-06-21T02:52:04.8833333+00:00

    As explained in the article you post, the last_elapsed_time column shows how long it took to execute the most recent request in microseconds. The last_worker_time column indicates how long the task has spent doing work, excluding any time it was blocked.

    If the last_worker_time column is significantly lower than the last_elapsed_time column, this could indicate that another process is causing the query to wait before being able to run. This is known as a waiting query.

    User's image

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    1 person found this answer helpful.
    0 comments No comments