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?

0

2 Answers 2

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.

2
  • OK your answer make sense. Max Server Memory setting controls buffer pool memory allocated to that SQL instance. Jan 11, 2012 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, 2012 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 and acknowledge you have read our privacy policy.

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