How Red9 Saved 66+ Minutes on SQL Server Daily

SUMMARY: Our client achieved exceptional SQL Server performance transformation through Red9's systematic three-phase optimization approach. Within six weeks of onboarding, CPU utilization dropped dramatically through strategic health check implementation, performance tuning, and Azure infrastructure optimization. The result: a high-performance database environment operating at just 6.42% SQL usage while processing over 120 million monthly transactions.

The Problem

Their SQL Server was having issues. CPU usage was all over the place, queries were slow, and they were running on the wrong Azure instance type. Their database was not performing at the level they needed it to.

What We Did

Week 2: Fixed the Basics

We started with our SQL Server health check. Found a bunch of configuration problems that needed fixing and applied best practices. The moment we applied our recommendations on June 25th, you could see CPU usage start dropping on the performance graphs.

Week 4: Performance Tuning

July 17th was our main tuning session. We used SQL Server’s Query Store to find the worst-performing queries and created strategic indexes to fix them.

Performance tuning is complex, especially index design; knowing which columns to include, order, and optimizer behavior requires experience. The challenge isn’t just creating an index – it’s knowing which columns to include, in what order, and understanding how SQL Server’s query optimizer will use it. When you have deep knowledge of how this works, then yes, it becomes straightforward. But getting to that point takes years of experience.

Week 6: Infrastructure Fix

They were running on a generic D-series Azure instance type, which isn’t bad, but we could do better. After implementing best practices and performance tuning, we recommended switching to E4ads_v5, which is recommended for SQL Server workloads. The optimization work we’d done meant we could actually cut the instance size in half while getting better performance.

When they made the switch at the end of July, CPU usage dropped even more.

The Results

Here’s where they ended up:

SQL Usage: 6.42% (tons of headroom for growth)
120,869,229 transactions processed in July without breaking a sweat
57GB database with 45GB of space available
Only 11 deadlocks all month (that’s really good)
1,773 errors, mostly from application issues, not the database itself
445 failed SQL jobs out of 64,201 total runs
41 million rows are being processed monthly

Error Breakdown:

362 errors: SQL Agent profile validation issues
65 errors: JonasNET application unclosed quotation marks
26 errors: Number parsing problems with large values
18 errors: Numbers out of range for numeric data types
16 errors: Incorrect syntax issues

Most expensive queries were all JonasNET application calls, including operations like:

Client demographic updates
Member integration lookups
Charge code allocation processing
System integration queries

What We Actually Optimized

From the transcript discussion, here are some of the specific performance improvements we achieved during the July 17th tuning session:

Query Performance Examples:

One query went from 23 milliseconds down to 3.5 milliseconds
Another query improved from 400 milliseconds to under 300 milliseconds
A third query showed massive disk I/O improvements (from ~5,000 pages to 1,000 pages per execution)
Some queries saw “almost 1000 times faster” performance improvements

What We Focus On: Every performance issue comes down to three key metrics:

Duration – how long users wait for results
CPU usage – how many processing cycles each query consumes
Disk I/O – how many disk operations are needed to fetch data

Disk operations are the slowest part of any SQL Server system. Even with SSDs, excessive logical reads and inefficient plans can cause performance issues, especially when they trigger unnecessary I/O. It’s like searching through every page of a phone book instead of using the index.

How We Do It

We use SQL Server’s built-in Query Store to see exactly which queries are causing problems. No guesswork, just data.

Many performance problems can be resolved with the right indexes, especially when queries are otherwise well-written. However, some scenarios require query optimization, schema changes, or application code adjustments.

Scientific approach: “Every change gets measured”.

We capture before and after metrics, and if something makes performance worse, we roll it back immediately.

Why This Matters

This client deals with athletes and performance. Their systems needed to match that.

Their database now handles 120+ million transactions monthly while barely using any resources. That’s the kind of efficiency every organization should have.

Common SQL Server Problems We See

Wrong Configuration. Most SQL Servers run with default settings unsuited to their workload — for example, default MAXDOP, Cost Threshold for Parallelism, TempDB configuration, or memory limits. It’s like running a race car with flat tires.

Data Type Mismatches. Applications often use NVARCHAR(MAX) for everything while the database uses specific types like VARCHAR(100). This forces SQL Server to do expensive conversions on every query.

Missing Indexes. Queries scan entire tables instead of using indexes. It’s like looking through every page of a phone book instead of using the index.

Wrong Hardware. Running SQL Server on general-purpose cloud instances instead of database-optimized ones. The specs might look similar, but performance is completely different.

What to Look For

Your SQL Server might need help if:

Queries are getting slower over time
CPU usage spikes for no clear reason
Users complain about system responsiveness
You keep needing bigger servers to handle the same workload

How We Approach Performance Tuning

Step 1: Health Check. We examine 110 different aspects of your SQL Server setup. Most problems are in the configuration, not the queries.

Step 2: Find the Bottlenecks. Using Query Store, we identify which queries consume the most resources.

Step 3: Strategic Optimization. We create indexes and adjust settings to fix the biggest problems first.

Step 4: Measure Results. Every change gets measured with before/after metrics. No guessing.

Step 5: Ongoing Monitoring. We set up monitoring to catch new problems before they impact users.

Why Index Creation Works

Creating the right index can make a query run 100x faster. Here’s why:

Instead of scanning every row in a table (like reading every page of a book), SQL Server can jump directly to the data it needs (like using a book’s index).

The challenge is knowing which indexes to create. Create the wrong ones and you waste space and slow down inserts. Create the right ones and everything runs faster.

After 15+ years of doing this, we’ve learned which indexes actually matter.

The Technical Details

Query Store Integration: SQL Server’s Query Store tracks every query’s performance over time. We use this data to identify problems and measure improvements.

Automated Rollback: If any change makes performance worse, our monitoring detects it and we roll back the change promptly, either automatically or manually, depending on the nature of the modification.

Non-Intrusive Methods: All our analysis happens without impacting your production workload. Most optimizations can be done during business hours.

Real-World Impact

For this client, better database performance means:

Faster response times for staff and members
More capacity to handle growth without new hardware
Lower cloud costs from running efficiently
Reduced risk of performance problems during peak usage

Frequently Asked Questions

Will this affect our production system?

Our methods are specifically designed to be non-intrusive and safe for production environments. We use Query Store analysis, which has minimal impact on performance, and when we create indexes, we use SQL Server’s online index operations (Enterprise Edition feature) whenever supported to minimize blocking. Some operations may still cause short schema modification locks at the start and end of the process. Every change we make includes comprehensive rollback procedures – we can reverse any modification within minutes if needed. We aim to do analysis during business hours with minimal user impact, but in high-volume or latency-sensitive environments, certain changes may still need off-peak scheduling. This client experienced zero downtime during their entire 6-week optimization process.

How long does optimization take?

You’ll see improvements at different stages. Configuration changes show up immediately – literally within hours of implementation. The client saw CPU usage drop on June 25th the moment our best practices took effect. Performance tuning results appear within 24-48 hours as queries start using the new indexes we create. The complete optimization process typically takes 4-6 weeks, but you’re getting benefits throughout that entire period, not just at the end.

What if our application is custom-built?

Custom applications often benefit more from optimization than off-the-shelf software because they haven’t been professionally tuned before. We don’t make assumptions about how your application works – instead, we analyze the actual queries your application generates using SQL Server’s Query Store. This shows us exactly which queries run most frequently, consume the most resources, and would benefit most from optimization. Whether you’re running SAP, a custom .NET application, or something built in-house 10 years ago, the approach is the same: analyze actual usage patterns and optimize accordingly.

How do you know which indexes to create?

This is where experience really matters. We use SQL Server’s Query Store to see exactly which queries are running, how often they execute, and what resources they consume. But the data is only half the story – knowing which indexes will actually help requires understanding how SQL Server’s query optimizer works. For example, creating an index on the wrong column can make performance worse, not better. After 15+ years of doing this across hundreds of environments, we’ve learned which index patterns deliver real improvements and which ones just waste space.

What if performance gets worse after optimization?

Every single change we make includes automated rollback capabilities. We capture performance baselines before making any modifications, then monitor key metrics continuously after implementation. If CPU usage goes up, query execution time increases, or any other performance indicator gets worse, our monitoring system detects it and we reverse the change immediately. We’ve had cases where an index that should have helped actually made things worse due to unique data patterns – in those situations, we delete the index and try a different approach.

Do you work with cloud and on-premises SQL Server?

Yes, our methodology works across all SQL Server deployment types. We’ve optimized SQL Server running on physical hardware, VMware, Hyper-V, Azure VMs, Azure SQL Database, and Azure SQL Managed Instance. Cloud environments often provide additional optimization opportunities – like the client’s switch to E4ads_v5 instances – but the core database optimization techniques are the same regardless of where SQL Server is running.

How much does this typically cost and what’s the ROI?

Performance tuning is usually purchased through consulting hours, and the investment typically pays for itself through improved efficiency and reduced infrastructure needs. For example, if optimization lets you delay a hardware upgrade for a year, that’s often 10x the cost of the tuning work. Cloud environments see even faster ROI because you can immediately resize instances after optimization. The client’s switch to the right Azure instance type alone probably saves them thousands monthly while delivering better performance.

What ongoing support do you provide after initial optimization?

We offer several options depending on your needs. Some clients prefer one-time optimization with documentation so their team can maintain the improvements. Others purchase periodic consulting hours for quarterly or annual tune-ups as their workload changes. Many choose our managed services, which include continuous monitoring, monthly performance reviews, and proactive optimization as new bottlenecks emerge. The client gets monthly reports showing performance trends and any new optimization opportunities we identify.

Conclusion

SQL Server performance problems are usually fixable.

The client went from performance issues to processing 120+ million transactions monthly at 6.42% resource usage.

Most organizations are running SQL Server way below its potential. With the right approach, you can get dramatic performance improvements without changing your application or buying new hardware.

The key is systematic analysis, strategic optimization, and scientific measurement.

Guess and check doesn’t work with database performance.

If your SQL Server is slow, there’s probably a reason. And that reason is usually fixable.

Speak with a SQL Expert

In just 30 minutes, we will show you how we can eliminate your SQL Server headaches and provide  operational peace of mind

Schedule My Call Now ➜

Article by

Saulius Baskevicius

Hey, I’m Saulius, part of the team behind Red9. SQL Server is my thing. Complex challenges - my passion.

Case Study: How Red9 Transformed SQL Server Performance and Saved 66+ Minutes of Daily Processing in 6 Weeks