Post reply

dbcc checkdb takes up all my buffer cache

  • February 16, 2009 at 8:18 pm

    #128417

    Hi when I run a dbcc it seems to take up all my buffer cache. That is to say after dbcc my ring buffers report a %100 Utilization, target and total memory are the same (before hand the total is lower). How can I stop the dbcc using all the buffer cache?

  • February 16, 2009 at 9:57 pm

    #944942

    Add more memory?

    You can't tune dbcc. It has to run through all rows in all tables and that will eat up memory to complete.

  • February 17, 2009 at 12:24 am

    #944981

    bodhilove (2/16/2009)

    Hi when I run a dbcc it seems to take up all my buffer cache.

    Hardly surprising. CheckDB is very intensive and it has to read the entire database, sometimes more than once.

    Run it during a downtime window and don't worry about it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 17, 2009 at 8:46 pm

    #945626

    the problem is that after I run it the .memory_manager.total server memory increases to the target server memory and does not let up until a restart. I have experienced excessive paging (according to ops manager) because of this. I am running a 64-bit sql server on 2005.

  • February 17, 2009 at 11:19 pm

    #945681

    Then set the server's max memory. You should do that anyway on a 64-bit server because of SQL's tendency to eat memory.

    How much memory on the server?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 18, 2009 at 5:06 pm

    #946417

    yep I have set the max mem. I have 16GB and 5 instances.

    max mem is as follows

    inst1 1GB - 1 database mirrored

    inst2 500MB - 3 databases mirrorred 2, databases unmirrored

    inst3 500MB - 2 databases mirrored

    inst4 1GB - 6 databases mirrored, 3 databases unmirrored

    inst5 4GB - 1 database mirrored, 4 databases unmirrored

    It have 5GB of available memory.

    I may be wasting your time... sorry if that is the case. You see we are running operations manager as well and I get and alert if the %commited bytes in use get to 80%. Presently it is at 68%. I am a bit spooked by increasing max mem, besides all my instances are reporting the total and target memory to be same value except the 4GB one so from my way of thinking I need to increase max mem on all which I fear will start a load of paging. I may just try and start to tune 1 instance and see if that pushes me over the edge.

    thanks!

  • February 19, 2009 at 1:08 am

    #946495

    With 16 GB, you can probably safely increase the max memories to total 14.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 19, 2009 at 8:32 am

    #946764

    Also, do you really need so many instances? If you can consolidate, SQL can potentially make better use of memory.

  • February 19, 2009 at 11:31 am

    #946954

    You can also separate the checks into parts and don't run checkdb at its full:

    For example run it once with physical_only then do the checktables next time then checkalloc and so on...


    * Noel

  • February 19, 2009 at 11:40 am

    #946966

    But you dont have to do checktable, checkalloc once checkdb is done as checkdb checks integrity on all objects.

  • February 19, 2009 at 6:00 pm

    #947280

    Thanks for everyones help.

    Ok I cannot increase the amount of RAM to 14GB without causing paging (usually triggered by %committed bytes in use over 80%).

    I would prefer to look at other option before I consolidate my database anymore.

    I am interested in running a dbcc checkdb in parts but not sure what I need to do to make sure the db is checked properly. Can you help?

  • February 19, 2009 at 6:56 pm

    #947287

    Do you stagger your dbcc checkdbs? If so, I have a couple of err, lateral, suggestions:

    1. Consider allowing SQL Server to manage the memory which may serendipitusouly have the effect of you never hiting the 80% (although as Gail mentioned 64bit OS's seem to have an unusal attachement with memory).

    2. Replicate dynamic memory allocation by using sp_configure to reduce max server memory on idling instances and increasing max server memory on the server you are about to run the dbcc checkdb by running sp_configure? I don't know what effect this will have on overall performance and it's suggested to use this approach cautiously.

    Both of the suggestions would need to be tested and monitored over time, I wouldn't jump right into production with either.

    Otherwise, it seems like you've hit a ceiling with the amount of memory you have and with the alert triggered by ops manager. You may have to consider adding more physical memory and increasing the memory allocations per instance.

    I await the whirlwind 😀

    Max

  • February 22, 2009 at 4:23 pm

    #948379

    using dbcc checkdb with physical_only runs faster but does nothing from increasing the total memory to be equal to target memory.

Viewing 13 posts - 1 through 12 (of 12 total)

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