Very Computer

by **Larr** » Sun, 04 Jul 1999 04:00:00

Folks,

On the recommendation of a Sybase consultant, I defined a 20 MB cache
area on my 11.5.1 server. I allocated as much of this space as possible
(all but 1 MB) as a 16K I/O buffer pool. I then bound several tables
that were being used in a 5-way query join with lots of index-based
scanning to this cache. When I ran sp_sysmon, I found that the 16K
buffer was being used only 58% of the time! Doing a showplan on the
queries confirms this; some index scans use the 16K buffer and others
use the 2K buffer. I have two questions:

1) Why isn't the 16K buffer pool used more (Large I/Os Denied is
42%!). How can I improve this?

2) When the 16K I/O isn't used for a table bound to this cache, is
the I/O restricted to the measly 1 MB 2K pool in this cache, or can it
get more 2K memory from the default data cache? Should I have allocated
a greater percentage of the new cache to the 2K buffer pool?

Thanks in advance for any help!

Larry

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

by **Anthony Mandi** » Sun, 04 Jul 1999 04:00:00

> On the recommendation of a Sybase consultant, I defined a 20 MB cache
> area on my 11.5.1 server. I allocated as much of this space as possible
> (all but 1 MB) as a 16K I/O buffer pool. I then bound several tables
> that were being used in a 5-way query join with lots of index-based
> scanning to this cache. When I ran sp_sysmon, I found that the 16K
> buffer was being used only 58% of the time! Doing a showplan on the
> queries confirms this; some index scans use the 16K buffer and others
> use the 2K buffer. I have two questions:

> 1) Why isn't the 16K buffer pool used more (Large I/Os Denied is
> 42%!). How can I improve this?

Not sure without knowing what your table sizes are like. Could
it be that 2K io would suffice (even with the restricted 2K
pool size)?

Quote:> 2) When the 16K I/O isn't used for a table bound to this cache, is
> the I/O restricted to the measly 1 MB 2K pool in this cache, or can it

If you've bound them to that cache I think it does. Does sp_sysmon
show you or showplan?

Quote:> get more 2K memory from the default data cache? Should I have allocated
> a greater percentage of the new cache to the 2K buffer pool?

It might be better to go back to first principles. Use the default
data cache and just set up a 16K pool. Monitor the performce there
first. Check how the 16K pool gets used. Review your 5 way join too.
You may be able to optimise it somehow. Also check the layout of
your disk io subsystem. Try and isolate any bottlenecks and then
work on caches last.

-am

by **Mariano Corra** » Sun, 04 Jul 1999 04:00:00

> 1) Why isn't the 16K buffer pool used more (Large I/Os Denied is
> 42%!). How can I improve this?

Two common reasons are:
1 - Allocation pages always use the 2 KB pool. There is one every
256 pages. So, one out of 32 segments cannot be accessed
through the 16 KB because of that.
2 - A segment cannot be brought into a 16 KB buffer if one of
its 8 pages is already in memory (in a 2 KB buffer, commonly)

Regards,
Mariano Corral

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

Very Computer

Using (or not using) 16K buffer pools

Using (or not using) 16K buffer pools

Using (or not using) 16K buffer pools

Using (or not using) 16K buffer pools