Plans in Cache

If plans in cache are frequently being removed, can that indicate memory
pressure?
This is probably a "this depends" type of answer, what is an optimal time for
plans to stay in cache?
Message posted via http://www.droptable.com
Hello,
Plans will be flushed due to below reasons:-
1.
Whenever a schema change occurs for any of the objects referenced by a
batch, the batch is recompiled. "Schema change" is defined by the following:
. Adding or dropping columns to a table or view
. Adding or dropping constraints, defaults, or rules to/from a
table
. Adding an index to a table or an indexed view
. Dropping an index defined on a table or an indexed view (only
if the index is used by the query plan in question)
. (SQL Server 2000). Manually updating or dropping a statistic
(not creating!) on a table will cause a recompilation of any query plans
that use that table. Such recompilations happen the next time the query plan
in question begins execution.
. (SQL Server 2005). Dropping a statistic (not creating or
updating!) defined on a table will cause a correctness-related recompilation
of any query plans that use that table. Such recompilations happen the next
time the query plan in question begins execution. Updating a statistic (both
manual and auto-update) will cause an optimality-related (data related)
recompilation of any query plans that uses this statistic.
2.
Running sp_recompile on a stored procedure or a trigger causes them to
be recompiled the next time they are executed. When sp_recompile is run on a
table or a view, all of the stored procedures that reference that table or
view will be recompiled the next time they are run. sp_recompile
accomplishes recompilations by incrementing the on-disk schema version of
the object in question.
3.
The following operations flush the entire plan cache, and therefore,
cause fresh compilations of batches that are submitted the first time
afterwards:
. Detaching a database
. Upgrading a database to SQL Server 2000 (on SQL Server 2000)
. Upgrading a database to SQL Server 2005 (on SQL Server 2005
server)
. DBCC FREEPROCCACHE command
. RECONFIGURE command
. ALTER DATABASE ... MODIFY FILEGROUP command
. Modifying a collation using ALTER DATABASE ... COLLATE command
The following operations flush the plan cache entries that refer to a
particular database, and cause fresh compilations afterwards.
. DBCC FLUSHPROCINDB command
. ALTER DATABASE ... MODIFY NAME = command
. ALTER DATABASE ... SET ONLINE command
. ALTER DATABASE ... SET OFFLINE command
. ALTER DATABASE ... SET EMERGENCY command
. DROP DATABASE command
. When a database auto-closes
. When a view is created with CHECK OPTION, the plan cache entries of
the database in which the view is created is flushed.
. When DBCC CHECKDB is run, a replica of the specified database is
created. As part of DBCC CHECKDB's execution, some queries against the
replica are executed, and their plans cached. At the end of DBCC CHECKDB's
execution, the replica is deleted and so are the query plans of the queries
posed on the replica.
Read more from the below article:-
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Thanks
Hari
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f304c9d92eea@.uwe...
> If plans in cache are frequently being removed, can that indicate memory
> pressure?
> This is probably a "this depends" type of answer, what is an optimal time
> for
> plans to stay in cache?
> --
> Message posted via http://www.droptable.com
>
|||How do you know they are being removed? Are you sure they were there to
begin with? What exactly are you basing this on?
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f304c9d92eea@.uwe...
> If plans in cache are frequently being removed, can that indicate memory
> pressure?
> This is probably a "this depends" type of answer, what is an optimal time
> for
> plans to stay in cache?
> --
> Message posted via http://www.droptable.com
>
|||What first got me clued in was running the following statement. A particular
procedure dominated all 25 returns and the top plan_generation_num value was
143. I ran it 5 minutes or so later and a completely different procedure was
at the top with a plan_generation_num of 86, and the previous stored
procedure that had a plan_generation_num value of 143 was nowhere in the top
25.
In looking at Profiler, I then saw that the procedure that had a
plan_generation_num of 143 would recompile every time with an eventclasssub
of "4 - Schema Change".
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc
Andrew J. Kelly wrote:[vbcol=seagreen]
>How do you know they are being removed? Are you sure they were there to
>begin with? What exactly are you basing this on?
Message posted via http://www.droptable.com
|||I think it is just the recompilation of the stored procedure tht is happening
because of schema change which is causing ur procedure plans deletion from
plan cache.
Manu
"cbrichards via droptable.com" wrote:

> What first got me clued in was running the following statement. A particular
> procedure dominated all 25 returns and the top plan_generation_num value was
> 143. I ran it 5 minutes or so later and a completely different procedure was
> at the top with a plan_generation_num of 86, and the previous stored
> procedure that had a plan_generation_num value of 143 was nowhere in the top
> 25.
> In looking at Profiler, I then saw that the procedure that had a
> plan_generation_num of 143 would recompile every time with an eventclasssub
> of "4 - Schema Change".
>
> select top 25
> sql_text.text,
> sql_handle,
> plan_generation_num,
> execution_count,
> dbid,
> objectid
> from
> sys.dm_exec_query_stats a
> cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
> where
> plan_generation_num >1
> order by plan_generation_num desc
>
> Andrew J. Kelly wrote:
> --
> Message posted via http://www.droptable.com
>
|||Yet, if I run the same stored procedure in a test environment, it compiles
the first execution, but thereafter no compiles or recompiles occur. Yet in
production, it compiles quite frequently. Which comes back to my original
question...Can memory pressure induce cache removal?
manu wrote:[vbcol=seagreen]
>I think it is just the recompilation of the stored procedure tht is happening
>because of schema change which is causing ur procedure plans deletion from
>plan cache.
>Manu
>[quoted text clipped - 30 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
|||If the reason for the recompile was indeed a schema change inside the sp
then I don't see why it would recompile in one env and not the other. There
is no bought that memory pressure can cause plans to be forced out of cache.
But that would cause compiles and not recompiles. In production lots of
things can happen to force a recompile that may not happen in a dev env. For
instance in production you are changing the data within the table which will
at some point force the stats to be updated. This in turn forces the plan to
be recompiled. If the plan was forced out of cache due to memory pressure
then it would show up as a compile. So make sure which event is really
happening. Just because the plan doesn't show up in the top 25 of that
query does not mean it isn't still in cache. This is an excellent article
that goes into great details of this aspect of the engine.
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f46850557571@.uwe...
> Yet, if I run the same stored procedure in a test environment, it compiles
> the first execution, but thereafter no compiles or recompiles occur. Yet
> in
> production, it compiles quite frequently. Which comes back to my original
> question...Can memory pressure induce cache removal?
> manu wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>
|||Thanks Andrew for verifying and explaining several items.
After reviewing my profiler output...almost all were compiles, not recompiles.
As I originally started this thread, I suspected memory pressure. I largely
feel that is confirmed now and would like to explore that possibility further.
I will consider this thread closed. I have another thread opened that I will
pursue the memory pressure route. That thread is titled "Troubleshooting High
CPU". My situation is explained in detail there, including some suspected
memory metrics obtained from Perfmon. But so far, nobody has addressed that
issue in that particular thread. Thanks again.
Andrew J. Kelly wrote:[vbcol=seagreen]
>If the reason for the recompile was indeed a schema change inside the sp
>then I don't see why it would recompile in one env and not the other. There
>is no bought that memory pressure can cause plans to be forced out of cache.
>But that would cause compiles and not recompiles. In production lots of
>things can happen to force a recompile that may not happen in a dev env. For
>instance in production you are changing the data within the table which will
>at some point force the stats to be updated. This in turn forces the plan to
>be recompiled. If the plan was forced out of cache due to memory pressure
>then it would show up as a compile. So make sure which event is really
>happening. Just because the plan doesn't show up in the top 25 of that
>query does not mean it isn't still in cache. This is an excellent article
>that goes into great details of this aspect of the engine.
>http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>[quoted text clipped - 17 lines]
Message posted via http://www.droptable.com
|||Make sure the proc doesn't have the RECOMPILE hint in it as well.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f4c9dc754d6e@.uwe...
> Thanks Andrew for verifying and explaining several items.
> After reviewing my profiler output...almost all were compiles, not
> recompiles.
> As I originally started this thread, I suspected memory pressure. I
> largely
> feel that is confirmed now and would like to explore that possibility
> further.
>
> I will consider this thread closed. I have another thread opened that I
> will
> pursue the memory pressure route. That thread is titled "Troubleshooting
> High
> CPU". My situation is explained in detail there, including some suspected
> memory metrics obtained from Perfmon. But so far, nobody has addressed
> that
> issue in that particular thread. Thanks again.
> Andrew J. Kelly wrote:
> --
> Message posted via http://www.droptable.com
>
|||None of our procs, across the board, contain a RECOMPILE hint.
Andrew J. Kelly wrote:[vbcol=seagreen]
>Make sure the proc doesn't have the RECOMPILE hint in it as well.
>[quoted text clipped - 40 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1