SQL Server Performance

Array Accelerator / Battery Backed Write Cache...

Discussion in 'Performance Tuning for Hardware Configurations' started by ymtlc, May 21, 2007.

  1. ymtlc New Member


    I read in multiple places that Array Accelerator / Battery Backed Write Cache should be disabled in SQL Server environment. I have also read that some turn it on and set it to 100% read / 0% write (hence use it only as read-ahead, which should be safe). Others are actually using the BBWC part of the controller (i.e. 50% /50% ) . Microsoft suggests turning it off unless the controller is designed for database environments. Are there controllers especially designed for SQL Server ? Also, are the new controllers, especially HP SmartArray P400 / P600 safe for SQL Server with Array Accelerator turned on ? Can anybody explain why is running Smart Arrays' Accelerator/BBWC bad, since virtually ALL SAN Storages run huge cache (2GB) in them ? Isn't that the same in case of power failure etc ?

  2. satya Moderator

    Fyi KBAhttp://support.microsoft.com/kb/234656 &http://support.microsoft.com/kb/Q86903 on more information why to use and what to take care.

    Further you should make sure that your disk controller has features such as safeguards to avoid uncontrolled reset of the caching controller, on-board battery backup, and mirrored or error checking and correcting memory. May check with the hardware vendor to ensure that the write cache includes these and any other features necessary to avoid data loss.

    Also it is a best practice to place the transaction log file on a disk with the RAID controller, where on this array should have a (battery-backed) write-back cache to speed log writes.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. ymtlc New Member

    Thank you for your response. I read the articles you suggested. To me, Microsoft's points are somehow confusing, seem to be written by the lawyer... you can, but you need to check, but but but... etc.. There seems to be no straight answer to which controlers are ok and which are not. Is there a list of known controllers for which turning on the cache is ok (certified etc...) ???<br /><br />Well, I'm looking at HP Smart Array P400 P600 with BBWC (has ECC Memory with battery backup), to make things worse, my hardware vendor says it's ok to turn on BBWC for SQL Server but can't provide a "proof" <img src='/community/emoticons/emotion-1.gif' alt=':)' /> meaning that it's certified to work with SQL Server / or it's "designed for DBMS systems".... I also looked at HP website to no avail (this should be the first place to find such information).<br /><br />YMTLC
  4. joechang New Member

    the reason some say turn it off is that in tests with short bursts,
    where all data fits in cache, the cache does help

    but in sustained ops, once the cache size is exceeded,
    the cache does not help, and can even degrade performance

    HP does use their own controllers in TPC-C benchmarks, which has a data integrity component, ie, power failure, and requires recoverability

    before you turn it on in production,
    verify that you have a working power failure recovery process

Share This Page