DaveWentzel.com            All Things Data

Controller Cache

 

So, your SAN has a data cache and generally they aren't huge.  A few GB is standard.  And your SAN is hooked up to many different servers/applications.  So, what is the best controller cache configuration for (your) SQL Server? 
 
You can split the controller cache into read and write caches specifically.  Without testing and examples to back up my claims, assume that increasing the read cache, or having a higher read cache than the default, is NOT beneficial.  Why?  Your servers will likely have at least a few GB of memory themselves, so if a sql server needs add'l data it will likely find it in its own cache.  If it doesn't then it goes to the SAN where it is highly unlikely it will be found in controller cache.  Further, SQL Server uses a read-ahead algorithm to prefetch pages for read operations, negating further the need for a large SAN read cache.   Your RDBMS is in essence one big cache, bigger than your SAN cache even.  
 
Caching disk IO has significant CPU overhead with a negative impact to performance in total.  Disabling cache entirely is often the best choice for performance reasons, even though logically many people's first inclination would be that cache, any cache, is better than no cache at all.  
 
With a write cache you can assume that the SAN will be able to send the IO completion message back to the server faster if it can do this once the write cache was updated, vs the actual rotational media.  Note that your controller cache needs to be battery backed, otherwise you open yourself to risk.  
 
Even with a write cache there is debate as to whether the write cache is hurting or helping performance.  The cache algorithm itself may hinder the raw IO of the underlying arrays.  I'm still not totally convinced on this point and more testing would need to occur.  For very write-intensive periods, such as during a checkpoint, the write cache should help performance.  
 
Cache can mask your IO problems vs helping IO performance.  
 
Write-Through Cache vs Write-Back Cache
A write through cache eliminates the risk of subsystem failure by writing to disk before acknowledging the IO completion.  A write back cache acknowledges the IO completion after the write to cache only. 
 

Add new comment