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.

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.
 

http://sqlserver-qa.net/blogs/perftune/archive/2007/05/22/battery-backup-write-cache-for-controllers-should-it-be-disabled.aspx

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).

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

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.

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.

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.

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.

 So , you are safe for write cache