Procedure Cache and Statement Cache (reported in sysmon)

Discussion:

(too old to reply)

Sybase Replication Server

2008-08-19 15:39:12 UTC

All -

In reviewing my sysmons for a reporting dataserver, I found that my
'Statements not cached' were non-zero with my 'statement cache size'
configured at '0'. So, for testing, I increased that configuration to
'100' MB to see what effects this would have in sysmon.

Before starting my tests, I also looked at 'Procedure Removals' which
have been consistently ~0 for months. Immediately upon testing and
my subsequent sysmons, my 'Statements not Cached' drop to 0, but my
'Procedure Removal' jumped anywhere from 12-49 for each 5 minute
sysmon interval. I then did some testing to try and get my 'Procedure
Removal' back down to ~0 by (1) increasing my Procedure Cache size by
100 Mb to offset my 'Statement Cache' configuration, (2) dropping my
'Statement Cache Size' to 50 Mb, 10Mb and 1 Mb (all the while still
seeing 0 for my Statements not Cached') but yet my 'Procedure Removal'
is never dropping to/near pre-test levels, and actually fluctuating
quite a bit for my sysmon intervals.

My question is if (1) this is expected and that the decrease in
'Statement Cache' size while dynamic, really won't happen until reboot
and (2) if by turning on 'Statement Cache' I've turned a corner that I
can change until I turn off 'Statement Cache' and reboot to give back
all memory to the Procedure Cache. I may have over configured the
'Statement Cache' which is why I'm testing and running sysmon's to
find a good range. I'm going to be monitoring this over the next
few days to see if anything changes maybe with statements in cache
need to age out on their own and I'll slowly start seeing my
'Procedure Removal' drop as statements age out, although I've
increased my 'Procedure Cache' size by 1 Gb over and above what was
originally configured, which suprises me that I didn't immedately jump
back to 0 for removals.

Any thoughts/experience seen on this?

Link

2008-08-19 16:32:41 UTC

Permalink

Post by Sybase Replication Server
All -
In reviewing my sysmons for a reporting dataserver, I found that my
'Statements not cached' were non-zero with my 'statement cache size'
configured at '0'. So, for testing, I increased that configuration to
'100' MB to see what effects this would have in sysmon.
Before starting my tests, I also looked at 'Procedure Removals' which
have been consistently ~0 for months. Immediately upon testing and
my subsequent sysmons, my 'Statements not Cached' drop to 0, but my
'Procedure Removal' jumped anywhere from 12-49 for each 5 minute
sysmon interval. I then did some testing to try and get my 'Procedure
Removal' back down to ~0 by (1) increasing my Procedure Cache size by
100 Mb to offset my 'Statement Cache' configuration, (2) dropping my
'Statement Cache Size' to 50 Mb, 10Mb and 1 Mb (all the while still
seeing 0 for my Statements not Cached') but yet my 'Procedure Removal'
is never dropping to/near pre-test levels, and actually fluctuating
quite a bit for my sysmon intervals.
My question is if (1) this is expected and that the decrease in
'Statement Cache' size while dynamic, really won't happen until reboot
and (2) if by turning on 'Statement Cache' I've turned a corner that I
can change until I turn off 'Statement Cache' and reboot to give back
all memory to the Procedure Cache. I may have over configured the
'Statement Cache' which is why I'm testing and running sysmon's to
find a good range. I'm going to be monitoring this over the next
few days to see if anything changes maybe with statements in cache
need to age out on their own and I'll slowly start seeing my
'Procedure Removal' drop as statements age out, although I've
increased my 'Procedure Cache' size by 1 Gb over and above what was
originally configured, which suprises me that I didn't immedately jump
back to 0 for removals.
Any thoughts/experience seen on this?

UPDATE: version 12.5.3/EBF 13057 ESD#5

I've reconfigured the 'Statement Cache' down to 0, which was reflected
in the sysmon, 'statements not in cache' went to non-zero, at which
point I re-configured my 'statement cache' to 10Mb and was able to get
it back to non-zero. But, my overall procedure cache size is much
larger than it was originally and I'm not seeing that come down to ~0
which is what it originally what is was for months leadiing up to my
testing. odd.

Mark K

2008-08-19 19:11:10 UTC

Permalink

Statement Cache adds pages to the Procedure Cache, it does not take away
pages. Does your reporting server have stored procedures being executed, or
is everything just plain SQL?

Mark Kusma

Link

2008-08-19 19:37:22 UTC

Permalink

Post by Mark K
Statement Cache adds pages to the Procedure Cache, it does not take away
pages. Does your reporting server have stored procedures being executed, or
is everything just plain SQL?
Mark Kusma

UPDATE: version 12.5.3/EBF 13057 ESD#5
I've reconfigured the 'Statement Cache' down to 0, which was reflected
in the sysmon, 'statements not in cache' went to non-zero, at which
point I re-configured my 'statement cache' to 10Mb and was able to get
it back to non-zero. But, my overall procedure cache size is much
larger than it was originally and I'm not seeing that come down to ~0
which is what it originally what is was for months leadiing up to my
testing. odd.

It's a combination of both, procedures and statements (this is
replicated environment). My belief is that these 'statements' are the
inserts/updates/deletes from the RS since we're not replicating
procs.

From my readings, I would not expect to see the 'procedure cache
behavior' because an adjustment was made to the 'procedure cache size'
to offset what I created for the 'statement cache'. The only way I
get my 'procedure removal' to drop to near 0 again is by turning of
'statement cache' completely.

Mark K

2008-08-19 21:08:28 UTC

Permalink

The statements become lightweight procedures (LWPs). Perhaps the LWPs are
being removed and getting counted under Procedure Removals. I haven't tested
that theory, but just thought I'd suggest it for now.

Mark Kusma

UPDATE: version 12.5.3/EBF 13057 ESD#5
I've reconfigured the 'Statement Cache' down to 0, which was reflected
in the sysmon, 'statements not in cache' went to non-zero, at which
point I re-configured my 'statement cache' to 10Mb and was able to get
it back to non-zero. But, my overall procedure cache size is much
larger than it was originally and I'm not seeing that come down to ~0
which is what it originally what is was for months leadiing up to my
testing. odd.

Link

2008-08-20 00:09:48 UTC

Permalink

Right, I did happen to read that but with increasing the size of the
'procedure cache' => the size I had configured for the 'statement cache', I
wasn't expecting to see the spike in 'procedure removals' from the sysmon
report unless it's very significant. I'm trying to use some of the other
counters to see if I need to increase the size of the procedure cache
altogether when I add a statement cache. Maybe it's the replication
statements/volume getting turned into LWP's that is pushing the 'procedure
cache' beyonds it's typical limits with 'statement cache' enabled. If
that's the case, at this point, I have to wonder if turning off the
'statement cache' is the better option. Maybe this isn't meant for a
heavily replicated, reporting env.

Post by Mark K
The statements become lightweight procedures (LWPs). Perhaps the LWPs are
being removed and getting counted under Procedure Removals. I haven't
tested that theory, but just thought I'd suggest it for now.
Mark Kusma

UPDATE: version 12.5.3/EBF 13057 ESD#5
I've reconfigured the 'Statement Cache' down to 0, which was reflected
in the sysmon, 'statements not in cache' went to non-zero, at which
point I re-configured my 'statement cache' to 10Mb and was able to get
it back to non-zero. But, my overall procedure cache size is much
larger than it was originally and I'm not seeing that come down to ~0
which is what it originally what is was for months leadiing up to my
testing. odd.

It's a combination of both, procedures and statements (this is
replicated environment). My belief is that these 'statements' are the
inserts/updates/deletes from the RS since we're not replicating
procs.
From my readings, I would not expect to see the 'procedure cache
behavior' because an adjustment was made to the 'procedure cache size'
to offset what I created for the 'statement cache'. The only way I
get my 'procedure removal' to drop to near 0 again is by turning of
'statement cache' completely.

Link

2008-08-20 00:33:04 UTC

Permalink

One additional note, based on the comment -

"Perhaps the LWPs are being removed and getting counted under
Procedure Removals"

to me appears to be accurate. In reviewing earlier sysmon's I found
the following nearly match in all -

Procedure Removals
Statements Cached
Statements Dropped

To me it appears that maybe the 'statement cache' is too small or
statements that aren't reused and getting flushed quickly due to the
small size.

Thoughts?

Post by Link
Right, I did happen to read that but with increasing the size of the
'procedure cache' => the size I had configured for the 'statement cache', I
wasn't expecting to see the spike in 'procedure removals' from the sysmon
report unless it's very significant. I'm trying to use some of the other
counters to see if I need to increase the size of the procedure cache
altogether when I add a statement cache. Maybe it's the replication
statements/volume getting turned into LWP's that is pushing the 'procedure
cache' beyonds it's typical limits with 'statement cache' enabled. If
that's the case, at this point, I have to wonder if turning off the
'statement cache' is the better option. Maybe this isn't meant for a
heavily replicated, reporting env.

UPDATE: version 12.5.3/EBF 13057 ESD#5
I've reconfigured the 'Statement Cache' down to 0, which was reflected
in the sysmon, 'statements not in cache' went to non-zero, at which
point I re-configured my 'statement cache' to 10Mb and was able to get
it back to non-zero. But, my overall procedure cache size is much
larger than it was originally and I'm not seeing that come down to ~0
which is what it originally what is was for months leadiing up to my
testing. odd.

It's a combination of both, procedures and statements (this is
replicated environment). My belief is that these 'statements' are the
inserts/updates/deletes from the RS since we're not replicating
procs.
From my readings, I would not expect to see the 'procedure cache
behavior' because an adjustment was made to the 'procedure cache size'
to offset what I created for the 'statement cache'. The only way I
get my 'procedure removal' to drop to near 0 again is by turning of
'statement cache' completely.

Mark A. Parsons

2008-08-20 01:19:14 UTC

Permalink

Yeah, sounds like statement cache entries are not being (re)used.

For each new query:

- query has different literals than previous queries so when hashed no matches found in statement cache

- query is optimized (as a LWP) and placed in statement cache (and procedure cache)

- oldest query (and LWP) is pushed from statement cache (and procedure cache)

Net result is thrashing of statement and procedure cache, as well as a bump up in cpu utilization.

Post by Link
One additional note, based on the comment -
"Perhaps the LWPs are being removed and getting counted under
Procedure Removals"
to me appears to be accurate. In reviewing earlier sysmon's I found
the following nearly match in all -
Procedure Removals
Statements Cached
Statements Dropped
To me it appears that maybe the 'statement cache' is too small or
statements that aren't reused and getting flushed quickly due to the
small size.
Thoughts?

UPDATE: version 12.5.3/EBF 13057 ESD#5
I've reconfigured the 'Statement Cache' down to 0, which was reflected
in the sysmon, 'statements not in cache' went to non-zero, at which
point I re-configured my 'statement cache' to 10Mb and was able to get
it back to non-zero. But, my overall procedure cache size is much
larger than it was originally and I'm not seeing that come down to ~0
which is what it originally what is was for months leadiing up to my
testing. odd.

It's a combination of both, procedures and statements (this is
replicated environment). My belief is that these 'statements' are the
inserts/updates/deletes from the RS since we're not replicating
procs.
From my readings, I would not expect to see the 'procedure cache
behavior' because an adjustment was made to the 'procedure cache size'
to offset what I created for the 'statement cache'. The only way I
get my 'procedure removal' to drop to near 0 again is by turning of
'statement cache' completely.

Mark A. Parsons

2008-08-20 01:47:17 UTC

Permalink

While I don't have access to an ASE 12.5.x dataserver at the moment, here's what happens in an ASE 15.0.2 dataserver
with a large volume of SQL queries (and light/moderate procedure usage) when statement cache (and literal autoparam) are
enabled and disabled ...

- statement cache size = 0
- enable literal autoparam = 0

Notice the low counts for Procedure Requests and Removals and high counts for Statements Not Cached. This is just
telling us that statement cache is *not* in use at this time.

This should be comparable to what you'd see in ASE 12.5.x with statement cache size = 0.

Procedure Cache Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Procedure Requests 1.1 0.0 649 n/a
Procedure Reads from Disk 0.0 0.0 2 0.3 %
Procedure Writes to Disk 0.0 0.0 0 0.0 %
Procedure Removals 0.0 0.0 4 n/a
Procedure Recompilations 0.0 0.0 0 n/a

SQL Statement Cache:
Statements Cached 0.0 0.0 0 n/a
Statements Found in Cache 0.0 0.0 0 n/a
Statements Not Found 0.0 0.0 0 n/a
Statements Dropped 0.0 0.0 0 n/a
Statements Restored 0.0 0.0 0 n/a
Statements Not Cached 117.7 1.3 70604 n/a

- statement cache size = 25000
- enable literal autoparam = 0

Notice the high number of Statements Cached *AND* Not Found *AND* Dropped but with relatively few Statements Found in
Cache, ie, we're thrashing statement cache.

NOTE: This particular dataserver *does* see a moderate number of queries with the same *EXACT* literals, ie, the same
*EXACT* queries are executed quite often, hence the reason we see a moderate number for Statements Found in Cache.

Also notice the high volume of Procedure Requests (ie, LWPs) and Removals, ie, procedure cache thrashing.

This should be comparable to what you'd see in ASE 12.5.x if statement cache size > 0 and most/all queries are unique,
ie, different literal values.

In this situation there is basically no benefit from using statement cache in ASE 12.5.x; in fact you're actually
degrading dataserver performance by wasting cpu cycles to manage (aka thrash) statement and procedure cache.

Procedure Cache Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Procedure Requests 75.6 1.1 45384 n/a
Procedure Reads from Disk 0.1 0.0 47 0.1 %
Procedure Writes to Disk 0.0 0.0 0 0.0 %
Procedure Removals 63.4 0.9 38063 n/a
Procedure Recompilations 0.0 0.0 0 n/a

SQL Statement Cache:
Statements Cached 63.3 0.9 37951 n/a
Statements Found in Cache 11.2 0.2 6698 n/a
Statements Not Found 63.3 0.9 37951 n/a
Statements Dropped 63.2 0.9 37923 n/a
Statements Restored 0.1 0.0 33 n/a
Statements Not Cached 0.0 0.0 0 n/a

- statement cache size = 25000
- enable literal autoparam = 1

Notice the high (re)use of statement cache entries (Statements Found in Cache > 0) and corresponding jump in number of
Procedure Requests (ie, 97.2 of those procedure requests are actually the LWPs that go hand-in-hand with the 97.2
statement cache (re)uses).

The low counts for Statements Dropped and Procedure Removals means that we've got our statement and procedure caches
sized just about right and that statement cache entries are being (re)used. In other words little thrashing of either
statement or procedure cache.

This should be comparable to what you'd see in ASE 12.5.x if *ALL* statements are *EXACTLY* the same, ie, literal values
are exactly the same.

NOTE: 'Statements Cached' is quite low because statement cache was already 'populated' at this point.

Procedure Cache Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Procedure Requests 98.3 1.3 58954 n/a
Procedure Reads from Disk 0.0 0.0 2 0.0 %
Procedure Writes to Disk 0.0 0.0 0 0.0 %
Procedure Removals 0.1 0.0 89 n/a
Procedure Recompilations 0.0 0.0 0 n/a

SQL Statement Cache:
Statements Cached 0.0 0.0 23 n/a
Statements Found in Cache 97.2 1.3 58321 n/a
Statements Not Found 0.1 0.0 62 n/a
Statements Dropped 0.0 0.0 11 n/a
Statements Restored 0.1 0.0 30 n/a
Statements Not Cached 0.0 0.0 0 n/a

Obivously (?) the size of your statement cache will depend on your environment.

Post by Mark A. Parsons
Yeah, sounds like statement cache entries are not being (re)used.
- query has different literals than previous queries so when hashed no
matches found in statement cache
- query is optimized (as a LWP) and placed in statement cache (and procedure cache)
- oldest query (and LWP) is pushed from statement cache (and procedure cache)
Net result is thrashing of statement and procedure cache, as well as a
bump up in cpu utilization.

Post by Mark K
Statement Cache adds pages to the Procedure Cache, it does not take away
pages. Does your reporting server have stored procedures being
executed,
or
is everything just plain SQL?
Mark Kusma

UPDATE: version 12.5.3/EBF 13057 ESD#5
I've reconfigured the 'Statement Cache' down to 0, which was reflected
in the sysmon, 'statements not in cache' went to non-zero, at which
point I re-configured my 'statement cache' to 10Mb and was able to get
it back to non-zero. But, my overall procedure cache size is much
larger than it was originally and I'm not seeing that come down to ~0
which is what it originally what is was for months leadiing up to my
testing. odd.

It's a combination of both, procedures and statements (this is
replicated environment). My belief is that these 'statements' are the
inserts/updates/deletes from the RS since we're not replicating
procs.
From my readings, I would not expect to see the 'procedure cache
behavior' because an adjustment was made to the 'procedure cache size'
to offset what I created for the 'statement cache'. The only way I
get my 'procedure removal' to drop to near 0 again is by turning of
'statement cache' completely.

Jeff Tallman

2008-08-20 02:14:53 UTC

Permalink

Actually, what you are trying to do is self defeating....do NOT attempt
to use statement cache with RS in ASE 12.5.x - ASE's 12.5.x statement
cache is only effective for repeated selects or updates with same exact
literals.... RS never does that (okay - rarely) - as each insert,
update and delete as replicated typically have distinct literals.

ASE 15.0.x with literal parameterization works with RS - but then using
RS's Dynamic SQL option does almost same thing - while both are not
mutually exclusive - since they do the same thing, turning both on
likely has marginal benefit. The net:

ASE 12.5.x + RS 12.6 -> suffer the penalties of not upgrading
ASE 12.5.x + RS 15.0.1 or 15.1 -> use RS dynamic SQL
ASE 15.0.x + RS 12.6 -> use ASE statement cache + literal autoparam
ASE 15.0.x + RS 15.0.1 or 15.1 -> use ASE statement cache + literal
autoparam

The last may be a bit of a surprise, but by using TDSLANG instead of
TDSRPC (dynamic sql), RS can send SQL batches more effectively, allows
fstrings and eliminates the RS overhead of managing the dynamic SQL
cache MRU chain.

UPDATE: version 12.5.3/EBF 13057 ESD#5
I've reconfigured the 'Statement Cache' down to 0, which was reflected
in the sysmon, 'statements not in cache' went to non-zero, at which
point I re-configured my 'statement cache' to 10Mb and was able to get
it back to non-zero. But, my overall procedure cache size is much
larger than it was originally and I'm not seeing that come down to ~0
which is what it originally what is was for months leadiing up to my
testing. odd.

It's a combination of both, procedures and statements (this is
replicated environment). My belief is that these 'statements' are the
inserts/updates/deletes from the RS since we're not replicating
procs.
From my readings, I would not expect to see the 'procedure cache
behavior' because an adjustment was made to the 'procedure cache size'
to offset what I created for the 'statement cache'. The only way I
get my 'procedure removal' to drop to near 0 again is by turning of
'statement cache' completely.

Mark A. Parsons

2008-08-20 01:09:54 UTC

Permalink

A related question/issue re: statement cache usage ...

Could you post a copy of the Statement Cache area from a sp_sysmon session during a period when you had statement cache
enabled?

I'm curious to see if those cached statements are actually being used (ie, is Statements Found in Cache > 0? is
Statements Dropped/Not Found > 0?).

----------------

With ASE 12.5.x I found that the statement cache worked great when I had a large volume of the same *EXACT* query coming
into the dataserver ...

select * from sysobjects where id = 8
select * from sysobjects where id = 8
select * from sysobjects where id = 8

... but statement cache was a waste of resources (memory, hashing, recycling statement cache entries) when the queries
were similar but not *EXACT* ...

select * from sysobjects where id = 7
select * from sysobjects where id = 8
select * from sysobjects where id = 9

When the second set of queries were hashed, 3 different hash keys would be generated and thus require 3 different
queries to be optimized and placed into the statement cache. None of the query plans were re-used from statement cache.

At a couple clients the vast majority of the queries (primary and replicate databases) were more like the second set of
queries, so we received no benefit from having statement cache enabled.

In fact we actually saw some degradation in performance due to all of the churning in the statement cache (think one big
FIFO/queue with *all* queries being stuffed in the front and then ignored until they were pushed out the back side).
Performance degradation took the form of increased cpu utilization (think: hash a query, search for match in statement
cache but not find anything, *optimize query*, push on statement cache, remove oldest query from statement cache, etc).
Notice that *all* queries were having to be compiled anyway (ie, no benefit from (re)using pre-compiled plans from
statement cache).

So while your queries will certainly be compiled and placed in the statement cache I'd be more intested in finding out
if those entries (in statement cache) are actually being (re)used. If not, you're wasting memory and cpu cycles.

----------------

And for completeness sake ...

With ASE 15.0.1 a new configuration parameter was provided: 'enable literal autoparam'. (see ASE 15.0.1 What's New manual)

This parameter, when used in conjunction with 'statement cache size' > 0, allows the dataserver to view similar queries
(eg, second batch of queries - above) as being the same.

In a nutshell the parser replaces all static values with temporary variables (eg, @@@V0_INT, @@@V1_VARCHAR,
@@@V2_DATETIME). The second set of queries would thus be converted into:

select * from sysobjects where id = @@@V0_INT
select * from sysobjects where id = @@@VO_INT
select * from sysobjects where id = @@@V0_INT

At this point the queries are then hashed, and since the queries are now *EXACTLY* alike we find matches in statement
cache; this in turn means we can benefit from (re)using pre-compiled query plans from statement cache.

At a couple of my clients the use of 'enable literal autoparam' and 'statement cache size' has come in real handy in
replicate databases. With ASE 15.x the optimizer has to do a lot more work (ie, query compilation takes longer), so DSI
connections tend to take longer to process queries. (In one case replication fell behind during the first night's batch
processing and never caught up due to the slow(er) optimizer in the replicate database.) Once we configured 'enable
literal autoparam' and 'statement cache size' we found that replication returned to pre-ASE 15 levels of performance
(due to the heavy re-use statement cache query plans in the replicate database).

NOTE: RepServer 15.0.1 has a new feature (Dynamic SQL, aka Prepared Statements) which should provide a similiar benefit
to using 'enable literal autoparam' and 'statement cache size' in a replicate ASE 15.x dataserver. This feature
actually offloads some of the processing (eg, statement cache management) from the RDS, though you'll also see an
increase in procedure cache usage (prepared statements == dynamic stored procs == lightweight stored procs) in the RDS.

And yeah, there are some tradeoffs to using statement cache, eg, they suffer from the same problems as a stored proc
that can be compiled in one of several different ways depending on the value of input parameters.

While the following 2 queries may hash to the same key they may actually require a different query plan:

select * from sysobjects where id between 1 and 1 (use index on id ?)
select * from sysobjects where id between -2147000000 and 2147000000 (perform table scan ?)

The first query will be compiled and placed in statement cache with a query plan that is best for it. The second query
will be hashed and match the first query's plan from statement cache; this could lead to the second query performing
poorly (due to using the first query's plan) when it would probably perform more efficiently with a different query plan.

Similar issues arise if you switch the order of the 2 queries.

--------------------

Anyhoo, 'nuff of the tangents and rambling for now ...

UPDATE: version 12.5.3/EBF 13057 ESD#5
I've reconfigured the 'Statement Cache' down to 0, which was reflected
in the sysmon, 'statements not in cache' went to non-zero, at which
point I re-configured my 'statement cache' to 10Mb and was able to get
it back to non-zero. But, my overall procedure cache size is much
larger than it was originally and I'm not seeing that come down to ~0
which is what it originally what is was for months leadiing up to my
testing. odd.

It's a combination of both, procedures and statements (this is
replicated environment). My belief is that these 'statements' are the
inserts/updates/deletes from the RS since we're not replicating
procs.
From my readings, I would not expect to see the 'procedure cache
behavior' because an adjustment was made to the 'procedure cache size'
to offset what I created for the 'statement cache'. The only way I
get my 'procedure removal' to drop to near 0 again is by turning of
'statement cache' completely.

Link

2008-08-20 13:17:59 UTC

Permalink

Here's a snapshot of 10:15 yesterday -

Procedure Cache Management per sec per xact count %
of total
--------------------------- ------------ ------------ ----------
----------
Procedure Requests 55.2 20.2
16550 n/a
Procedure Reads from Disk 0.0 0.0
0 0.0 %
Procedure Writes to Disk 0.0 0.0
0 0.0 %
Procedure Removals 40.1 14.7
12033 n/a
Procedure Recompilations 0.0 0.0
0 n/a

SQL Statement Cache:
Statements Cached 40.1 14.7
12029 n/a
Statements Found in Cache 12.5 4.6
3752 n/a
Statements Not Found 40.1 14.7
12029 n/a
Statements Dropped 80.0 29.3
24010 n/a
Statements Restored 0.0 0.0
0 n/a
Statements Not Cached 0.0 0.0
0 n/a

I agree that I'm not seeing any benefit from using the statement cache
because the statements are different/thrashing so I've turned off the
'statement cache' and I'll review it again later (upgrade).

On Aug 19, 7:09 pm, "Mark A. Parsons"

Post by Mark A. Parsons
A related question/issue re: statement cache usage ...
Could you post a copy of the Statement Cache area from a sp_sysmon session during a period when you had statement cache
enabled?
I'm curious to see if those cached statements are actually being used (ie, is Statements Found in Cache > 0? is
Statements Dropped/Not Found > 0?).
----------------
With ASE 12.5.x I found that the statement cache worked great when I had a large volume of the same *EXACT* query coming
into the dataserver ...
select * from sysobjects where id = 8
select * from sysobjects where id = 8
select * from sysobjects where id = 8
... but statement cache was a waste of resources (memory, hashing, recycling statement cache entries) when the queries
were similar but not *EXACT* ...
select * from sysobjects where id = 7
select * from sysobjects where id = 8
select * from sysobjects where id = 9
When the second set of queries were hashed, 3 different hash keys would be generated and thus require 3 different
queries to be optimized and placed into the statement cache. None of the query plans were re-used from statement cache.
At a couple clients the vast majority of the queries (primary and replicate databases) were more like the second set of
queries, so we received no benefit from having statement cache enabled.
In fact we actually saw some degradation in performance due to all of the churning in the statement cache (think one big
FIFO/queue with *all* queries being stuffed in the front and then ignored until they were pushed out the back side).
Performance degradation took the form of increased cpu utilization (think: hash a query, search for match in statement
cache but not find anything, *optimize query*, push on statement cache, remove oldest query from statement cache, etc).
Notice that *all* queries were having to be compiled anyway (ie, no benefit from (re)using pre-compiled plans from
statement cache).
So while your queries will certainly be compiled and placed in the statement cache I'd be more intested in finding out
if those entries (in statement cache) are actually being (re)used. If not, you're wasting memory and cpu cycles.
----------------
And for completeness sake ...
With ASE 15.0.1 a new configuration parameter was provided: 'enable literal autoparam'. (see ASE 15.0.1 What's New manual)
This parameter, when used in conjunction with 'statement cache size' > 0, allows the dataserver to view similar queries
(eg, second batch of queries - above) as being the same.
At this point the queries are then hashed, and since the queries are now *EXACTLY* alike we find matches in statement
cache; this in turn means we can benefit from (re)using pre-compiled query plans from statement cache.
At a couple of my clients the use of 'enable literal autoparam' and 'statement cache size' has come in real handy in
replicate databases. With ASE 15.x the optimizer has to do a lot more work (ie, query compilation takes longer), so DSI
connections tend to take longer to process queries. (In one case replication fell behind during the first night's batch
processing and never caught up due to the slow(er) optimizer in the replicate database.) Once we configured 'enable
literal autoparam' and 'statement cache size' we found that replication returned to pre-ASE 15 levels of performance
(due to the heavy re-use statement cache query plans in the replicate database).
NOTE: RepServer 15.0.1 has a new feature (Dynamic SQL, aka Prepared Statements) which should provide a similiar benefit
to using 'enable literal autoparam' and 'statement cache size' in a replicate ASE 15.x dataserver. This feature
actually offloads some of the processing (eg, statement cache management) from the RDS, though you'll also see an
increase in procedure cache usage (prepared statements == dynamic stored procs == lightweight stored procs) in the RDS.
And yeah, there are some tradeoffs to using statement cache, eg, they suffer from the same problems as a stored proc
that can be compiled in one of several different ways depending on the value of input parameters.
select * from sysobjects where id between 1 and 1 (use index on id ?)
select * from sysobjects where id between -2147000000 and 2147000000 (perform table scan ?)
The first query will be compiled and placed in statement cache with a query plan that is best for it. The second query
will be hashed and match the first query's plan from statement cache; this could lead to the second query performing
poorly (due to using the first query's plan) when it would probably perform more efficiently with a different query plan.
Similar issues arise if you switch the order of the 2 queries.
--------------------
Anyhoo, 'nuff of the tangents and rambling for now ...

UPDATE: version 12.5.3/EBF 13057 ESD#5
I've reconfigured the 'Statement Cache' down to 0, which was reflected
in the sysmon, 'statements not in cache' went to non-zero, at which
point I re-configured my 'statement cache' to 10Mb and was able to get
it back to non-zero. But, my overall procedure cache size is much
larger than it was originally and I'm not seeing that come down to ~0
which is what it originally what is was for months leadiing up to my
testing. odd.

It's a combination of both, procedures and statements (this is
replicated environment). My belief is that these 'statements' are the
inserts/updates/deletes from the RS since we're not replicating
procs.
From my readings, I would not expect to see the 'procedure cache
behavior' because an adjustment was made to the 'procedure cache size'
to offset what I created for the 'statement cache'. The only way I
get my 'procedure removal' to drop to near 0 again is by turning of
'statement cache' completely.