Solved

sp_configure "statement cache size"

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
We need a new way to communicate time sensitive or critical info.   The best part of my role at xMatters is visiting our clients all over the world to learn about how they operate their businesses, share insights that xMatters has gleaned across…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now