16

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.

18

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.

  • 3
    So a more obvious and optimal solution would be for me to limit the columns I select, and include them in the INCLUDE clause of the index? – Der Kommissar Dec 29 '15 at 16:27
  • 1
    That could very well make a large difference. Adding all the columns returned by the query to the INCLUDE clause will likely make SQL Server use the index. Having said that, what are you attempting to optimize? It seems to me if your table has an average row size of 100 bytes, then 5000 rows is only about 500kb of data, and may well be not worth spending any time on. – Max Vernon Dec 29 '15 at 16:32
  • 1
    The average row size is 0.30KB for Table1, and 0.53KB for Table2. All this data is imported from an AS/400 (IBM System i) and there are NO PK's on anything. I manually created all the indexes today after people were mentioning that the application is quite slow at times. – Der Kommissar Dec 29 '15 at 16:42
10

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).

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.