SQL Optimization: How to Achieve 2X Faster MS-SQL Applications

By following the best practices outlined here for SQL optimization, we can virtually guarantee a 2X or faster boost in your MS-SQL performance with DymaxIO*, our I/O optimization software.

The secret sauce to 2X faster SQL is to install DymaxIO and follow these best practices. If you don’t have the DymaxIO software you can purchase a subscription or you can start with a free 30-day subscription.

1) Don’t just run our I/O optimization software on the SQL Server instances but also on the application servers that run on top of MS-SQL

– It’s not just SQL performance that needs improvement, but the associated application servers that communicate with SQL. Our software will eliminate a minimum of 30-40% of the I/O traffic from those systems.

2) Run our I/O optimization software on all the non-SQL systems on the same host/hypervisor

– Sometimes a customer is only concerned with improving their SQL performance, so they only install our I/O optimization software on the SQL Server instances. Keep in mind, the other VMs on the same host/hypervisor are interfering with the performance of your SQL instances due to chatty I/O that is contending for the same network and storage resources. Our software eliminates a minimum of 30-40% of the I/O traffic from those systems that is completely unnecessary, so they don’t interfere with your SQL performance.

– Any customer that is on the host or site license pricing model is able to deploy the software to an unlimited number of guest machines on the same host. If you are on per system pricing, consider migrating to a host model if your VM density is 7 or greater.

3) Cap MS-SQL memory usage, leaving at least 8GB left over

– Perhaps the largest SQL inefficiency is related to how it uses memory. SQL is a memory hog. It takes everything you give it then does very little with it to actually boost performance, which is why customers see such big SQL optimization gains with our software when memory has been tuned properly. If SQL is left uncapped, our software will not see any memory available to be used for cache, so only our write optimization engine will be in effect. Moreover, most DB admins cap SQL, leaving 4GB for the OS to use according to Microsoft’s own best practice.

– However, when using our software, it is best to begin by capping SQL a little more aggressively by leaving 8GB. That will give plenty to the OS, and whatever is leftover as idle will be dynamically leveraged by our software for cache. If 4GB is available to be used as cache by our software, we commonly see customers achieve 50% cache hit rates. It doesn’t take much capacity for our software to drive big gains.

4) Consider adding more memory to the SQL Server

– For SQL optimization, some customers will add more memory then limit SQL memory usage to what it was using originally, which leaves the extra RAM left over for our software to use as cache. This also alleviates concerns about capping SQL aggressively if you feel that it may result in the application being memory starved. The very best place to start is by adding 16GB of RAM then monitor the dashboard to see the impact. The software can use up to 128GB of DRAM. Those customers who are generous in this approach on read-heavy applications get into otherworldly kind of gains far beyond 2X with >90% of I/O served from DRAM. Remember, DRAM is 15X faster than SSD and sits next to the CPU.

5) Monitor the dashboard for a 50% reduction in I/O traffic to storage

– When the DymaxIO dashboard shows a 50% or more reduction in I/O to storage, that’s when you know you have properly tuned your system to be in the range of 2X faster gains to the user, barring any network congestion issues or delivery issues.

– As much as capping SQL at 8GB may be a good place to start, it may not always get you to the desired 50% I/O reduction number. Monitor the dashboard to see how much I/O is being offloaded and simply tweak memory usage by capping SQL a little more aggressively. If you feel you may be memory constrained already, then add a little more memory, so you can cap more aggressively. For every 1-2GB of memory added, another 10-25% of read traffic may be offloaded.

To achieve ideal SQL optimization with 2X faster SQL, tune DymaxIO and SQL as outlined in these best practices. If you don’t have the DymaxIO software and need faster SQL performance, purchase a subscription or start with a free 30-day subscription. Just remember to install DymaxIO on all VMs on the same host!

 

* These same best practices apply if you are running V-locity I/O reduction software or Diskeeper performance software in your Windows environment.