Skip to Content

Case Study

30% Improvement with SQL Server Performance Tuning
Technologies Supported: SQL Server

A recent upgrade left this health insurer’s database underperforming when it mattered most. IncWorx audited the environment and delivered a multi-phased performance tuning effort that fixed the immediate problems and set the platform up for the future.

The Challenge

A Need for Performance and Scalability following  In-Place Upgrade

After a recent in-place SQL Server upgrade, a large health insurance provider reached out to us searching for some help with a variety of performance and scalability challenges in their environment.

The customer’s internal team, who were not deeply experienced with completing upgrades had successfully upgraded their 8TB dataset. However, because they do not work on this type of project regularly, they were unfamiliar with some of the complications of completing an in-place upgrade.

Their core claims processing system is extremely data-intensive and includes tables as large as 2 billion records. After their upgrade, they continued to deal with issues. Most of them seemed to stem from poor query plan construction. This resulted in spiking processor utilization and very slow throughput.

The team reached out to our SQL Server consultants with the following goals:

  • Conduct performance tuning to improve system performance
  • Execute query optimization and index optimization analysis
  • Increase system throughput
  • Eliminate spikes in process utilization

The Solution

Execute a Multi-Phased SQL Server Performance Tuning Effort

After several discovery sessions, we agreed upon a two-phase approach for tackling the performance issues. The first would allow us to get a firm grasp on what was causing the performance issues and phase two would be an opportunity to fix the issues.

Over the course of two weeks, we conducted a comprehensive SQL Server Performance Audit.
The audit took a deep dive into best practices including hardware, configuration, maintenance plan and disaster recovery.

We also completed performance evaluations in 6 distinct areas. Key areas of valuation include index optimization, query optimization and conducting a performance evaluation.

At the end of the evaluation, we provided our customer with a report outlining not only our findings but recommendations for how to address each of the areas of concern that we had identified.

Following a review and sign-off by the customer’s internal team, we moved to the second phase of the project. We started out with high-priority findings, focusing on those that would have the biggest positive impact on the customer’s claims processing system.

This approach allowed us to work through each focus area including addressing:

  • Failing index maintenance job
  • Long running queries
  • 442 potentially missing indexes
  • 48 potentially superfluous indexes
  • 101 potentially duplicate/overlapping indexes
  • Poorly written stored procedures

“I’ve been doing business with IncWorx for 10+ years. Whether project-based or on-going support, they have done an excellent job at a very reasonable rate. They are responsive, have the right lever of expertise, and easy to work with.”

-Yuri

The Results

30% Improvement in Application Performance

After all SQL Server performance tuning was completed, the customer saw a 30% improvement in application performance. These improvements lead to a significant reduction in system resources which eliminated the need for our customer to purchase additional hardware and licensing.

Through performance tuning, we were able to:

  • Fix the index maintenance job so that it completes properly
  • Eliminated superfluous and duplicate indexes
  • Reduced the runtime of long-running queries
  • Rewrote stored procedures to make them more efficient

It often seems more cost-efficient to complete projects with an internal team. However, unknowns and inexperience can lead to a situation where a system is not optimized to run efficiently.

Working with a SQL consultant experienced in SQL Server upgrades provides years of knowledge and experience without first-time learning.

This experience provided our customer with a highly optimized database environment that meets the current throughput needs of the organization and provides growth opportunities for the future.

The Microsoft technology stack provides the infrastructure and software necessary to maintain and grow your business. Combining Azure, SQL Server databases and SQL Server Integration Services packages provided this customer with a secure and robust data processing platform that will ensure the growth of their business for years to come. Do not delay your opportunity to get more from your data with ETL development.

ETL Development Leads to Efficiencies for This Food Distributor

Read

Migration Playbook Key To Online Retailers SQL Server Upgrade

Read

Technologies

Let's Build a Data Platform That Works for You

An upgrade shouldn’t slow you down. Get a performance audit and tuning plan built around your actual usage, not guesswork.

Let's Get Started