[Case Study] Tuning SQL Stored Procedure Performance to run 354 times faster! How did we do it?

Here what can be done during a session of SQL performance tuning.

Problem summary

Slow store procedure. This query was running for a long time, causing a blocking chain and High CPU usage.

What was done

Added these two new indexes:

Total improvement

As expected, the same output (result set) was presented by the original and modified query.

The first spreadsheet shows the original version of the store procedure (red). The next is the version tuned by red9 (green).

Tuning results vary. They vary based on the complexity of the query, how much tuning has gone into it already, etc. But we rarely can’t make T-SQL perform faster.

Technical background

Indexes operate “behind the scenes”: adding and removing indexes rarely changes an application’s code.

The SQL Server engine uses an index, as a reader uses a book index. Without it, the reader needs to look at every page in a book to find a word.

Indexes are the method by which SQL Server sorts and organizes the rows in tables. It allows you to quickly narrow in on the rows to satisfy the query and avoid scanning every row in the table.

In database terms, a table scan (or just scan) happens when there is no index available to help a query. They have a terrific impact on performance on large tables, but sometimes are inevitable.

The proper index can drastically increase the performance of an application. However, there are tradeoffs to almost any feature, and indexes are no exception.

Learn how to improve your SQL Server performance using Indexes

See more SQL Server Performance Optimization examples here.

We tune slow SQL Servers every day. For over a DECADE now. Contact us!

Picture of Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *