Stephen Cook wrote:
> Magnus Hagander wrote:
>> I would guess they're referring to the ability to "pin" a table into
>> memory, so that it always stays in the cache regardless of what else
>> the database is doing. There is a narrow use-case where this can be
>> very useful, but it can also be a very dangerous tool (hint: if you
>> pin a table that grows up to say 80-90% of your RAM size, your
>> database will not be fast for anything else)
>
> I know that MS removed this ability in SQL Server 2005 for pretty much
> this reason; it's usefulness was greatly outweighed by people screwing
> up their systems by not calculating things correctly.
>
What they removed was "dbcc pintable", which would specify that data
pages for a table should be pinned in the general buffer cache as they
are requested. This feature didn't allow you to divide up your buffer
cache, and so this rather pointless feature went away in SQL Server
2005. A few large, active pinned tables is obviously going to really
wreck performance for most databases.
What SQL Server never had is more like what you get with Sybase, where
you can partition your buffer cache into different regions of whatever
fraction of the overall buffer cache you wish. This is IMHO a far more
useful implementation. You can specify which regions a particular (set
of) tables should use. You can further specify different page sizes for
each buffer (say 2k pages for the intensely oltp stuff, and 16k pages
for the more olapy things). You don't end up trying to leave *every*
page of a table in memory this way, since LRU (or whatever method) will
still recycle pages as needed within a named cache. This was all there
in version 11 of the product, which was the last one I ever worked with
(and is very dated at this point). This feature never made it to SQL
Server since Microsoft went off and did their own thing well before this.
It's more this Sybase-type implementation I assumed the original poster
was asking about?
You can do something kind of similar in MySQL with the MyISAM storage
engine, but I've not heard of too many shops actually doing this (who
knows). The MySQL manual seems to strongly recommend it, anyway.
Paul
