SQL Server Performance

Write Caching

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by melvinlusk, Aug 1, 2008.

  1. melvinlusk Member

    I heard a myth that you shouldn't use write caching on your SAN volumes if you expect a high number of writes. Has anyone had experience with this?
    My particular setup is an IBM DS4800 with several different LUN's, all of them are RAID 0+1, the primary file group is split into 4 different files spread across 4 different LUN's, and the log volume resides on a single RAID 0+1. tempdb is also on it's own LUN.
    The drives are 300 GB fibre channel. Each LUN has 4 drives. The server is 64-bit SQL 2005 Enterprise.
    melvinlusk, Aug 1, 2008
    #2

  2. satya Moderator

    How big is the database?
    How actively the transactions are written to disk?>
    As you may be aware SAN has two separate cache's, a READ cache and a WRITE cache, remember this is SAN cache not system memory, That when the SAN is confirming the transaction is complete (written to disk) to the operating system (even though it has not actually been flushed to disk yet), that the SAN read cache, being separate from the SAN write cache.
    Many disk drives (SATA, ATA, SCSI and IDE based) contain onboard caches of 512 KB, 1 MB, and larger. Drive caches usually rely on a capacitor and not a battery-backed solution. These caching mechanisms cannot guarantee writes across a power cycle or similar failure point. They only guarantee the completion of the sector write operations. As the drives continue to grow in size, the caches become larger, and they can expose larger amounts of data during a failure.

    86903 (http://support.microsoft.com/kb/86903/) SQL Server and caching disk controllers
    46091 (http://support.microsoft.com/kb/46091/) Using hard disk controller caching with SQL Server
    http://sqlserver-qa.net/blogs/perft...he-for-controllers-should-it-be-disabled.aspx
    satya, Aug 6, 2008
    #3

  3. melvinlusk Member

    Thanks for the info, I'll read up on it.
    I have full redundancy at every level, so I'm really more concerned about the cache affecting performance than I am about failures. The DB size is currently minimal but I expect it to grow to about 700 GB in about 4 years. I've experimented with turning off write caching in the past but didn't seem to notice much of a difference (on am EMC SAN, not IBM).
    melvinlusk, Aug 6, 2008
    #4

  4. satya Moderator

    If I were then I'm not inclined to enable the write caching, as long as the RAID disks are beefed up and data is managed with smaller transactions for better WRITE performance.
    satya, Aug 6, 2008
    #5

  5. melvinlusk Member

    After reading this article:
    http://support.microsoft.com/kb/234656
    It seems that the main concern with write caching is for data atomicity rather than performance. My SAN has battery-backed cache, not to mention enough UPS systems to power the entire city. I think it's best if I leave it enabled.
    melvinlusk, Aug 8, 2008
    #6

  6. moh_hassan20 New Member

    [quote user="melvinlusk"]
    After reading this article:
    http://support.microsoft.com/kb/234656
    It seems that the main concern with write caching is for data atomicity rather than performance. My SAN has battery-backed cache, not to mention enough UPS systems to power the entire city. I think it's best if I leave it enabled.
    [/quote]
    So , you are safe for write cache
    moh_hassan20, Aug 12, 2008
    #7

  7. satya Moderator

    Glad to know that you have digged out further on the system side to get more information, unless it is required I wouldn't touch the storage area without proper analysis in hand.
    satya, Aug 11, 2008
    #8

  8. moh_hassan20 New Member

    what is the maximum time SAN can keep cache in case of SAN down for any reason.?


    moh_hassan20, Aug 6, 2008
    #9

Share This Page