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.
buffer cache