Post reply

clearing buffer pool

  • October 1, 2019 at 9:49 am

    Go to Answer

    #3684881

    Hi All,

    We have a QA environment where we have bunch of databases(18+) but with limitied RAM. After having some disussions with Application teams, we came to know some databases are READONLY and some they don't use it because the have not been refreshed from Prod. So, actually there are only 2-4 databases in use.

    My question is, I wanted to know whether we can release some memory from buffer pool so that the portion of memory can be used for Active databases which are accessed heavily for that sprint testing.

    1. Can we clear contents of buffer pool of specific database? if so, how can we clear the data pages of that database which are not in use?

    2. Can we clear plan cache of specific database ? If so, How to do it ?

    3. Can we measure using a query how much memory was taken before and released after doing clearing cache.

    Environment : SQL 2012+ EE

    I know some of you might come back at me and ask, why cant you increase the memory on the server. But, it's the management decision and they want to invest more on that server. In future, they want to move it Azure.

    Thanks,

    Sam

  • October 1, 2019 at 12:07 pm

    #3684946

    As far as I can see DBCC Dropcleanbuffers won't allow you to specify a database

    but dbcc freeproccache can drop individual plans - if you can identify the plan handles by database then you could create a loop and drop all of the plans

    see https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-2017

    you could also take the unused databases offline

    I also just found this - DBCC FLUSHPROCINDB - clears the plan cache for a single db

    https://www.mssqltips.com/sqlservertip/4714/different-ways-to-flush-or-clear-sql-server-cache/

    MVDBA

  • October 1, 2019 at 8:47 pm

    Answer

    #3685134

    To be honest, you don't have to do a thing.  SQL Server will flush the buffer pool based on the work load and what fits into memory all on its own.  If you have a small amount of memory, it's also going to matter even less.  If something doesn't fit in memory and it's needed now, SQL Server will kick out the oldest unused stuff and load the new.  If there's not enough room memory to actually load the new stuff even when the old stuff has all been kicked out of memory, then you'll make a visit to the swap file (which is, of course, terribly slow even if you have SSDs).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 4, 2019 at 6:50 am

    #3685910

    Thanks everyone for sharing your thoughts.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy