Plan Cache

Last Post 24 Feb 2008 06:21 AM by TRACEYSQL. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
TRACEYSQL
New Member
New Member

--
24 Feb 2008 06:21 AM
i was reading
As you learned last month in “Managing Plan-Cache Size,” InstantDoc ID 97373, SQL Server 2005 provides some controls to keep plan cache from getting too large.

So started to look at what it is suggested doing as an example:
And did the following:

1st
USE ADVENTUREWORKS
GO
SELECT * INTO SALES FROM SALES.SALESORDERHEADER
GO
CREATE INDEX CONTACT_INDX ON SALES(CONTACTID)
GO


2nd
USE ADVENTUREWORKS
dbcc freeproccache
go
exeC sp_executesql
N'SELECT * FROM SALES WHERE CONTACTID < @ID', N'@ID int',
@ID = 3
GO


3rd
exeC sp_executesql
N'SELECT * FROM SALES WHERE CONTACTID < @ID', N'@ID int',
@ID = 5
GO


4th
select usecounts as uses, sql from sys.syscacheobjects where
dbid = db_id('adventureworks') and objtype = 'Prepared'

Saw i got two counts in here


Then i ran the following:
5th
use adventureworks
exec sp_create_plan_guide
@name = N'RemovePlan',
@stmt = N'SELECT * FROM Sales WHERE ContactID < @ID',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@ID int',
@hints = N'OPTION(RECOMPILE)';


Then i ran 2 and 3 above then 4
I was expecting there to be no counts in here as statement 5th is supposed to not keep the plan in cache.
But it kept increasing the counts every time i execute a select statement

Perhaps i misunderstood the thread, i thought the option in 5 would not keep the plan in cache.


Any light would be appreciated.
You are not authorized to post a reply.

Acceptable Use Policy