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

Question

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
,STATUS = ses.STATUS
,[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
WHERE st.text IS NOT NULL

Any thoughts on it.

Regards,

Zahid

Answer 1

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.

Answer 2

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.

Regards
Echo

---

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.

Answer 3

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.

Answer 4

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