April 17, 2009 at 4:19 am
select substring(b.text, (a.statement_start_offset/2)+1 , ((case a.statement_end_offset when -1 then datalength(b.text) else a.statement_end_offset end - a.statement_start_offset)/2) + 1) as statement_text, a.sql_handle, a.plan_handle, a.creation_time, c.cacheobjtype, c.objtype,b.dbid, b.objectid,c.refcounts, c.usecounts, a.execution_count, a.last_execution_time, a.total_worker_time, a.total_elapsed_time,a.last_elapsed_time, c.size_in_bytes from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(a.sql_handle) b , sys.dm_exec_cached_plans c where a.plan_handle = c.plan_handle order by 1
When we executed below query in sql server 2005 - 9.00.4035.00 running on windows 2003 R2 sp1 intel 2 x quadar core xeon processor (i.e 8 CPU) system we are getting for same sql statement multiple plan handles and some confusion results, please find part of the result in attachment xls file.
In above result the first two rows is for same statement we are having two plans, in the first row sql creation time at 2009-04-15 18:43:09.493 with execution count 3, plan use count 4, last_execution_time 2009-04-15 18:43:14.960 and surprisingly the total_worker_time & total_elapsed_time are 0. In second row for same sql statement sql creation time at 2009-04-15 18:19:19.563 with execution count 8, plan use count 8, last_execution_time 2009-04-15 18:42:55.830.
My questions for these two rows:
1.If execution count is 3 then why total_worker_time & total_elapsed_time are 0.
Similarly for row 3 and 4 creation time at 2009-04-15 18:49:32.320 with execution count 8, plan use count 8, last_execution_time 2009-04-15 18:54:45.413 and surprisingly the last_execution_time 2009-04-15 18:54:38.843 for 4th row is greater than 3 row creation time and total_worker_time & total_elapsed_time are 0. And also the pla_use_count is 10 and the execution_count is 4.
My questions for these two rows:
1.If a new plan is created at 2009-04-15 18:49:32.320 for the sql (i..e row 3) then why the old plan is tried at 2009-04-15 18:54:38.843 and also the total worker_time & total_elapsed_time are 0.
Note: max degree of parallelism is set 0.
Can anyone help me to understand the mechanism behind cache plan management?
And also if we set max degree of parallelism is set 0 how can I get the total_worker_time & total_elapsed_time?
April 17, 2009 at 7:00 am
If you have multiple plan handles, have you looked at the plans? For the same query, you'll get two plans when parallelism is invoked.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply