Case study – 9,320x faster!

Description:

Problem: Select command inside trigger (EHRHist<removed>_AFTER_INSERT_UPDATE) doing an index scan.
Solution: Added an index.

Command:

Change:

Before:

After:

Technical Background:

Most SQL Servers bottleneck is 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) much 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), in any case.

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.

Check out more case studies here!

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

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