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

Description:

Problem: Deadlocking. This stored procedure is continuously involved in deadlocking.

Solution: A new index. Scan operation can be tuned into becoming a seek (a lot more efficient).

The total improvement looks like this:

Technical Background:

Most SQL Servers bottleneck on Disk access (or disk “reads”).

It’s not CPU or RAM – which most customers often suspect first.

And that makes much sense. Here is why.

Inefficient queries scan (or read) a lot of data. Data read in is stored in RAM. As more data is read in, “older” data is pushed out from RAM. If there isn’t enough RAM to keep ALL data in memory (which is often not possible), SQL Server has to read from disk – and that is the slowest operation SQL Server can do.

When the query can be tuned to read 10 rows vs. 10M – less CPU and RAM automatically are necessary. Therefore, tuning for less disk “reads” is often the primary goal.

To the end-user nothing is more important than Speed (or Duration of the query), though.

Tuning to reduce CPU/RAM resources is helpful too.

When queries are tuned to need less CPU & RAM, it means that the same server now has more capacity. Which means that the same server can process double or triple the load. Which means it extends the lifespan of the same server. Which means hardware upgrades can be pushed out further into the future.

See more SQL Server Performance Optimization examples here.

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

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 *