question

ZahidNadeem-8256 avatar image
0 Votes"
ZahidNadeem-8256 asked EchoLiu-msft commented

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

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @ZahidNadeem-8256,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.