buffer cache 
Author Message
 buffer cache

Does anyone know if it's possible to prevent pages from a particular
table being placed in the buffer cache?

I have a large database (4.5GB), too big to fit in the server's
available memory (1GB). However this database size is skewed by
several large tables that make up the bulk of the database. These
tables are also accessed randomly but regularly, i.e. there is little
pattern to the rows accessed (so little chance of caching being
successful), but considerable buffer cache is used by the regularity
of access.

The cache space could be better used by other tables where the
potential cache hit ratio is higher (smaller tables, rows more
commonly used), but the large tables are using the space instead. Is
there anyway I can prevent pages from these specific tables from being
cached, such that space can be used by other tables?

I'm using SQL2000 sp2 on Windows 2000.



Tue, 27 Sep 2005 15:10:05 GMT
 buffer cache

Hello Chris,

Looks like pintable option in sp_tableoption help you.

--
Raja Venugopal
SQL Server Development Team
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.


Quote:
> Does anyone know if it's possible to prevent pages from a particular
> table being placed in the buffer cache?

> I have a large database (4.5GB), too big to fit in the server's
> available memory (1GB). However this database size is skewed by
> several large tables that make up the bulk of the database. These
> tables are also accessed randomly but regularly, i.e. there is little
> pattern to the rows accessed (so little chance of caching being
> successful), but considerable buffer cache is used by the regularity
> of access.

> The cache space could be better used by other tables where the
> potential cache hit ratio is higher (smaller tables, rows more
> commonly used), but the large tables are using the space instead. Is
> there anyway I can prevent pages from these specific tables from being
> cached, such that space can be used by other tables?

> I'm using SQL2000 sp2 on Windows 2000.



Tue, 27 Sep 2005 18:25:42 GMT
 buffer cache
Chris,

I would advise against the pinning of tables in general.  Based on what you
stated the smaller tables are accessed more frequently than the larger ones.
This in it self will usually keep the smaller tables in cache due to the way
the caching algorithm works.  Since the larger tables gets accessed less
frequently or the same rows less often the data from this table is more
likely to be forced out of cache before the smaller one that get accessed
more often.  For instance you can potentially scan an entire large table and
by the time it gets to the end of the table it will start to force out the
data it read from the beginning of the scan instead of the smaller tables
data.  This is by design and it usually works out well without pinning the
tables.

--

Andrew J. Kelly
SQL Server MVP



Quote:
> Hello Chris,

> Looks like pintable option in sp_tableoption help you.

> --
> Raja Venugopal
> SQL Server Development Team
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.



> > Does anyone know if it's possible to prevent pages from a particular
> > table being placed in the buffer cache?

> > I have a large database (4.5GB), too big to fit in the server's
> > available memory (1GB). However this database size is skewed by
> > several large tables that make up the bulk of the database. These
> > tables are also accessed randomly but regularly, i.e. there is little
> > pattern to the rows accessed (so little chance of caching being
> > successful), but considerable buffer cache is used by the regularity
> > of access.

> > The cache space could be better used by other tables where the
> > potential cache hit ratio is higher (smaller tables, rows more
> > commonly used), but the large tables are using the space instead. Is
> > there anyway I can prevent pages from these specific tables from being
> > cached, such that space can be used by other tables?

> > I'm using SQL2000 sp2 on Windows 2000.



Tue, 27 Sep 2005 19:48:35 GMT
 buffer cache
I agree about the problems of pintable, but oddly, what I wanted to do
was the opposite of pintable, i.e. rather than be able to say 'put all
pages from this table in memory as they're read and leave them there',
I wanted to say 'don't ever put any pages from this table in buffer
cache' (as they're never going to be any use).

The larger tables actually get accessed more frequently (it's just
that they get accessed randomly rather than a small set of rows),
hence my concern about their use of the buffer cache in place of the
less frequently accessed smaller tables (but these tables have the
same rows used more than once - my comment about 'more commonly used
rows' - sorry if this gave the impression of the table itself being
more frequently accessed).

However, is what you're saying Andrew that the buffer cache will never
fill entirely with pages from one table? If so, how does the buffer
cache determine what proportion of space to allocate to each table?
Does it rely on (say) a row from the smaller table having been
accessed several times already whereas the row from the larger table
will only have been accessed once? If so, I can't guarantee that that
will always be he case.


Quote:
> Chris,

> I would advise against the pinning of tables in general.  Based on what you
> stated the smaller tables are accessed more frequently than the larger ones.
> This in it self will usually keep the smaller tables in cache due to the way
> the caching algorithm works.  Since the larger tables gets accessed less
> frequently or the same rows less often the data from this table is more
> likely to be forced out of cache before the smaller one that get accessed
> more often.  For instance you can potentially scan an entire large table and
> by the time it gets to the end of the table it will start to force out the
> data it read from the beginning of the scan instead of the smaller tables
> data.  This is by design and it usually works out well without pinning the
> tables.

> --

> Andrew J. Kelly
> SQL Server MVP



> > Hello Chris,

> > Looks like pintable option in sp_tableoption help you.

> > --
> > Raja Venugopal
> > SQL Server Development Team
> > This posting is provided "AS IS" with no warranties, and confers no
>  rights.
> > Use of included script samples are subject to the terms specified at
> > http://www.microsoft.com/info/cpyright.htm.



> > > Does anyone know if it's possible to prevent pages from a particular
> > > table being placed in the buffer cache?

> > > I have a large database (4.5GB), too big to fit in the server's
> > > available memory (1GB). However this database size is skewed by
> > > several large tables that make up the bulk of the database. These
> > > tables are also accessed randomly but regularly, i.e. there is little
> > > pattern to the rows accessed (so little chance of caching being
> > > successful), but considerable buffer cache is used by the regularity
> > > of access.

> > > The cache space could be better used by other tables where the
> > > potential cache hit ratio is higher (smaller tables, rows more
> > > commonly used), but the large tables are using the space instead. Is
> > > there anyway I can prevent pages from these specific tables from being
> > > cached, such that space can be used by other tables?

> > > I'm using SQL2000 sp2 on Windows 2000.



Wed, 28 Sep 2005 19:33:08 GMT
 buffer cache
See in-line

Quote:
> I agree about the problems of pintable, but oddly, what I wanted to do
> was the opposite of pintable, i.e. rather than be able to say 'put all
> pages from this table in memory as they're read and leave them there',
> I wanted to say 'don't ever put any pages from this table in buffer
> cache' (as they're never going to be any use).

ALL data is read from cache and you can't change this.  If it's not in cache
then the storage engine pulls it from disk and puts it there so the
relational engine can work with it.

Quote:
> The larger tables actually get accessed more frequently (it's just
> that they get accessed randomly rather than a small set of rows),
> hence my concern about their use of the buffer cache in place of the
> less frequently accessed smaller tables (but these tables have the
> same rows used more than once - my comment about 'more commonly used
> rows' - sorry if this gave the impression of the table itself being
> more frequently accessed).

Its the individual pages not the whole table that count.  You can access the
table 1000 times but if its always a different row the page the row is on is
not likely to stay in cache too long (assuming there is memory pressure to
drive it out).  Basically each time you access a page in cache a counter
gets incremented.  Every so often the lazy writer comes along and checks
each page in the cache to see if it was accessed since the last time it
checked.  If not the counter gets decremented. When you attempt to read more
data than is in cache the engine uses these numbers to determine which pages
to push out of the cache to make room for the newly read data.  So if the
rows (hence the pages) in the smaller tables get read more often than the
specific pages from the larger tables, the smaller tables pages will stay in
cache.  The oldest read pages from the larger table will go first. It's a
little more complicated than that (but not much) so I suggest you get a hold
of "Inside SQL Server 2000" by Kalen Delaney and you can read more on how it
all works.

Quote:
> However, is what you're saying Andrew that the buffer cache will never
> fill entirely with pages from one table? If so, how does the buffer
> cache determine what proportion of space to allocate to each table?
> Does it rely on (say) a row from the smaller table having been
> accessed several times already whereas the row from the larger table
> will only have been accessed once? If so, I can't guarantee that that
> will always be he case.

See above.   Bottom line is that this technique is pretty good at keeping
the right data in cache and anything you do will most likely hinder the
process more than help it.  Remember the pages in cache are not
automatically pushed out of cache when their counters hit 0.  Instead they
wait until there is memory pressure.  So it is possable in some cases that
just adding a little more ram can help.   If adding ram is not an option and
you are absolutely sure the revavant pages from the smaller tables are not
being held in cache, you then might consider pinning the tables.  You do
have a situation , more than most where this may help.

--

Andrew J. Kelly
SQL Server MVP


Quote:
> > Chris,

> > I would advise against the pinning of tables in general.  Based on what
you
> > stated the smaller tables are accessed more frequently than the larger
ones.
> > This in it self will usually keep the smaller tables in cache due to the
way
> > the caching algorithm works.  Since the larger tables gets accessed less
> > frequently or the same rows less often the data from this table is more
> > likely to be forced out of cache before the smaller one that get
accessed
> > more often.  For instance you can potentially scan an entire large table
and
> > by the time it gets to the end of the table it will start to force out
the
> > data it read from the beginning of the scan instead of the smaller
tables
> > data.  This is by design and it usually works out well without pinning
the
> > tables.

> > --

> > Andrew J. Kelly
> > SQL Server MVP


message

> > > Hello Chris,

> > > Looks like pintable option in sp_tableoption help you.

> > > --
> > > Raja Venugopal
> > > SQL Server Development Team
> > > This posting is provided "AS IS" with no warranties, and confers no
> >  rights.
> > > Use of included script samples are subject to the terms specified at
> > > http://www.microsoft.com/info/cpyright.htm.



> > > > Does anyone know if it's possible to prevent pages from a particular
> > > > table being placed in the buffer cache?

> > > > I have a large database (4.5GB), too big to fit in the server's
> > > > available memory (1GB). However this database size is skewed by
> > > > several large tables that make up the bulk of the database. These
> > > > tables are also accessed randomly but regularly, i.e. there is
little
> > > > pattern to the rows accessed (so little chance of caching being
> > > > successful), but considerable buffer cache is used by the regularity
> > > > of access.

> > > > The cache space could be better used by other tables where the
> > > > potential cache hit ratio is higher (smaller tables, rows more
> > > > commonly used), but the large tables are using the space instead. Is
> > > > there anyway I can prevent pages from these specific tables from
being
> > > > cached, such that space can be used by other tables?

> > > > I'm using SQL2000 sp2 on Windows 2000.



Thu, 29 Sep 2005 00:39:35 GMT
 buffer cache
In addition to the excellent explanation andrew offered, SQL DBA's live a
long and happy life NOT having the knobs the other DBMS's do with buffer
pools.  Trust me, SQL Server does a better job at managing cache than a
highly skilled buffer pool tuner could do about 99.999%   It's unlikely your
scenario is part of the .00001%.

Environments have gotten too complex and buffer algorithms too smart to make
manual buffer tuning a viable option unless you have an unlimited staff
budget.



Quote:
> See in-line

> > I agree about the problems of pintable, but oddly, what I wanted to do
> > was the opposite of pintable, i.e. rather than be able to say 'put all
> > pages from this table in memory as they're read and leave them there',
> > I wanted to say 'don't ever put any pages from this table in buffer
> > cache' (as they're never going to be any use).

> ALL data is read from cache and you can't change this.  If it's not in
cache
> then the storage engine pulls it from disk and puts it there so the
> relational engine can work with it.

> > The larger tables actually get accessed more frequently (it's just
> > that they get accessed randomly rather than a small set of rows),
> > hence my concern about their use of the buffer cache in place of the
> > less frequently accessed smaller tables (but these tables have the
> > same rows used more than once - my comment about 'more commonly used
> > rows' - sorry if this gave the impression of the table itself being
> > more frequently accessed).

> Its the individual pages not the whole table that count.  You can access
the
> table 1000 times but if its always a different row the page the row is on
is
> not likely to stay in cache too long (assuming there is memory pressure to
> drive it out).  Basically each time you access a page in cache a counter
> gets incremented.  Every so often the lazy writer comes along and checks
> each page in the cache to see if it was accessed since the last time it
> checked.  If not the counter gets decremented. When you attempt to read
more
> data than is in cache the engine uses these numbers to determine which
pages
> to push out of the cache to make room for the newly read data.  So if the
> rows (hence the pages) in the smaller tables get read more often than the
> specific pages from the larger tables, the smaller tables pages will stay
in
> cache.  The oldest read pages from the larger table will go first. It's a
> little more complicated than that (but not much) so I suggest you get a
hold
> of "Inside SQL Server 2000" by Kalen Delaney and you can read more on how
it
> all works.

> > However, is what you're saying Andrew that the buffer cache will never
> > fill entirely with pages from one table? If so, how does the buffer
> > cache determine what proportion of space to allocate to each table?
> > Does it rely on (say) a row from the smaller table having been
> > accessed several times already whereas the row from the larger table
> > will only have been accessed once? If so, I can't guarantee that that
> > will always be he case.

> See above.   Bottom line is that this technique is pretty good at keeping
> the right data in cache and anything you do will most likely hinder the
> process more than help it.  Remember the pages in cache are not
> automatically pushed out of cache when their counters hit 0.  Instead they
> wait until there is memory pressure.  So it is possable in some cases that
> just adding a little more ram can help.   If adding ram is not an option
and
> you are absolutely sure the revavant pages from the smaller tables are not
> being held in cache, you then might consider pinning the tables.  You do
> have a situation , more than most where this may help.

> --

> Andrew J. Kelly
> SQL Server MVP



> > > Chris,

> > > I would advise against the pinning of tables in general.  Based on
what
> you
> > > stated the smaller tables are accessed more frequently than the larger
> ones.
> > > This in it self will usually keep the smaller tables in cache due to
the
> way
> > > the caching algorithm works.  Since the larger tables gets accessed
less
> > > frequently or the same rows less often the data from this table is
more
> > > likely to be forced out of cache before the smaller one that get
> accessed
> > > more often.  For instance you can potentially scan an entire large
table
> and
> > > by the time it gets to the end of the table it will start to force out
> the
> > > data it read from the beginning of the scan instead of the smaller
> tables
> > > data.  This is by design and it usually works out well without pinning
> the
> > > tables.

> > > --

> > > Andrew J. Kelly
> > > SQL Server MVP


> message

> > > > Hello Chris,

> > > > Looks like pintable option in sp_tableoption help you.

> > > > --
> > > > Raja Venugopal
> > > > SQL Server Development Team
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > >  rights.
> > > > Use of included script samples are subject to the terms specified at
> > > > http://www.microsoft.com/info/cpyright.htm.



> > > > > Does anyone know if it's possible to prevent pages from a
particular
> > > > > table being placed in the buffer cache?

> > > > > I have a large database (4.5GB), too big to fit in the server's
> > > > > available memory (1GB). However this database size is skewed by
> > > > > several large tables that make up the bulk of the database. These
> > > > > tables are also accessed randomly but regularly, i.e. there is
> little
> > > > > pattern to the rows accessed (so little chance of caching being
> > > > > successful), but considerable buffer cache is used by the
regularity
> > > > > of access.

> > > > > The cache space could be better used by other tables where the
> > > > > potential cache hit ratio is higher (smaller tables, rows more
> > > > > commonly used), but the large tables are using the space instead.
Is
> > > > > there anyway I can prevent pages from these specific tables from
> being
> > > > > cached, such that space can be used by other tables?

> > > > > I'm using SQL2000 sp2 on Windows 2000.



Thu, 29 Sep 2005 15:53:26 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. PerfMon - SQL Buffer - Buffer Cache Hit Ratio

2. Cache hit vs. Buffer cache hit

3. Cache hit % vs. Buffer Cache hit %

4. dmf.cache and Unix Buffer Cache

5. Buffer cache content

6. buffer cache hit ratio drops to zero

7. Buffer cache hit ratio

8. buffer cache hit ratio in sysperfinfo

9. buffer cache and memory pool

10. SQL Server Buffer Cache question

11. clearing buffer cache

12. SQL server Buffer/Cache manager takes 99.99% hits


 
Powered by phpBB® Forum Software