Introduction

Adding an index is often one of the first things we check when a SQL Server query is running slowly. However, simply having an index does not mean SQL Server can always use it efficiently.

A query may still read far more rows than it returns if a function is applied to an indexed column or if SQL Server has to perform an implicit data type conversion. In other cases, SQL Server may use an Index Seek but still do extra work through repeated Key Lookups.

I will use a sample CustomerOrders table to demonstrate three situations where an index already exists but the query still performs more work than expected. I will compare the queries using the Actual Execution Plan and SET STATISTICS IO ON to show what changes in each case.

Setup

To keep the examples consistent, I used one CustomerOrders table throughout the article. It contains 500,000 fictional orders across 10,000 customers, together with an order date, customer code, order status, and a few additional columns used later in the tests.

The following script creates the table:

CREATE TABLE dbo.CustomerOrders
(
    OrderID             int IDENTITY(1,1) NOT NULL,
    CustomerID          int NOT NULL,
    LegacyCustomerCode  varchar(6) NOT NULL,
    OrderDate           datetime2(0) NOT NULL,
    OrderStatus         varchar(20) NOT NULL,
    TotalAmount         decimal(12,2) NOT NULL,
    ShippingCity        varchar(30) NOT NULL,
    OrderNotes          nvarchar(150) NULL,

    CONSTRAINT PK_CustomerOrders
        PRIMARY KEY CLUSTERED (OrderID)
);

I then generated 500,000 rows with order dates ranging from 2017 through 2026. Customer codes are stored as six-character text values such as 000123, which will be useful in the implicit conversion example.

;WITH Numbers AS
(
    SELECT TOP (500000)
        CAST(
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
            AS int
        ) AS n
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b
)
INSERT INTO dbo.CustomerOrders
(
    CustomerID,
    LegacyCustomerCode,
    OrderDate,
    OrderStatus,
    TotalAmount,
    ShippingCity,
    OrderNotes
)
SELECT
    (n % 10000) + 1,

    RIGHT(
        '000000' +
        CONVERT(varchar(6), (n % 10000) + 1),
        6
    ),

    DATEADD
    (
        minute,
        n % 1440,
        DATEADD
        (
            day,
            n % 3652,
            CONVERT(datetime2(0), '20170101')
        )
    ),

    CASE
        WHEN n % 200 = 0 THEN 'Backorder'
        WHEN n % 10 = 0  THEN 'Pending'
        ELSE 'Completed'
    END,

    CONVERT
    (
        decimal(12,2),
        10.00 + ((n % 25000) / 10.0)
    ),

    CASE n % 8
        WHEN 0 THEN 'London'
        WHEN 1 THEN 'Manchester'
        WHEN 2 THEN 'Birmingham'
        WHEN 3 THEN 'Leeds'
        WHEN 4 THEN 'Liverpool'
        WHEN 5 THEN 'Bristol'
        WHEN 6 THEN 'Sheffield'
        ELSE 'Glasgow'
    END,

    CONCAT(
        N'Order note ',
        n,
        N' - ',
        REPLICATE(N'X', 80)
    )
FROM Numbers;

Before the troubleshooting begins, I created three nonclustered indexes:

CREATE INDEX IX_CustomerOrders_OrderDate
ON dbo.CustomerOrders (OrderDate);

CREATE INDEX IX_CustomerOrders_LegacyCustomerCode
ON dbo.CustomerOrders (LegacyCustomerCode);

CREATE INDEX IX_CustomerOrders_OrderStatus
ON dbo.CustomerOrders (OrderStatus);

Each example therefore starts with an index already in place. The point is to see why a query can still do more work than expected even when an index exists.

The sample data contains 2,500 Backorder rows, 47,500 Pending rows, and 450,000 Completed rows. The smaller Backorder set will also be useful in the Key Lookup example later.

With the test data ready, let's look at some problem queries.

Problem 1: Using a Function on an Indexed Column

The first example looks at a function applied to the indexed OrderDate column. Suppose I want to count the orders placed during 2024. Since OrderDate already has a nonclustered index, it may seem that SQL Server should be able to use it efficiently. The first query uses YEAR():

SET STATISTICS IO ON;

SELECT COUNT(*) AS OrdersIn2024
FROM dbo.CustomerOrders
WHERE YEAR(OrderDate) = 2024;

SET STATISTICS IO OFF;

The query returned 50,142 rows, but the Actual Execution Plan showed an Index Scan on IX_CustomerOrders_OrderDate. The operator properties showed that SQL Server returned 50,142 rows but read 500,000 rows from the index. STATISTICS IO also reported 1,000 logical reads. The issue is the function applied to the indexed column:

WHERE YEAR(OrderDate) = 2024;

SQL Server cannot use this predicate as a direct date range. It has to evaluate the year from the indexed values and check which rows match.

The Fix? Rewrite the query. The same requirement can be written without applying a function to OrderDate:

SET STATISTICS IO ON;

SELECT COUNT(*) AS OrdersIn2024
FROM dbo.CustomerOrders
WHERE OrderDate >= '20240101'
  AND OrderDate <  '20250101';

SET STATISTICS IO OFF;

This query also returned 50,142 rows, but the execution plan changed to an Index Seek. SQL Server read 50,142 rows, and the logical reads dropped from 1,000 to 105.

The important point is not that every Index Scan is bad or every Index Seek is good. In this test, the useful difference is the amount of work SQL Server performed. The first query read all 500,000 index rows, while the range predicate allowed SQL Server to go directly to the rows required for 2024.

The range version is SARGable, which means SQL Server can use the predicate as an index search condition.

Problem 2: An implicit conversion on an indexed column

The next example shows another case where an index exists, but a data type mismatch prevents SQL Server from using it efficiently. This second example uses LegacyCustomerCode, which stores values such as 000123 as varchar(6) and already has a nonclustered index.

Suppose an application passes the customer code as a number instead of text:

SET STATISTICS IO ON;

SELECT COUNT(*) AS MatchingOrders
FROM dbo.CustomerOrders
WHERE LegacyCustomerCode = 123;

SET STATISTICS IO OFF;

The query returned 50 rows, but the execution plan showed an Index Scan on IX_CustomerOrders_LegacyCustomerCode. The operator properties also showed this predicate:

CONVERT_IMPLICIT(int, LegacyCustomerCode, 0)

LegacyCustomerCode is varchar, while 123 is an int. Because int has higher data type precedence, SQL Server converts the column values before making the comparison.

Since the conversion is applied to the indexed column, SQL Server cannot simply seek to the matching text value. In this test, it read 500,000 rows to return only 50, and STATISTICS IO reported 1,251 logical reads.

The Fix? Match the data type used by the column. The same lookup can be written using a character value:

SET STATISTICS IO ON;

SELECT COUNT(*) AS MatchingOrders
FROM dbo.CustomerOrders
WHERE LegacyCustomerCode = '000123';

SET STATISTICS IO OFF;

This version also returned 50 rows, but the plan changed to an Index Seek. SQL Server read only 50 rows, and the logical reads dropped to 3.

The index existed in both cases. The difference was the data type used in the comparison.

In a real application, this kind of mismatch may come from a parameter, variable, stored procedure, or application code rather than a hard-coded value. If an indexed lookup unexpectedly scans, checking the data types on both sides of the comparison is a useful place to start.

There is also another risk. The sample values are all numeric-looking text, so converting them to int succeeds. If the column contained values that could not be converted, the query could fail with a conversion error instead of simply producing a less efficient plan.

Problem 3: When an Index Seek Still Leads to Expensive Key Lookups

Here SQL Server already uses an Index Seek, but the query still performs more work than expected. This query returns the 2,500 Backorder rows:

SET STATISTICS IO ON;

SELECT
    OrderID,
    OrderDate,
    TotalAmount,
    ShippingCity
FROM dbo.CustomerOrders
WHERE OrderStatus = 'Backorder';

SET STATISTICS IO OFF;

OrderStatus already has a nonclustered index, so the execution plan shows an Index Seek on IX_CustomerOrders_OrderStatus. However, the index does not contain OrderDate, TotalAmount, or ShippingCity. SQL Server therefore performs a Key Lookup against the clustered index to retrieve those columns for each matching row. In this test, the Key Lookup executed 2,500 times, and the query required 7,676 logical reads.

For this query, I created a covering index that includes the additional columns:

CREATE INDEX IX_CustomerOrders_OrderStatus_Covering
ON dbo.CustomerOrders (OrderStatus)
INCLUDE
(
    OrderDate,
    TotalAmount,
    ShippingCity
);

OrderID does not need to be added to the INCLUDE list because it is the clustered key and is already carried with the nonclustered index rows.

After creating the index, I ran the same query again. It still returned 2,500 rows, but the Key Lookup disappeared from the execution plan. SQL Server could satisfy the query from the nonclustered index, and the logical reads dropped from 7,676 to 18.

This example shows why an Index Seek does not automatically mean a query is efficient. The rest of the execution plan still matters.

It is also important not to add INCLUDE columns to every index just to remove Key Lookups. Wider indexes use more storage and add overhead to data changes, so a covering index should be added only when the benefit justifies that cost.

What to Check When an Indexed Query Is Still Slow

When a query already has an index but still performs poorly, I usually start with the Actual Execution Plan and SET STATISTICS IO ON.

A few things are worth checking:

• Is SQL Server reading far more rows than the query returns?
• Is a function being applied to an indexed column?
• Is there an implicit conversion in the predicate?
• Does the plan contain repeated Key Lookups?
• Is the index being used in a way that matches the query?

The main point is not simply whether the plan shows an Index Seek or an Index Scan. Rows read, logical reads, and the other operators in the plan often give a much better picture of where the work is happening.

Conclusion

Having an index does not always mean SQL Server can use it efficiently for every query.

In the first example, applying YEAR() to an indexed date column caused SQL Server to read all 500,000 index rows. Rewriting the filter as a date range reduced that work. In the second example, an implicit conversion on LegacyCustomerCode caused a scan instead of an efficient seek. The third example showed that even an Index Seek can still be expensive when repeated Key Lookups are involved.

The main lesson is to look beyond whether an index simply exists. The Actual Execution Plan, rows read, logical reads, and other operators in the plan can show where the real work is happening.

Small query or index changes can make a large difference, but they should always be tested against the actual workload before being applied in production.