SQL SERVER – Measure Index Performance

I measure index performance against the same workload before and after a change. A customer wanted to assess a new index.

-- On a representative controlled query, before and after the proposed index:
SET STATISTICS IO,TIME ON;
SELECT ProductID,Name FROM Production.Product WHERE ProductID BETWEEN 1 AND 100;
SET STATISTICS IO,TIME OFF;

STATISTICS IO and TIME report reads, CPU and elapsed timing in Messages. Keep parameters, correctness, concurrency and conditions comparable. Record actual plans. Repeat enough to understand variation.

Warmer caches, changed compilation and concurrent activity can affect a run. Don’t clear shared production caches to manufacture equality. Test representative parameter sets. Retain the exact index definition.

Include modifications, storage, logging and maintenance. One improved SELECT can accompany a worse overall workload. My four-to-five-index preference was a review heuristic. It was not an engine limit.

The videos explain the measurement tools. This sample demonstrates collection without creating an index. It claims no measured speed improvement.

Related reading

• Comprehensive Database Performance Health Check
• SQL in Sixty Seconds series
• Twitter

Original video

A faster individual run is not proof of an index benefit, it is one measurement to compare under controlled conditions.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.