SQL queries with elapsed time shown as 23:59:59.9970000

Zahid Butt 556 Reputation points

Hi ,

In SQL server some times a query/process is shown with elapsed time 23:59:59.9970000 after below query but actually its not.

SELECT distinct SPID = er.session_id
,[Login] = ses.login_name
,Host = ses.host_name
,BlkBy = er.blocking_session_id
,DBName = DB_Name(er.database_id)
,CommandType = er.command
,ObjectName = OBJECT_NAME(st.objectid)
,CPUTime = er.cpu_time
,StartTime = er.start_time
,TimeElapsed = CAST(GETDATE() - er.start_time AS TIME)
,SQLStatement = st.text
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id

Any thoughts on it.



A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,583 questions
{count} votes

Accepted answer
  1. Viorel 114K Reputation points

    I think that your query executes GETDATE, then proceeds to read from database. During the preparations, some parallel process executes GETDATE almost simultaneously with your query and manages to write a row. Then your query reads this row. Therefore, your GETDATE is less than er.start_time. The negative difference is displayed as “23:59:59...”. Probably you can detect and display such negative results as 0 or as another smallest time value.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points

    Restrict your query to session_id > 50. Many system processes run when the server starts and never stop.

    0 comments No comments

  2. Erland Sommarskog 104.2K Reputation points MVP

    Yes, as Viorel suggests, this is due to time is seemingly going backwards. This is not unheard of in modern computers, where the different cores may not be in exact sync. Not the least if there is a balanced power plan, which causes the clock frequency to vary.

    0 comments No comments

  3. EchoLiu-MSFT 14,576 Reputation points

    Hi @Zahid Butt ,

    As Viorel said, this may be a time difference in the execution process.

    If you want to get the value of TimeElapsed, you can execute CAST(GETDATE()-er.start_time AS TIME) after the execution is complete.

    If you have any question, please feel free to let me know.


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments