Bottleneck with statement cache

[ozgur.kara]

Hello everybody,

What I have recently experienced on a system is, keeping statement
cache too high may cause a performance drop; probably because growing
memory to be searched for statements makes the search process a big
deal.

Does anyone have an idea how to optimize statement cache size. I'm
asking for a method other than trial&error, if there is :)

[Leonid Gvirtz]

Post by ozgur.kara
Hello everybody,
What I have recently experienced on a system is, keeping statement
cache too high may cause a performance drop; probably because growing
memory to be searched for statements makes the search process a big
deal.
Does anyone have an idea how to optimize statement cache size. I'm
asking for a method other than trial&error, if there is :)

Hi

Please provide us with some more details. What is your ASE version? What
is the size of the statement cache? Do you use bind variables for your
SQL statements? Does your applications really reuse statements? At what
extent?

I performed some benchmarks on ASE statement cache when it was
introduced a number of years ago and found that if applications don't
use bind variables and don't reuse statements then the statement cache
may lead to decrease in performance, not a dramatic one though.

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com

[Mark A. Parsons]

In addition to Leonid's requests I'd like to add:

- provide details on how/why you've come to the conclusion that you have a performance problem related to a large
statement cache

- if you have some queries taking longer than 'normal' to run, have you verified that they are (re)using a good query
plan? [it is possible that 2 queries ... exactly the same except for literal values ... can require different query
plans in order to run efficiently; in this scenario, with statement cache enabled, it is possible for a cached plan to
be 'bad' for some queries]

- provide output from a sp_sysmon session (10-15 minutes should suffice) during a period of performance degradation;
please post the sp_sysmon output as an *txt attachment to your post (to maintain formatting and make it easier to read)

Post by Leonid Gvirtz

Post by ozgur.kara
Hello everybody,
What I have recently experienced on a system is, keeping statement
cache too high may cause a performance drop; probably because growing
memory to be searched for statements makes the search process a big
deal.
Does anyone have an idea how to optimize statement cache size. I'm
asking for a method other than trial&error, if there is :)

Hi
Please provide us with some more details. What is your ASE version? What
is the size of the statement cache? Do you use bind variables for your
SQL statements? Does your applications really reuse statements? At what
extent?
I performed some benchmarks on ASE statement cache when it was
introduced a number of years ago and found that if applications don't
use bind variables and don't reuse statements then the statement cache
may lead to decrease in performance, not a dramatic one though.
Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com

[John McVicker]

I've heard (and read in the Jeff Tallman presentations) that:

if you use statement cache, it only works well if you enable the
sp_configure 'enable literal automarm', 1

Without that, it apparently isn't a help.

Seems like a good idea for the target of Rep server where you may see
thousands of the same statements due to the way rep server sends over DML
statements.

Statement cache in general seems like a great idea - however, it all depends
on what's going on behind the curtain. If you make a lot of stored
procedure calls rather than client-sent SQL statements - then there really
isn't much need for statement cache. I've also heard that statement cache
is not used for dynamic sql -meaning if in procs you use: exec
(@sql_string).

- John McVicker
Inventa Technologies