Why would SQL Server ignore an index?

Question

I have a table, CustPassMaster with 16 columns in it, one of which is CustNum varchar(8), and I created an index IX_dbo_CustPassMaster_CustNum. When I run my SELECT statement:

SELECT * FROM dbo.CustPassMaster WHERE CustNum = '12345678'

It ignores the index completely. This confuses me as I have another table CustDataMaster with way more columns (55), one of which is CustNum varchar(8). I created an index on this column (IX_dbo_CustDataMaster_CustNum) in this table, and use practically the same query:

SELECT * FROM dbo.CustDataMaster WHERE CustNum = '12345678'

And it uses the index I created.

Is there any specific reasoning behind this? Why would it use the index from CustDataMaster, but not the one from CustPassMaster? Is it due to the low column count?

The first query returns 66 rows. For the second, 1 row is returned.

Also, additional note: CustPassMaster has 4991 records, and CustDataMaster has 5376 records. Could this be the reasoning behind ignoring the index? CustPassMaster also has duplicate records that have the same CustNum values as well. Is this another factor?

I am basing this claim on the actual execution plan results of both queries.

Here is the DDL for CustPassMaster (the one with the unused index):

CREATE TABLE dbo.CustPassMaster(
    [CustNum] [varchar](8) NOT NULL,
    [Username] [char](15) NOT NULL,
    [Password] [char](15) NOT NULL,
    /* more columns here */
    [VBTerminator] [varchar](1) NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dbo_CustPassMaster_CustNum] ON dbo.CustPassMaster
(
    [CustNum] ASC
) WITH (PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

And the DDL for CustDataMaster (I've omitted a lot of irrelevant fields):

CREATE TABLE dbo.CustDataMaster(
    [CustNum] [varchar](8) NOT NULL,
    /* more columns here */
    [VBTerminator] [varchar](1) NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dbo_CustDataMaster_CustNum] ON dbo.CustDataMaster
(
    [CustNum] ASC
)WITH (PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I don't have a clustered index on either of those tables, only one nonclustered index.

Ignore the fact that the datatypes don't entirely match the type of data being stored. These fields are a backup from an IBM AS/400 DB2 database, and these are the compatible datatypes for it. (I have to be able to query this backup database with the exact same queries, and get the exact same results.)

This data is only used for SELECT statements. I don't do any INSERT/UPDATE/DELETE statements on it, except for when the backup application is copying data from the AS/400.

Answer 1

Typically indexes will be used by SQL Server if it deems it more expedient to use the index than to directly use the underlying table.

It would seem likely the cost-based optimizer thinks it would be more expensive to actually use the index in question. You may see it use the index if instead of doing SELECT *, you simply SELECT T1Col1.

When you SELECT * you are telling SQL Server to return all columns in the table. To return those columns SQL Server must read the pages for the rows that match the WHERE statement criteria from the table itself (clustered index or heap). SQL Server is probably thinking the amount of reads required to get the rest of the columns from the table means it might as well scan the table directly. It would be useful to see the actual query and the actual execution plan used by the query.

Answer 2

For using the index, because you're doing select *, then SQL Server must first read each of the rows from the index that match the value you have in the where clause. Based on this, it will get the clustered index values for each of the row, and then it has to seek each of them separately from the clustered index (=key lookup). Since you said that the values are not unique, SQL Server uses statistics to estimate how many times it has to do this key lookup.

Most likely the cost estimate for scanning the non-clustered index + key lookups exceeds the cost estimate for clustered index scan, and that's why the index is ignored.

You could try to use set statistics io on and then use an index hint to see if the I/O cost is actually smaller when using the index or not. If the difference is big, you could look into statistics, if those are out of date.

Also, if your SQL is actually using variables and not the exact values, this might also be caused by parameter sniffing (=previous value used to create the plan had a lot of rows in the table).

Answer 3

That might be the reason. The optimizers are cost based and decide what path to choose based on the 'cost' that each execution path has. The 'biggest' cost is getting the data from disk to memory. If the optimizer calculates that it takes more time to read both the index and data then it might decide to skip the index. The bigger the rows are the more disk blocks they take.