Clock Time is over 2 minutes, but Duration is .684 at end?

Gemini_X 2017-12-21 16:43:28

What is Duration? I would think it's the total time it took for the query to run. However, every query I am testing goes much longer in real-time than what ends up showing under Duration.

I posted this question here and Aaron responded:

"While a query is running, we show clock time on the status bar. However, at the end, we sum up the total duration, in milliseconds, as reported by the trace rows we collected. We subtract duration from any trace rows that are discarded (e.g. events that don't generate plans, like WAITFOR). If you send your .pesession file to we can help you take a closer look. "

So, here is one of my plans. I'm just trying to understand why there is such a difference. I want to know how long the query takes to run in real-time, since that's what the end-user will experience.


Aaron Bertrand 2017-12-21 16:52:44
According to the trace row, the query started at 14:54 and ended later the same second:

alt text

The history view also shows that it took less than a second to run the query (though others took as long as 12 seconds):

alt text

Is it possible that the longer runtime is due to establishing connectivity, gaining access to the database, or other activity before the query actually gets issued? You'll need to figure out what your connection is doing for two minutes before it's allowed to actually issue the query – seems environment-related but it's tough to guess exactly what is going on. During those two minutes, have you looked at sys.dm_exec_requests?

Gemini_X 2017-12-21 17:37:42
I had wondered if there could be some connection issue between SentryOne Plan Explorer and the database, but the query also takes approximately 2 minutes on the actual SQL Server as well.

UNTIL NOW, WTF, lol. OK, suddenly this query is running in less than a couple of seconds on the actual server, and via Plan Explorer.

I did reboot the SQL Server last night, maybe something got cleaned up? So, if I'm understanding correctly, something else was causing a delay (connection, processes, etc.) aside from the actual query. The Duration time is showing how long it actually took for the query to run – minus all the other garbage that was slowing the processing down? Does that sound about right?

I've never looked at sys.dm_exec_requests before, so that's going to be handy!

I've been changing this query a little at a time and comparing the plans so I can see how to improve my coding, and the duration part was throwing me off.

Thanks for the help Aaron!

I love this product!! 🙂

Aaron Bertrand 2017-12-21 18:19:18
Yeah, it sounds like your query was never slow, but something environmental was holding up your query from even starting.