Improving SQL Server Performance by Caching Writes and Reads to VMware Host SSD or RAM

Improving SQL Server Performance by Caching Writes and Reads to VMware Host SSD or RAM

If your SQL Server runs in a VMware VM and it has performance issues, the root cause of which is slow storage, then our host side caching software for VMware, called VirtuCache, will be able to fix this problem. This post explains how VirtuCache does this by explaining the read and write storage IO paths in SQL Server that factor into transaction times,  with and without VirtuCache.

Write IO Path in SQL Server (without VirtuCache):

Step 1. Writes in SQL Server modify SQL Server data pages in VM memory (SQL Server stores data in 8KB chunks that it calls a Page) and a corresponding entry is written to a transaction log, also in VM memory (called Log Buffer). ¹

Step 2. When SQL Server commits the transaction, the log buffer in SQL RAM is flushed to persistent storage. The transaction is not committed until the log buffer is written to persistent storage.¹ So, slow backend storage will result in long transaction times (high write latency). A highly transactional application with many small transactions makes this problem worse.²

Step 3. Thereafter the data pages are also flushed to persistent storage from SQL RAM. Though the flushing of data pages to persistent storage doesn’t factor into transaction commit times, it is only the flushing of transaction log entries to persistent storage that does.

N.B.You can assign large amounts of RAM to SQL server, however VM RAM assigned to SQL server doesn’t improve write performance since writes are committed in SQL Server only when the transaction log for those writes is flushed to persistent storage from VM RAM.

Write IO Path in SQL Server running in a VMware VM (without VirtuCache)

Write IO path when VirtuCache is installed:

Step 1 above remains the same.

In Step 2, when SQL transaction log entries are being flushed from VM RAM to persistent storage, VirtuCache will intercept it and write it to cache media (SSD or RAM) in the local host and another copy will be written to cache media in another ESXi host in the same ESXi cluster. VirtuCache mirrors writes to a second host to protect against data loss if the host were to fail. This policy in VirtuCache is called ‘Write-Back’ Caching. Once the log page is cached to in-host RAM / SSD by VirtuCache, VirtuCache will send a write commit to VMware, VMware then conveys the commit to the VM, and SQL Server commits the transaction.  Note that the transaction log has still not been written to the backend array. The fact that VirtuCache is caching writes to VMware host SSD or RAM, is seamless to the application, SQL Server, VM, and VMware, and they all think that the writes are committed to backend array, when in fact the writes have not yet been committed to backend storage. Now VirtuCache does have a background job that runs every few seconds that flushes the writes from VirtuCache cache media to backend array, but SQL Server has committed the transaction much before VirtuCache flushes the writes to backend array. So, the performance of your storage appliance and network doesn’t factor into SQL server transaction completion times if VirtuCache is installed on the ESXi hosts.

Step 3: Separately VirtuCache also intercepts flushing of data pages from SQL RAM to persistent storage, and caches these to cache media in the ESXi host in exactly the same way that VirtuCache does for transaction log records, thus speeding up the process of writing SQL data pages as well. However, transaction completion times in SQL Server are not affected by the write IO path of data pages.

Write IO Path in SQL Server with VirtuCache

Read IO Path in SQL Server (without VirtuCache):

Reads in SQL Server retrieve data pages from VM memory assigned to SQL Server. If the pages are not in memory, SQL Server first retrieves those pages from persistent storage, so, if your backend storage is slow, then these reads will be slow.

Also, slow writes will reduce the performance of reads. Since reads and writes are interspersed on the same thread, if writes are slow then the reads behind these writes on the same thread will be slow as well, even though these reads will most likely be serviced from VM RAM when they get to the front of the queue.

Read IO Path in SQL Server running in a VMware VM (without VirtuCache)

Read IO path when VirtuCache is installed:

When VirtuCache is installed in the ESXi host, reads that were earlier retrieved from backend storage will now be retrieved from VirtuCache cache media. Also, since VirtuCache caches both reads and writes, the problem of reads being slowed down by writes in front of it on the same thread is also taken care of.

Read IO Path in SQL Server with VirtuCache

Cross References:
¹ This Microsoft link says this – “SQL Server assembles transaction log records in a buffer, and flushes them out to durable media during commit processing. SQL will not complete the commit until the commit log record is durably stored on media. This hard flush can delay processing in very high transaction rate (low latency) systems.”
² This link says “workloads that involve lots of small transactions, like OLTP workloads, can potentially experience log-write-related bottlenecks.”