Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server High Performance and Cost Savings in the Cloud with DataCore Software


By:   |   Updated: 2018-06-15   |   Comments   |   Related: More > Database Administration

Problem

We have moved to the public cloud for flexibility to be able to better scale our systems on demand, reduce our investment in hardware and licensing as well as relieve some of the burdens for our day to day management for our technology team.  After working through a long and intensive migration process to the cloud to some of the largest Virtual Machines (VM) available we are facing numerous unexpected issues.  Unfortunately, our management has had some sticker shock with our monthly bills and the technology team is getting pressure to reduce costs while improving application performance.  As a SQL Server DBA with the databases at the core of our application, I have been able to pinpoint I/O as our culprit, but what can I do from here to help our organization?

Solution

The promise of moving to the public cloud for flexibility, cost savings and free time to address new business initiatives is not always the case when migrating and consolidating applications.  Even with the largest of the VMs available, having applications share resources is not always ideal.  Often times the answer is to add more resources to the VM, but with your management already sensitive to the monthly recurring costs, this is probably not the best option.  Couple this with the fact that the user experience has diminished for both internal and external users, the impact to the business is real.

As a SQL Server DBA, we are vigilant when it comes to weekly maintenance, index rebuilds, updating statistics, etc., but I see a couple of options to help the organization with this I/O issue in the cloud:

  • On each VM, split the heavy usage databases on to different disks as well as splitting the transaction logs and TempDB onto different disks.  Unfortunately, I am concerned this could increase our cloud bill.
  • On each VM, we can move to Solid State Disks (SSD), but I know this will be an expensive option each and every month.
  • On each VM, add more CPU core’s, but I am not convinced the CPUs will resolve the I/O issues.  When the servers were on-premises we had mixed results with server upgrades.
  • We can prioritize applications and begin to re-design and re-factor code for the portions that are causing the most pain.  I know in some circumstances we have the source code with team members familiar with the applications and in others we are not so fortunate.  This will take significant resources and take time away from our core business initiatives.
  • Along the same lines, we can hire consultants to work through the performance tuning and development needs in order for our team to focus on core business initiatives, but this will come at a cost.

Although all of these options could be beneficial, I would like to introduce you to MaxParallel software from DataCore which is an emerging technology solution for I/O-intensive workloads on the Windows platform.  MaxParallel for Windows Server can address your SQL Server I/O issues in the public cloud by:

  • Enabling your applications to be more responsive and improve the user experience by improving I/O concurrency and reducing I/O latency
  • Removing serial choke points in the I/O stack by scheduling multiple independent I/O requests in parallel rather than letting the I/O requests queue up behind each other
  • Cost savings by reducing the number of cores in scenarios where processors are idle waiting on I/O
  • Reduce context switching which is an expensive operation and impedes performance
  • Intelligent read-ahead and data pre-fetching based on I/O patterns yielding improved read performance in generally 50 to 80 percent of most SQL Server applications
  • Improve the SQL Server application performance for OLTP, Reporting, BI, OLAP and ETL processes by 20 to 50 percent
  • Reduce the SQL Server full backup time needed with improved read-ahead and parallel I/O operations to reduce contention and streamline critical operational tasks
  • Honor writes and commits to disk from SQL Server without interruption and fully comply with all Windows I/O operations
  • Improve SQL Server I/O performance issues on large scale VM’s in the cloud with no code changes and no further hardware investments (disks, SSDs, CPU, new VMs, etc.) to improve the user experience

How does MaxParallel for Windows Server work?

With the single threaded nature of I/O, I/O is generally the most common performance issue for SQL Server based applications.  MaxParallel for Windows Server optimizes your I/O workload with a simple installation and reboot.  Once completed you can manage enabling and disabling MaxParallel as well as observe the performance improvements of 20 to 50 percent as compared to the original workload as shown with the product dashboard below.

maxparallel dashboard

The MaxParallel dashboard includes the following counters:

  • Total IOPS
  • Average Read Latency (ms)
  • Read Requests / sec
  • Total Throughput
  • Average Write Latency (ms)
  • Write Requests / sec

It is also important to note that the white line on each counter graph is the baseline, the metrics in green are the percentage improvement and the orange metrics are the current raw value for the counter.

Customers on large scale VMs in the public cloud that are suffering from I/O issues generally recognize a 20 to 50 percent performance improvement once the product is installed.  This provides the technology team the opportunity to stop firefighting, take a deep breath and continue to address SQL Server performance issues related to the database design, coding practices, maintenance, etc. that are only going to further improve the application performance and user experience.  In addition, this is also a much more cost-effective solution than the monthly recurring costs related to adding more disks, moving to SSDs, adding more CPU cores or moving to a new VM.

How can MaxParallel for Windows Server help me?

Beyond the performance gains, improved user experience and cost reductions, MaxParallel delivers:

  • Faster SQL Server performance with no code changes
  • Commercial Off The Shelf (COTS) applications such as critical ERP systems with limited tuning opportunities get an immediate performance gain
  • Short Staffed DBA Team \ Junior DBA will get a breath of fresh air to determine the next steps on how to best improve the application and user experience
  • Designed for the Windows operating system, MaxParallel can help applications such as Exchange, IIS, Oracle on Windows, etc. that are suffering from intense I/O workloads

The patented technology that drives MaxParallel has initially been developed and embedded in DataCore’s Software-Defined Storage products. There are more than ten thousand field deployments of these products globally, an indicator for the maturity of the underlying I/O-intensive workload solution.

How can I get started with MaxParallel for Windows Server?

  1. Learn more about MaxParallel and get started with a download.
  2. Check out the customer testimonials.
  3. Checkout the user guide to see how easily you can get started.
  4. Think about all of the challenges you face with achieving a high-performance SQL Server and user experience then communicate with your team and management about how you think MaxParallel will help:
    1. No code changes to implement MaxParallel - simple install and reboot for the performance gain
    2. 20 to 50 percent SQL Server performance gain for instances that are I/O bound
    3. Performance gains for read-intensive SQL Server workloads - OLTP, OLAP, Reporting, ETL and backups
    4. Reduce recurring costs for monthly cloud investments
    5. Beneficial for all I/O intensive Windows Server based applications such as Exchange, IIS, Oracle, etc.
  5. Put MaxParallel through its paces in your environment, share the results with your team and determine your next steps.
Next Steps

MSSQLTips.com Product Spotlight sponsored by Data Core, makers of MaxParallel for Windows Server.



Last Updated: 2018-06-15


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools