10

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.

Improve this question
8
  • 4
    On what basis have you decided that the tables should not be cached?
    – Joe Stefanelli
    Commented Sep 30, 2011 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
    Commented Sep 30, 2011 at 13:45
  • 4
    @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
    Commented Sep 30, 2011 at 13:50
  • 3
    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.
    – Catalin Adler
    Commented Oct 3, 2011 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
    Commented Oct 3, 2011 at 14:37

2 Answers 2

Reset to default
12

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).

Improve this answer
7

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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