1

We have several databases on an instance of MS SQL Server 2012. Using Glenn Berry's DMV queries, I see the memory buffer pool used by each database.

Is there a way I can limit AltDB to only use a maximum of 10GB. My goal is that more memory is used by MainDB.

DB     | Memory Buffer Pool in GB
MainDB | 34.00
AltDB  | 24.00
2

Is there a way I can limit AltDB to only use a maximum of 10GB.

No, not even with Resource Governor.

From BOL

The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query.

A good article to read : The clock hands of the buffer cache

My goal is that more memory is used by MainDB.

Another alternative is to have MainDB on a different instance capped by Max Memory. This way you can reduce the max memory for AltDB.

  • Putting it on a separate instance - this is a great suggestion and really your only option. Unless you want to create a job that monitors buffer pool usage by database, and sets that database offline/online when it finds it has gone over 10GB. But that is like using a tank to get rid of a squirrel. – Aaron Bertrand Feb 7 '17 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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