Case Studies
Massive 30% Improvement in Application Performance After SQL Server Performance Tuning Project
One of our health insurance customers was in need of improved database performance and scalability after a recent upgrade. We worked with them to complete an audit and performance tuning project that resolved immediate issues and set them up for the future.
Technologies Supported
- SQL Server
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
Discovery
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.
Evaluation
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.
Implementation
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
Download the Ultimate SQL Server Performance Checklist
This checklist covers all of the exact areas we reviewed during our SQL Server performance tuning efforts with this customer.
“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 Outcome
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
The Conclusion
An Efficient Database System Ready for Growth
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.
Let’s Make Your Databases Work for You
Whether you just upgraded or you’ve been running on your environment for years, a comprehensive SQL Server audit and performance tuning engagement can have a dramatic positive impact on every user in your organization.
Get more from your databases with SQL Server performance tuning.