Opinions on Option (Keepfixed Plan)

I've got this situation where a certain large stored proc (that's used
all day long) just compiles way too often, to the point where it hurts
the performance. I've tried all the tricks I could find to reduce the
number of compilations. So my last option is to use Option (Keepfixed
Plan) hint.
Are there any downsides to using Option (Keepfixed Plan)?
RegardsHi Frank
Have you read these article to determine the cause of the recompilations and
address the actual reasons?
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:uuunQQy5GHA.4064@.TK2MSFTNGP03.phx.gbl...
> I've got this situation where a certain large stored proc (that's used all
> day long) just compiles way too often, to the point where it hurts the
> performance. I've tried all the tricks I could find to reduce the number
> of compilations. So my last option is to use Option (Keepfixed Plan)
> hint.
> Are there any downsides to using Option (Keepfixed Plan)?
> Regards|||Kalen Delaney wrote:
> Hi Frank
> Have you read these article to determine the cause of the recompilations a
nd
> address the actual reasons?
> Troubleshooting stored procedure recompilation
> http://support.microsoft.com/kb/243586
> How to identify the cause of recompilation in an SP:Recompile event
> http://support.microsoft.com/kb/308737
Yep, I did all these. I decreased SP:Recompile events in Profiler
substantially, however, they were still happening quite a bit.
Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
(anecdotally) increased the performance of the stored proc. In Perfmon,
however, I still get a large number for the SQL Statistic/SQL
Compilations. It must mean something else than what is reflected by the
SP:Recompile event in Profiler.|||Frank Rizzo wrote:
> Kalen Delaney wrote:
> Yep, I did all these. I decreased SP:Recompile events in Profiler
> substantially, however, they were still happening quite a bit.
> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
> (anecdotally) increased the performance of the stored proc. In Perfmon,
> however, I still get a large number for the SQL Statistic/SQL
> Compilations. It must mean something else than what is reflected by the
> SP:Recompile event in Profiler.
If the recompilations are caused by a large amount of
inserts/updates/deletes, then you could experiment with turning off
auto-update statistics and run a schedule to manually update the
statistics at a time that is convenient for you.
Gert-Jan|||Gert-Jan Strik wrote:
> Frank Rizzo wrote:
> If the recompilations are caused by a large amount of
> inserts/updates/deletes, then you could experiment with turning off
> auto-update statistics and run a schedule to manually update the
> statistics at a time that is convenient for you.
I turned off the auto-update stats from the get go, but the problem is
still happening.

> Gert-Jan