- No SQL code changes.
- No Reboots.
- No Disruption.
Introduction to DymaxIO™ fast data software for SQL
Whether running SQL in a physical or virtualized environment, most SQL DBAs would welcome faster storage at a reasonable price.
The DymaxIO fast data software (Diskeeper®, SSDkeeper®, and V-locity® are now DymaxIO) from Condusiv Technologies is designed to provide exactly that, but using the storage hardware that you already own. It doesn’t matter if you have direct attached disks, if you’re running a tiered SAN, have a tray of SSD storage or are fortunate enough to have an all-flash array; that storage layer can be a limiting factor to your SQL Server database productivity.
The DymaxIO software reduces the amount of storage I/O traffic that has to go out and be processed by the disk storage layer, and streamlines or optimizes the data which does have to still go out to disk.
The net result is that SQL can typically get more transactions completed in the same amount of time, quite simply because on average, it’s not having to wait so much on the storage before being able to get on with its next transaction.
DymaxIO can be downloaded and installed without any disruption to live SQL servers. No SQL code changes are required and no reboots. Just install and typically you’ll start seeing results in just a few minutes.
Before we take a more in-depth look at that, I would like to briefly mention that, the Condusiv software was awarded the Microsoft SQL Server I/O Reliability Certification. This means that whilst providing faster storage access, Condusiv’s software didn’t adversely affect the required and recommended behaviors that an I/O subsystem must provide for SQL Server, as defined by Microsoft themselves.
Microsoft ran tests for this in Azure, with SQL Server, and used HammerDB to generate an online transaction processing type workload. Not only was Condusiv able to jump through all the hoops necessary to achieve the certification, but it was also able to show an increase of about 30% more SQL transactions in the same amount of time.
In this test, that meant roughly 30% more orders processed.
They probably could have processed more too, if they had allowed DymaxIO a slightly larger RAM cache size.
Bad I/O, Impact on SQL
Most SQL DBAs are familiar with why split pages are bad for performance. If you’ve got room and your UPDATE or INSERT doesn’t require a split, this is pretty fast because SQL is just updating one page and then writing it out to disk, and to the transaction log. But, if the updated or inserted row doesn’t fit, SQL needs to allocate a new page, move about half of the rows to the new page, and then write both pages to disk and to the transaction log. In addition, the pages in all the indexes that point to the data pages need to be updated too.
So, let’s say that your table had one clustered index and four non-clustered indexes, that would mean at least seven pages would need to be updated. You would have one for the clustered index structure, four for the non-clustered indexes, and two in the data pages in the clustered index. This page split would result in a minimum of seven times the I/O as an INSERT or UPDATE that didn’t require a page split.
Each I/O that takes place, takes a measurable amount of time and resource to process, and the same is true when the Windows file system splits data as it writes out to the logical NTFS volume, and that can have significant performance ramifications too.
This is why DBAs format volumes to be used by SQL with a 64 KB cluster size, rather than the default of 4 KB. It helps to avoid split I/O situations when writing to disk, and keeps performance-losing file fragmentation from building up so quickly. It doesn’t fix the problem though.
As your NTFS file systems mature over time, and files are created, extended and deleted, the free space on the NTFS volume will become more and more fragmented into smaller and smaller free space extents, increasing those split I/O situations when writing. Each time your write is split in this way, the data is being sent out to disk in a separate I/O packet, and like split pages in SQL, each I/O takes a measurable amount of time and resource to process.
In the real world, a gigabyte of storage I/O traffic that should be written in 2,000 or 3,000 I/Os, could now be taking 30,000, or 40,000 I/Os to complete. We call the performance penalty that these split I/O situations cause, the “Windows I/O Tax”, as in a tax on your performance due to split I/Os.
In a virtualized environment this performance penalty can be amplified by something called the “I/O Blender Effect”. What’s happening here is that you have small, fractured I/O packets coming from the virtual machines and as they pass through the hypervisor, the hypervisor mixes these I/O streams together, causing a randomization effect. What comes out between the physical host hypervisor and the disk storage controller is now a ‘chaotic mess’ of small, fractured and now very randomized I/O streams, that by the time they hit the storage controller, couldn’t be less storage friendly.
It means that the storage controller is only receiving data in very small packets at a time, so it now only has the opportunity to create very small stripes across its media and that means many more storage level operations, than if the data hadn’t been split as it was being written up at the NTFS layer.
DymaxIO helps Windows avoid or prevent most of the excess, unnecessary performance-losing split I/O traffic and replace it with far fewer storage I/O packets, each one carrying a larger payload of data. This makes the movement of data between server and storage more efficient, much like avoiding page splits in SQL wherever possible, and allows the disk storage to store the data, using far fewer storage-level operations.
DymaxIO will also help create those nice large free space extents, quietly and automatically, in the background, using only otherwise idle compute resources. This makes it easier for the Windows Write Driver to write into.
Think for a moment, of a busy freeway or motorway at rush hour. Imagine that you have lots of cars sitting bumper to bumper, with one person in each car, and none of them are getting anywhere very quickly. Now imagine that each car on the motorway is a storage I/O packet, and each person in each car is your data. What DymaxIO is doing, is taking all of the people out of the cars, and placing them in buses or coaches, and removing all the cars from the motorway, and getting rid of all that congestion, so that now, all the people, (or your data,) can now flow to their destination in a much more efficient manner. I hope that makes sense as an analogy!
But that’s only half the story. DymaxIO also introduces a RAM caching technique into the Windows operating system that further reduces the read I/O traffic that the back-end storage has to deal with. The performance benefits provided are in addition to SQL’s own buffering, and any caching natively provided by the Windows operating system. This is how we could get 30% more SQL transactions done in the same amount of time during that Microsoft I/O Reliability testing that I mentioned before.
Quite simply, SQL could get more work done in the same amount of time, because it wasn’t having to wait so much on the disk storage layer, before being able to get on with its next transaction, and because RAM is faster than disk storage.
Oh! And it absolutely works with both physical AND virtualized SQL servers, and in the cloud too.
Storage I/O reduction, optimization, and RAM caching represent a significant reduction in workload that has to be processed by the back-end storage. This is not only true of SQL Server’s storage traffic, but other workload types too. In this example, let’s say that the first VM is hosting SQL, and the second is hosting the application that interfaces with SQL, and the other two VMs are hosting busy file servers, and they’re all sharing the same back end storage. You might have a situation where the traffic from the file servers are taxing the performance of SQL, by keeping the storage layer busy. So, SQL isn’t necessarily the cause of the disk storage being slow, but it would likely be affected by the increased disk I/O queue depths and latency caused by the busy workloads on the file servers.
My recommendation would be to install the DymaxIO software on ALL Windows machines sharing the same back end storage. That way, from the point of view of the storage, it is now doing the least amount of work, regardless of which VM is generating the most workload at any given time.
Best Practice for Microsoft SQL Servers
At this point, some of you will be thinking:
“Ah! Yes, but SQL is already using most of the available memory in the machine. How can you create a RAM cache?”
It is true that if SQL is left uncapped, there typically wouldn’t be enough RAM for Condusiv’s software to create a cache with. We have intentionally designed our software so that it can’t compete for system resources with anything else that is running, so that we should never be the cause of a memory starvation situation. DymaxIO would only use some of the free RAM that isn’t being used by anything else, and will dynamically size the cache, handing RAM back to Windows if other processes or applications need it. Watch the video on how DymaxIO uses memory.
For best results, you can easily cap the amount of RAM that SQL takes for its own form of caching or buffering. On busy database servers, if you can leave 16 GB of RAM free, you would have a DymaxIO cache size that would be enough to really make that machine’s performance ‘fly’ in most cases. If you can’t spare 16 GB, leave 8 GB. If you can’t afford 8 GB, leave 4 GB free. Even that is enough to make a difference. Smaller than that though, the cache size would really be too small and performance improvements would be limited.
You might also be thinking that SQL’s buffering is good enough, so how does DymaxIO’s caching complement this? At a basic level, SQL’s cache does a pretty good job and whilst that does have a positive impact on performance, DymaxIO’s RAM caching is designed to eliminate the type of storage I/O traffic that tends to slow the storage down the most, and whilst that tends to be the smaller, more random read I/O traffic, it’s not always the case.
So, DymaxIO uses a very lightweight storage filter driver to gather telemetry. This allows the software to learn useful things like:
- What is the main application in use on a machine?
- What type of files are being accessed and what type of storage I/O streams are being generated?
- And, at what times of the day, the week, the month, the quarter?
DymaxIO can be very intelligent about what the ‘hot blocks’ of data are that need to be in the RAM cache, and more importantly, when. It can also use that telemetry to figure out how best to size the storage I/O packets to give the main application the best performance. If the way you use that machine changes over time, DymaxIO can automatically adapt to those changes, without you having to reconfigure or ‘tweak’ any settings.
Validating results is really easy. First of all, you could measure the amount of SQL transactions being done each day to see if they increase. Some customers see reports that they generate take less time to produce, SQL imports take less time, or batch jobs complete more quickly. Some customers have even done a simple stopwatch test to measure record retrieval times.
However, DymaxIO itself comes with very transparent reporting that will show you exactly how much storage I/O traffic it’s able to eliminate from the disk storage layer. You can see what percentage of the reads and writes are being eliminated, and it will show how much storage time is being saved, by the storage not having to process that eliminated I/O.
Other Features and Best Practice
If you are using thin-provisioned storage, DymaxIO can help you reclaim space. If using thin-provisioned VMDK files, it can automatically zero out free space, so you don’t have to wait for hours for an SDELETE pass to complete. If thin provisioning down at the storage layer, it can also send SCSI UNMAP commands to the storage controller to notify it of blocks that are no longer needed.
If your VMs are provisioned with dynamically allocated RAM instead of a fixed amount of RAM, such that the hypervisor can ‘borrow’ some back to reallocate to some other VM, it is important to make sure that you reserve enough RAM in your SQL VMs configuration for both SQL AND DymaxIO. Otherwise, too much RAM in the VM will become ‘driver-locked’ by the hypervisor’s ballooning driver, and that would mean that DymaxIO might not get enough RAM to cache with effectively.
Customer SQL Case Studies
These are just some of the case studies that you can access on our web site at Condusiv.com.
As an example, Bell Mobility, a telecoms company in Canada saw the Condusiv software reduce the storage I/O traffic to their SAN by 61%.
This gave them SQL report queries that were three times faster, with no additional hardware required.
All they had to do was install the software, which as mentioned earlier, is completely non-disruptive. There are no SQL code changes required, no reboots required, and zero disruption to live running workloads.
Next Steps to Boost SQL
Firstly, I would encourage you to share what you’ve learned in this session with colleagues. See what they have to say about this.
The simplest and best way to test DymaxIO is to try it for yourself, with your real-world workloads. Make sure you follow the best practice tips for best results, and ensure you leave at least 4 GB of RAM to cache with.
And by the way, you are very welcome to share the DymaxIO Dashboard Report data with a Condusiv Engineer for additional analysis and get a written engineer’s report.
So, if your organization would benefit from faster storage at a reasonable price, using the storage hardware that you already own, I would urge you to give it a try. You’re not going to disrupt any live running workloads, you don’t have to make any SQL code changes and the whole download and installation will take no more than a few minutes.
Use the DymaxIO software to not only identify those servers that cause storage I/O issues, but fix those issues at the same time.
Originally published October 23rd, 2018. Last updated April 27, 2021.