Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sp_configure "statement cache size"

Posted on 2011-02-25
1
Medium Priority
?
1,544 Views
Last Modified: 2012-05-11
Hello All,

Any advice on configuration of 'statement cache size' because as usual Sybase manual is not clear cut on its example...


Many Thanks.
0
Comment
Question by:tranicus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 2000 total points
ID: 34986254
I feel your frustration. I'm yet to discover any robust method of doing this either. :)

What I've done is pick a figure maybe 25% the size of procedure cache, and watch carefully what happens. sp_sysmon will give you a pretty good idea if that's too small or too large - which also means it's difficult to test this prior to production unless you have a way of simulating full production load.

Note if you're on a version of ASE that has this, you must also enable "literal autoparam" as statement cache is almost useless without it.

A corollary to that is if you are not on a version of ASE that has that, it may not help very much to use statement cache.

Test this most carefully in production - if statement cache is too small statements start failing with run-time errors!

Like most fancy cache techniques this is the luxury of having enough memory to play with. If you only have 2Gb of memory for ASE you probably don't have enough to use this sensibly... but try it and see, that's just a rule of thumb, not a natural law.

Lastly note that when statement cache is being used it will reduce procedure cache, so to preserve the status quo it's best if procedure cache is increased by the same amount you configure statement cache for. (See above re. "luxury of having plenty of memory".)
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Considering cloud tradeoffs and determining the right mix for your organization.
Are you an Exchange administrator employed with an organization? And, have you encountered a corrupt Exchange database due to which you are not able to open its EDB file. This article will explain all the steps to repair corrupt Exchange database.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question