I'm sure there will be cases when some human assisted caching algorithm will
perform better than an mathetical statistical based design, but it will also
depend on the "human". And it probably will make thing worse when workload
changes and human doesn't realize. It must be considered that, today,
hardware cost is not the %90 of budget that it used to be. Throwing hardware
at the system can be as much expensive as throwing certified "it stuff".
(just think in coffee budget! :-) )
If you need to improve "user perception", you can do others things. Like
caching a table in your client (with a trigger for any change on table X
updating a table called "timestamp_table_change" and a small select to this
table, you can easily know when you must update your client). If it is a
application server, serving http request, then "user perception" will be
sticked to bandwidth AND application server (some of them have cache for
request).
FYI, I don't recall a mechanism in MSSQL to cache a table in buffers. Oracle
has some structures to allow that. (you know) It uses his own buffer. Since
version 9i, you can set three different data buffers, one (recycled cache)
for low usage tables (I mean tables with blocks which don't have too much
chance to be queried again, like a very large historical table) , one for
high usage tables (keep cache), and the regular one (difference is in
algorithm). And you must also set a buffer cache size for tablespaces with
different block size. But there is no such thing as "create table x keep
entirenly in buffer". And above all things, oracle doc always states "first,
tune design, then tune queries, then start tunning engine".
greetings.
