Hi Kalen

Thanks for your explanations on my questions. I am wondering which dynamic/catalog view to get information about current memory consumption for plan cache. So I can monitor memory consumed for plan cache and plan cache pressure limit.

Is there any techniques that I can use to raise plan cache pressure limit, let say I want to raise from 16 GB to 32 GB?

Thank you.

Kind regards,

Kasim Wirama

Kevin12/20/2007 4:14:09 PM

Hi Kasim

I have posted the answers on my blog:
http://sqlblog.com/blogs/kalen_delaney/archive/2007/12/15/QA-on-my-plan-cache-sizing-article-in-sql-server-mag
azine.aspx

Thanks for your comments.

Kalen

KALEN12/15/2007 8:07:04 PM

Hi Kalen,

I am regular reader of your article, this article is very interesting. After reading your article, I have 4 questions:

1. Could you give me illustration how to calculate target memory please? Let say there is a 64 bit system (64 bit hardware, windows 2003 64 bit enterprise edition, SQL Server 2005 Enterprise edition 64 bit, memory 30 GB), max server memory (in sp_configure) is between 16 (min value) - 30 GB (max value)).

2. In your article, you give example how to calculate plan-cache pressure limit, for example for SQL Server 2005 RTM and SP1, it calculates for on 28 target memory as follows :
(.75 x 8 GB) + (.5 x 20 GB) = 6 GB. Then you add to 10 GB so pressure limit is 16 GB. Where does 10 GB come from?

3. you mention "plan-cache pressure limit". Does it mean that when , let's say pressure limit of 28 GB is 16 GB, 16 GB is reached then SQL Server couldn't remove plan cache to make it less than 16 GB, is my understanding right?

4. you show how to remove prepared query by creating plan guide with recompile hint, is it possible to remove non-prepared query plan on the following sample :

EXEC sp_create_plan_guide
@name = N'RemovePlan1',
@stmt = N' SELECT * FROM Sales WHERE ContactID < 3',
@type = N'SQL',
@module_or_batch = NULL,
@params = N',
@hints = N'OPTION(RECOMPILE)';

and I issue query twice as below shown
SELECT * FROM Sales WHERE ContactID < 3

When I check in syscacheobjects with query :
SELECT usecounts as uses, sql FROM sys.syscacheobjects
WHERE dbid = db_id('AdventureWorks');

It shows that the non-prepared query plan is reuse with count equals to 2.

Thanks Kalen, your article is incredibly useful.

Regards,

Kasim Wirama

Kevin12/1/2007 6:54:04 PM