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.sqlmonster.comHello,
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 SQLMonster.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.sqlmonster.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 SQLMonster.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.sqlmonster.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:
>How do you know they are being removed? Are you sure they were there to
>begin with? What exactly are you basing this on?
>> 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.sqlmonster.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 SQLMonster.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:
> >How do you know they are being removed? Are you sure they were there to
> >begin with? What exactly are you basing this on?
> >
> >> 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.sqlmonster.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:
>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
>> 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
>[quoted text clipped - 30 lines]
>> >> for
>> >> plans to stay in cache?
--
Message posted via SQLMonster.com
http://www.sqlmonster.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 SQLMonster.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:
>>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
>> 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
>>[quoted text clipped - 30 lines]
>> >> for
>> >> plans to stay in cache?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.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:
>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
>> 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
>[quoted text clipped - 17 lines]
>> >> for
>> >> plans to stay in cache?
--
Message posted via http://www.sqlmonster.com|||Make sure the proc doesn't have the RECOMPILE hint in it as well.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.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:
>>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
>> 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
>>[quoted text clipped - 17 lines]
>> >> for
>> >> plans to stay in cache?
> --
> Message posted via http://www.sqlmonster.com
>|||None of our procs, across the board, contain a RECOMPILE hint.
Andrew J. Kelly wrote:
>Make sure the proc doesn't have the RECOMPILE hint in it as well.
>> Thanks Andrew for verifying and explaining several items.
>[quoted text clipped - 40 lines]
>> >> for
>> >> plans to stay in cache?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1

Planning for SQL Server 2005: 64-bit, OS, processor, memory

I was browsing Microsoft's SQL Server site, looking for
some details about SQL Server 2005. Didn't find what
I was looking for...

I'm thinking about moving an existing SQL Server 2000
workload to a new box, using SQL Server 2005, and
maybe the 64-bit version.

My questions are:

1. What is the current target date for release of SQL Server 2005?
Will 64-bit ship when 32-bit ships?

2. Will 64-bit SQL Server 2005 require a special version
of Windows Server 2003 (e.g. Windows Server 2003 Enterprise x64)?
Will it work with both Intel and AMD processors?

3. How many CPUs, and how much memory, will be supported by
SQL Server 2005, 32-bit and 64-bit, on each OS that can run
SQL Server 2005.

I'm looking for a chart here, something like the chart on
page 117 of Kalen Delaney's "Inside SQL Server 2000" book.

SQL Server 2005 SQL Server 2005
Feature Enterprise 32-bit Enterprise 64-bit
------ ------ ------
CPUs supported
Win Srvr 2003:
Win Srvr 2003 Adv:
Win Srvr 2003 Ent x64:

Physical memory
supported
Win Srvr 2003:
Win Srvr 2003 Adv:
Win Srvr 2003 Ent x64:

Has Microsoft published this info, and I just can find it?Larry Bertolini (bertolini.1@.osu.edu) writes:
> I was browsing Microsoft's SQL Server site, looking for
> some details about SQL Server 2005. Didn't find what
> I was looking for...
> I'm thinking about moving an existing SQL Server 2000
> workload to a new box, using SQL Server 2005, and
> maybe the 64-bit version.
> My questions are:
> 1. What is the current target date for release of SQL Server 2005?

The only answer I have heard is "when we're ready". Maybe someone
has said things like "second half of 2005". But I think that is about
as precise information you can get.

> Will 64-bit ship when 32-bit ships?

Since every drop I've seen of SQL2005 has included 64-bit, I see no
reason to assume that 64-bit would not ship when 32-bit ships.

> 2. Will 64-bit SQL Server 2005 require a special version
> of Windows Server 2003 (e.g. Windows Server 2003 Enterprise x64)?

I don't think the whole thing about editions have been sorted out
yet.

> Will it work with both Intel and AMD processors?

Provided that Win2003 SP1 has shipped by then, I would assume so.

> 3. How many CPUs, and how much memory, will be supported by
> SQL Server 2005, 32-bit and 64-bit, on each OS that can run
> SQL Server 2005.

Again, I don't think anything that has been finalized yet. These
limits may not necessarily be hard limits, but rather what sort
of configuration that actually has been tested.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

placing table on the memory

Hello there
I have reference localAreaCode table with 3000 records , i need to check on
existing phone table that the area code exist on LocalAreaCode table.
So far the query worked very slow.
Is there a way to place the LocalAreaCode table at the memory, to inprove
perfomance?Hi Roy,
In SQL Server 2000 you can use:
1) DBCC PINTABLE
2) sp_tableoption
See Books Online for more info about this.
I'm pretty sure that DBCC PINTABLE does nothing in SQL Server 2005 and
for sp_tableoption the choice to pin a table in memory is no longer
there. The rationale is that using these options can cause bad things
to happen. For example, in SQL Server 2005 Books Online we have the
following in the DBCC PINTABLE entry:
==============================
This functionality was introduced for performance in SQL Server version
6.5. DBCC PINTABLE has highly unwanted side-effects. These include the
potential to damage the buffer pool. DBCC PINTABLE is not required and
has been removed to prevent additional problems. The syntax for this
command still works but does not affect the server.
==============================
Sorry I can't be of more help|||Maybe we should first take look at the query. Please post it.
Also check the execution plan to see whether indexes are used properly. The
table is indexed, isn't it?
ML
http://milambda.blogspot.com/|||You could do this in 2000, using sp_tableoption. This was misused, though so
I believe that removed
it in 2005. If the table is accessed frequently enough, it will be cache in
memory, especially with
only 3000 rows. Still, you want to make sure that queries against it are eff
icient (create
supporting indexes etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Goldhammer" <roy@.hotmail.com> wrote in message news:uetz2fwfGHA.4276@.TK2MSFTNGP03.phx.
gbl...
> Hello there
> I have reference localAreaCode table with 3000 records , i need to check o
n existing phone table
> that the area code exist on LocalAreaCode table.
> So far the query worked very slow.
> Is there a way to place the LocalAreaCode table at the memory, to inprove
perfomance?
>|||With only 3000 rows this should never be slow, so I suspect problems
with the table design or the query. You need to post the table
design, all keys and indexes, and the query that is slow.
Roy Harvey
Beacon Falls, CT
On Wed, 24 May 2006 10:58:04 +0300, "Roy Goldhammer" <roy@.hotmail.com>
wrote:

>Hello there
>I have reference localAreaCode table with 3000 records , i need to check on
>existing phone table that the area code exist on LocalAreaCode table.
>So far the query worked very slow.
>Is there a way to place the LocalAreaCode table at the memory, to inprove
>perfomance?
>