Solved

sp_configure "statement cache size"

Posted on 2011-02-25
1
1,468 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 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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