I have a stored procedure in SQL Server 2005 (SP2) which contains a single query like the following (simplified for clarity)


When this proc is run I can see the plan appear in sys.dm_exec_query_stats with a high 'total_worker_time' value. However, when I run it in SQL Management Studio the statistics show a very low CPU time, as I'd expect. Although the query takes some time to return, because of the slowness of the server it is talking to, it doesn't return many rows.

I'm aware of the issue that parallel queries in SQL Server 2005 can present odd CPU times, which is why I've tried to turn off any parallism with the query hint (though I don't think that there was any in any case).

I don't know how to account for the discrepancy - can anyone give me a steer?

UPDATE: I was assuming that the problem was with the OPENQUERY so I tried looking at times for a long-running query which doesn't use OPENQUERY. In this case the statistics (gained by setting STATISTICS TIME ON) reported the CPU time at 3315ms, whereas the DMV gave it at 0.511ms. The total elapsed times reported by each method agreed.


In sys.dm_exec_query_stats total_worker_time is:

Total amount of CPU time, in microseconds, that was consumed by executions of this plan since it was compiled.

So it is the total CPU time for all executions of the cached plan. The execution_count column gives the number of times the complete plan has executed, and there is also information about the lowest and highest values recorded. The figures from the DMV are in microseconds, whereas STATISTICS TIME output shows milliseconds. Finally, the MAXDOP hint only applies to the portion of the query that runs locally.

I noticed you are running SQL Server 2005 SP2. There were all sorts of timing issues with that (now unsupported) release, for some detail, see:

SQL Server 2005 SP2 will introduce new messages to the error log related to timing activities.

Consider moving to 2005 SP4.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.