How come sys.dm_exec_requests.cpu_time never moves?

Today, I want to point out another SQL Server 2012 SP2 fix that may affect your performance troubleshooting. When you are troubleshooting a long running query, chances are you will use sys.dm_exec_requests to look for progresses of the query. But one of the column – cpu_time is not accurate prior to SQL Server 2012 SP2.
When a query runs in parallel, main thread just coordinates things and it's the child threads that do meaningful work. Prior to SP2, the sys.dm_exec_requests.cpu_time doesn't roll up child threads' cpu while the query is in progress. At end of the query, the CPU times spent will be all rolled up though.

To Illustrate the problem, I came up with a query that can generate parallel plan and do testing on a SQL 2012 SP1 and SP2 instances.

First, I ran the query on SP1 instances. In order to see if the query is run in parallel, I used query the following DMVs sys.dm_exec_requests and sys.dm_os_tasks. I could see that the spid 51 had 55 tasks.
select t.session_id, count (*) 'number of tasks'
from sys.dm_exec_requests r join sys.dm_os_tasks t on r.session_id =t.session_id
group by t.session_id
having count (*) > 1

But after 1 minutes into running, the cpu_time still showed 0.

When I forced a serial plan, I see the cpu_time increased correctly as time went by.

Now let's test the same query in SP2 which fixed the problem. Again, I verified that the query has 54 tasks associated with it using the same DMV query above.

Query "select cpu_time, * from sys.dm_exec_requests where session_id = 52" shows that cpu_time kept increasing as time went by.

Jack Li |Senior Escalation Engineer | Microsoft SQL Server Support