Turning an 89ms Query into 0ms: Real‑World SQL Server Index Optimization

Overview

The author discovered a severe performance issue while tuning a component of a Windows service that could handle only around 12 events per second. The root cause was identified as a poorly designed SQL query lacking suitable indexes.

Environment

The service runs on a machine with an i7‑2670 CPU, 16 GB RAM, and SSD storage, using SQL Server 2012. The relevant table contains roughly 300,000 rows, has only a primary‑key index, and no other indexes.

Problem: Slow query without indexes

A simple query is executed repeatedly:

SELECT TOP 1 *
FROM SMS_SHORTNO_ASSIGN
WHERE APP_CODE = 'SMSNotice'
  AND IS_DYNAMIC_ASSIGN = 'N'
  AND SMS_TYPE_CODE = 'Mas'

When IS_DYNAMIC_ASSIGN = 'N' returns no rows, the query takes about 89 ms; when it returns rows, it is much faster. The execution plan shows a full table scan because no supporting indexes exist.

Adding a non‑clustered index

The first attempt was to create a non‑clustered index on the columns used in the WHERE clause.

After the index was created, the new execution plan still showed a table scan, because the query used SELECT *, which prevents index coverage.

Switching to specific columns

Changing the query to select only the indexed columns allowed the optimizer to use the index, dramatically reducing execution time.

Creating a clustered index

Because a table can have only one clustered index, the author added a clustered index on two high‑frequency columns that appear in the WHERE clause (neither of which is the primary key).

After this change, the query executed in virtually 0 ms.

Additional observations

While the clustered index solved the specific query, the author notes that non‑clustered indexes remain essential for other workloads. Removing the previously created non‑clustered index and re‑adding it showed a noticeable performance difference for count queries.

The author also experimented with different COUNT syntaxes (COUNT(*), COUNT(1), COUNT(primary_key)) and observed negligible differences.

Open question: LEFT JOIN performance

Another query using LEFT JOIN and IS NULL to find unlocked records exhibited poor performance (≈26 ms). The optimizer could not use an index because the IS NULL predicate prevents index usage. The author asks whether rewriting the query to use INNER JOIN with an indexed column or avoiding IS NULL would improve speed.

These observations highlight the importance of designing schemas and queries that allow the optimizer to leverage indexes effectively.