How can I find out what my SqlServer 2005 CPU is mostly working on? I apologize if this is somewhat of a rookie question.

After looking online I found some articles that explain how to find the worker time per query using sys.dm_exec_query_stats. e.g.

SELECT TOP(10)
creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 AS total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime] , execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_worker_time &gt; 0
ORDER BY total_worker_time DESC


from here.

But according to Microsoft the total_work_time only tells us the amount of time spent after the plan has been generated, not for instance, the amount of time spent building the plan for that query.

Is there a way to determine how much CPU is being spent in building the plans, and better yet, a simple breakdown of percent CPU by task (e.g. building plans, executing queries, indexing).

Thank you in advance.
Ref: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-time-transact-sql

You can use SET STATISTICS TIME ON.

You can get a breakdown of:


SQL Server parse and compile time
SQL Server Execution Times


You can turn this on in Management studio for all queries or per session.