SQL Server Performance Forum – Threads Archive
Cache TablesHow To Cache Table in SQL2000
You can refer to DBCC PINTABLE, although itcan provide performance improvements, it must be used with care. If a large table is pinned, it can start using a large portion of the buffer cache and not leave enough cache to service the other tables in the system adequately. If a table larger than the buffer cache is pinned, it can fill the entire buffer cache. And permissions are default to SYSADMIN group.
Can you tell us a little about why you want to cache a table?
Brad M. McGehee
The reason why Brad is asking this is DBCC PINTABLE does not help a whole lot, at least in my opinion. And the room for error is so great that it really is not worth it. Perhaps your problem might stem from a source that has another solution besides permanently caching the table in memory.
"How do you expect to beat me when I am forever?"