Improvement after tuning
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 before | Reads after | |
|---|---|---|---|---|---|---|
| Run 1 | 426 | 23 | 1,453 | 0 | 245,028 | 59 |
| Run 2 | 380 | 62 | 1,439 | 0 | 245,028 | 17 |
| Run 3 | 602 | 42 | 2,296 | 15 | 244,995 | 17 |
| Run 4 | 385 | 64 | 1,438 | 16 | 244,993 | 17 |
| AVG | 448 | 47.75 | 1,656 | 7.75 | 245,011 | 27.5 |
Here is the summary:
| Before | After | Improvement | |
|---|---|---|---|
| Duration (ms)* | 448 | 47.75 | 9.4x |
| CPU (ms)* | 1,656 | 7.75 | 214x |
| Disk (number of read operations)* | 245,011 | 27.5 | 8,909x |
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.



