Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sp_configure "statement cache size"

Posted on 2011-02-25
1
Medium Priority
?
1,572 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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Aerodynamic noise is the cause of the majority of the noise produced by helicopters. The inordinate amount of noise helicopters produce is a major problem in the both a military and civilian setting. To remedy this problem the use of an aerogel coat…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

877 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