October 19, 2011 at 6:55 am
HI I am looking at DB performance and i have noticed in activity monitor under recent expensive queries i have queries that have a plan count of 700 and higher.
does duplicate plans have an impact on performance? what would the rerecommendedlan count be? 1?
What causes the cache plan to increase and how can this be stopped?
***The first step is always the hardest *******
October 19, 2011 at 7:14 am
You mean 1 plan executed 700 times
or 700 plans executed once?
Neither and both are a problem.
SS manages the ram used for the plan cache and wipes out old & unused plans when needed.
Both are a problem because you seem to have 1 slow query running very often & taking up lots of ressources.
Either way you need to tune that query. Which in the end will give you the most bang for your time.
October 19, 2011 at 8:33 am
You mean 1 plan executed 700 times
If this plan is efficient, you are good to go. It also means the Buffer Cache will have hot data whenever you need.
or 700 plans executed once?
If its 700 different plans you are not utilizing cache (plan / buffer) well. It also indicates use of too many ad-hoc queries (not necessarily). Or many named objects say stored procedures might be performing same (or mostly same) operations but SQL Server treats them differently.
October 19, 2011 at 11:32 am
I havent worked out the number of permuatations (I suspect its several hundred) but you will get a new plan everytime you run the same procedure with a different setting (language, arithabort etc)
Everything else has already been said.
October 19, 2011 at 11:37 am
Can be also because you didn't specify the owner.
Changed the case in the name of something.
cachemiss is real easy to get ;-).
October 19, 2011 at 11:49 am
Ninja's_RGR'us (10/19/2011)
Changed the case in the name of something.
Dynamic/ad-hoc SQL only
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 11:57 am
GilaMonster (10/19/2011)
Ninja's_RGR'us (10/19/2011)
Changed the case in the name of something.Dynamic/ad-hoc SQL only
When did that change?
used to be true in 2K unless the article was wrong.
October 19, 2011 at 12:01 pm
Ninja's_RGR'us (10/19/2011)
GilaMonster (10/19/2011)
Ninja's_RGR'us (10/19/2011)
Changed the case in the name of something.Dynamic/ad-hoc SQL only
When did that change?
Never. It's only ad-hoc SQL that's matched on a hash of the statement text. Objects are matched on the object_id and Proc1, proc1, pRoC1 and PROC1 are the same object (in a case-insensitive DB)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 12:03 pm
Cool, TX.
Oh wait, still on that darn CS server :hehe:.
October 19, 2011 at 12:12 pm
Ninja's_RGR'us (10/19/2011)
Oh wait, still on that darn CS server :hehe:.
In which case the varying different cases either match to an object (and hence a plan) or are errors (and don't get plans). Even on a case-sensitive server, Proc1, proc1 and PROC1 won't get 3 plans if there's really only one object called proc1.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2011 at 5:42 am
Do they all have the same query_plan_hash value?
Sounds like you might be a candidate for Optimize for Ad Hoc workloads, but it's hard to be sure.
"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
October 20, 2011 at 8:50 am
HI thanks for all this info, not sure about 1 plan executing several hundred times how do i find that information? just looking on the activity monitor the column header is plan count and the description if you hover over is number of duplicate plans in cache.
HOw do you identify if they all have the same query_plan_hash value?
***The first step is always the hardest *******
October 20, 2011 at 11:00 am
You'd need to get into querying against the DMOs. sys.dm_exec_query_stats will have it.
"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
October 20, 2011 at 11:23 am
And you can get info on the plans in cache from sys.dm_exec_cached_plans.
Exec_cached_plans - one row per plan in cache
Exec_query_stats - one row per statement in the batch/plan, so there will often be multiple rows per plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2011 at 11:49 am
Thanks ok so i found the below script however, this only works when i run it against the master DB can anyone help????
SELECT TOP 50 creation_time, last_execution_time, total_clr_time,
total_clr_time/execution_count AS [Avg CLR Time], last_clr_time,
execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY total_clr_time/execution_count DESC;
***The first step is always the hardest *******
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply