CODEWITHSUNDEEP
sqlsql-server
sommmen
sommmen

Reputation: 7618

Why is my filtered index not being used for one statement?

I have the following table (an excerpt follows w/o other columns):

USE [opg-systems-dev]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Orders]
(
    [SyncChannelEngineOrder] [bit] NOT NULL,
    [IsSyncing] [bit] NOT NULL,

    CONSTRAINT [PK_Orders] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] 
    ADD DEFAULT (CONVERT([bit], (0))) FOR [IsSyncing]
GO

-- -----

CREATE NONCLUSTERED INDEX [IX_Orders_SyncChannelEngineOrder] 
ON [dbo].[Orders] ([SyncChannelEngineOrder] ASC)
WHERE ([SyncChannelEngineOrder] <> (0))
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
      SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
      OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Orders_IsSyncing] 
ON [dbo].[Orders] ([IsSyncing] ASC)
WHERE ([IsSyncing] <> (0))
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
      SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
      OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

I'm trying to use a filtered index here - but it seems when I'm querying that the the index for SyncChannelEngineOrder is not being used for some reason.

The setup is supposed to be the same for both columns.

enter image description here

Upvotes: 1

Views: 770

Answers (1)

Paul Williams
Paul Williams

Reputation: 17020

The query is requesting all columns with a select *. The indexes only contain one column (ignoring the clustered key). To provide the missing column data that is not on the index, the query optimizer is calculating a lower cost to just scan all of the rows in the source table.

If SQL Server chose to use the existing indexes, it would first scan those rows, and then it would perform a bookmark lookup to get the rest of the columns from the table. If there are very few rows in the filtered index, and the table is very large, the SQL Server query optimizer could theoretically calculate a low enough cost to use the filtered index to limit the number of rows. Otherwise, bookmark lookups are expensive on a large number of rows. I am guessing the number of rows in the index is over this threshold.

SQL Server will add the clustered key to a non-clustered index to facilitate the bookmark lookup. If the query were to request only the clustered key and the index filter columns, the indexes would cover the query, and I expect the query optimizer would choose to use the filtered indexes.

Upvotes: 2

Related Questions