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.
Just working from Paul's comment. The key difference between the statement in Books Online and your interpretation:
It says: ... one row per cached stored procedure plan.
You read: ... one row per cached stored procedure.
You can check which plan attributes are different and thus leading to different copies of the plan by looking at the contents of the attributes DMV, ordering by attribute name. You should see at least one attribute where two rows for the same text have different values in the value column.
SELECT t.[text], pa.attribute, pa.value
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS pa
WHERE LOWER(t.[text]) LIKE N'%create%procedure%procedure_name%'
AND LOWER(t.[text]) NOT LIKE N'%sys.dm%'
AND pa.is_cache_key = 1
ORDER BY t.[text], pa.attribute;
Typically, you will see differences in one of these two attributes:
set_options
This is due to runtime differences in settings like ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, etc. I talk about this in the following post:
user_id
This isn't really the user running the query, but rather it is due to two different users having different default schemas (the value is actually schema_id), and at least one of them is calling the stored procedure without the schema prefix (SQL Server caches a different plan because the search path is different - it must check the default schema first). I talk about this in the above post as well as the following:
(Also, be sure you are looking at the overarching plan for the procedure, not two plans for two completely different statements within the stored procedure, e.g. WHERE p.objtype = N'Proc'.)
Best Answer
Things like select count(*) are just fine as long as the where clause is indexed correctly.
DTA may or may not be of any use.
The actual and estimated plan are what you need to look at. It'll tell you if there are any indexes which are missing. The indexes which are used will be in the execution plan.