Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a few tables in my database that should not be cached.

How do I tell SQL Server not to cache a table's pages or how do I flush a single table from the cache?

Flushing all the cache is not an option.

I'm using SQL Server 2008 and SQL Server 2008 R2.

share|improve this question

migrated from stackoverflow.com Oct 2 '11 at 14:41

This question came from our site for professional and enthusiast programmers.

3  
On what basis have you decided that the tables should not be cached? –  Joe Stefanelli Sep 30 '11 at 13:23
3  
If your tables are being cached then they're being queried. If they are accessed infrequently then the SQL Server Memory manager will flush them out in preference of other objects at the required time. What's your goal here? –  John Sansom Sep 30 '11 at 13:45
3  
@John - No idea what the OP's goal is but there are times where this would be useful. e.g. doing a one off scan against a large table without flushing the buffer pool. –  Martin Smith Sep 30 '11 at 13:50
2  
Some of the reasons: low-priority table, iis/rs on the same machine as sql server, power usage, etc. The most important is that some tables are low priority tables with regard to the application's performance and can be safely read from disk whenever they are accessed. By keeping those tables out of the buffer pool, memory analysis becomes somewhat easier. –  user973156 Oct 3 '11 at 9:33
1  
@user973156: Is your code and design that good that you can now only 2nd guess SQL Server to make things better? –  gbn Oct 3 '11 at 14:37

2 Answers 2

up vote 9 down vote accepted

There is no way of doing this.

DBCC DROPCLEANBUFFERS doesn't accept any parameters for a specific database or object. Internally SQL Server can do this at a database level however as when a database is AUTO_CLOSEd all corresponding pages are removed from the buffer cache.

Also internally SQL Server can mark certain pages such that they will be the first ones kicked out by the lazy writer. This is used by DMVs such as sys.dm_db_index_physical_stats to avoid flushing the buffer pool as alluded to in this article but this functionality is not exposed in any way to us (even though it might be useful to be able to specify the same if doing a one off scan of a large table for example).

share|improve this answer

You can not specify that specific tables should not be cached. What makes you think that you don't want the tables in cache?

SQL Server does ALL its normal operations in the buffer pool so if you were able to tell SQL Server not to load a table into cache that table wouldn't be accessible for any normal DML operations.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.