February 2, 2012 at 10:34 am
This SQL is part of a stored procedure.
When I saw the Activity Monitor it says that SQL Server is making 4 execution plans for this.
------------------------------------------------------------------
if(not exists(select ID from tableA where ID=@param))
begin
return 100
end
------------------------------------------------------------------
I would like to force it to use KEEPFIXED PLAN
How and where can I add ' OPTION (KEEPFIXED PLAN) '
February 2, 2012 at 10:46 am
Why do you want keepfixedplan? Are the compile times of this causing unacceptable load?
How did you identify that 4 plans were used?
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
February 2, 2012 at 11:11 am
In SQL Server 2008 Activity Monitor , under Recent Expensive Queires
there is a PLAN COUNT column. It says 4 plan counts.
I would like to make it low or may be tell it to use the same plan.
Something I am trying although there is no issue of unacceptable loads yet.
February 2, 2012 at 11:23 am
That's the count of plans found in cache. Duplicate plans are usually caused by not parameterising queries, not qualifying object names or changing set options. Since you say it's part of a proc, it's either different set options when calling the proc or not qualifying the table names.
Keep fixed plan prevents SQL from invalidating plans in cache, removing them and creating new ones to replace them. Nothing to do with multiple plans in cache
Two completely different things.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply