SQL Server Performance Tuning

How We Cut a Trigger's Disk Reads by 8,909 Times

Updated September 7, 20263 min read

A trigger’s disk reads, cut 8,909x. Ploom steps on a tripwire that sets a big gear contraption whirring.

Improvement after tuning

8,909xDISK
214xCPU
9.4xDURATION

Reading from disk is the slowest operation SQL Server does.

Therefore, tuning for less disk “reads” is often the primary goal.

Problem

A small SELECT inside a trigger read about 245,000 pages every time it fired. All it had to answer was whether a matching record already existed.

The trigger ran after every insert and update on the table. And each time, that check did a full index scan.

Here’s a glance at the pre-tuning metrics:

  • Duration (ms): 448
  • CPU (ms): 1,656
  • Disk (number of read operations): 245,011

448 ms does not sound like much. But a trigger runs on every insert and every update, so you pay that cost on every write, inside the user's transaction.

Solution

This is what we did to fix the issue: we added one non-clustered index that covers the columns in the trigger's WHERE clause.

The scan became a seek. The database now goes straight to the rows it needs.

Before vs. After

The number of disk reads dropped from 245,011 to about 27.

We ran the statement four times before and four times after the change:

Duration before (ms)Duration after (ms)CPU before (ms)CPU after (ms)Reads beforeReads after
Run 1426231,4530245,02859
Run 2380621,4390245,02817
Run 3602422,29615244,99517
Run 4385641,43816244,99317
AVG44847.751,6567.75245,01127.5

Here is the summary:

BeforeAfterImprovement
Duration (ms)*44847.759.4x
CPU (ms)*1,6567.75214x
Disk (number of read operations)*245,01127.58,909x
*The numbers are an average of multiple T-SQL runs, captured with SQL Server Profiler, Microsoft's tracing tool.

Reads fell 8,909x and the statement ran 9.4x faster.

We lead with reads because that is what the fix removed. The speed gain follows from it.

Final thoughts

Trigger cost often goes unnoticed. Nobody calls a trigger directly, so it rarely shows up when people look for slow stored procedures.

So if your inserts or updates feel slow and the statement itself looks fine, check what the trigger behind it is doing.

One index turned a 245,000-page scan into a seek that reads about 27 pages, on every write to that table.

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜