Setting the Read/Write Cache for Different Types of Workloads

My RAID controller lets me set the amount of cache dedicated to read and write operations. It's currently set to 50 percent read and 50 percent write. What are the best settings for different types of SQL Server workloads?

It seems reasonable to have 50 percent or more of your cache devoted to reads when you're running a read-intensive SQL Server workload. Data warehousing systems are dominated by reads, and even many online transaction processing (OLTP) systems have a lot of read activity. But it's often best to set cache to 100 percent for write rather than devoting any cache to read, especially on low-end and midrange I/O subsystems.

Let me explain why. Having any data in the read cache benefits performance only if someone needs to read the data again and it's still in cache, right? Well, remember that SQL Server maintains its own buffer cache and that SQL Server's cache management is intelligent and optimized for database workloads. Remember, too, that the cache on an I/O array controller is typically small compared to the size of the database, the amount of memory devoted to SQL Server, and SQL Server's buffer cache. So, for example, if UserA reads data and UserB needs that same data later, it's unlikely that the data would be in the I/O controller cache and not in the SQL Server buffer cache. And if the data is in the SQL Server buffer cache, there's no benefit in having the data in the read cache at the controller level.

In contrast, buffering a write in the write cache on the I/O controller is almost always beneficial from a performance perspective. I/O performance in write-heavy workloads is typically much better if the controller cache can do a reasonably good job of keeping up with write requests so that there's room in the cache when SQL Server or the OS makes a write request.

Obviously, transaction-log writing would benefit from a large write cache.Workloads seemingly dominated by read activity might still do a lot of writing to disk if TEMPDB is used extensively, and even databases that have moderately low delete, insert, and update activity need to be checkpointed regularly. In a typical SQL Server environment, the relative benefits of devoting the entire controller cache to write almost always outweigh the benefits of reserving any of it for read. Still, I rarely find that sites use 100 percent write cache because most array controllers default to devoting some amount of the cache to read activity.

Now some caveats: First, my advice here doesn't always apply to high-end I/O subsystems with very intelligent controllers. Second, as with any topic in performance tuning, the correct answer to any question is always "it depends." I've made some generalizations and simplifications in this article to help you think about what your setting should be.You'll often be better off devoting a larger amount of controller memory to write cache, but that's not always the best answer.To find the best answer, test, test, and then test some more. Don't assume that the default settings are best, and don't assume that your hardware vendor has set up the controller correctly. My experience shows that many server vendors configure servers for generic workloads rather than for the needs of a database. Did I say test, test, and test again?