2

If you have multiple instances of SQL Server running on the same windows server system,

a) Does buffer pool is shared amount multiple instances?

b) What is the effect of running DBCC DROPCLEANBUFFER OR DBCC FREEPROCCACHE? Would that only affect the SQL Server instance you ran the command on or all the instance(s) on the windows box would flush SQL server cache?

migrated from stackoverflow.com Jan 11 '12 at 14:58

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

6

The buffer pool in SQL Server isn't shared across intances.

DBCC DROPCLEANBUFFER removes all "clean" pages from the buffer pool, "clean" pages are pages which haven't been modified since being read into the buffer pool.

DBCC FREEPROCCACHE removes all cached execution plans from the plan cache.

  • OK your answer make sense. Max Server Memory setting controls buffer pool memory allocated to that SQL instance. – SQL Learner Jan 11 '12 at 16:16
  • That correct Max Server Memory in SQL Server 2008 R2 and lower controls the Single Page Allocations, Multiple Page Allocations aren't controled by the Max Server Memory see for more details sqlservercentral.com/articles/Memory/74867 – MartinC Jan 11 '12 at 18:37
0

Just complementing MartinC's answer:

Instances yes, but regarding databases

you can definitely use the following command to affect only your specific database:

DBCC FREESYSTEMCACHE ('userdatabase') -- cleans cache for specific user database

I know this is not your question, but it is something I found it might be useful to add.

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.