Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

SELECT * FROM OPENQUERY(MYODBC, 'SELECT * FROM MyTable WHERE Option = ''Y'' ')
OPTION (MAXDOP 1)    

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.

share|improve this question

1 Answer 1

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.

share|improve this answer
    
All this is true. However: I tested this by clearing the cache and then running the stored proc once. The DMV gave me 34762.196ms 'total worker time' and the statistics reported by Management Studio gave me 6828ms CPU time. I'm not getting confused by the difference between microseconds and milliseconds. The MAXDOP hint was just out of desperation - the execution plan shows no sign of parallelism. –  Yellowfog Jan 25 '12 at 9:41
1  
Just noticed you are running 2005 SP2. There were all sorts of timing issues with that (now unsupported) release, for some detail, see blogs.msdn.com/b/psssql/archive/2006/11/27/… Consider moving to 2005 SP4. –  Paul White Jan 25 '12 at 18:59
    
Thanks, that's really interesting. I'm trying to find a reproducible way of getting the problem before we move to SP4. –  Yellowfog Jan 26 '12 at 17:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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