Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that has following structure, as you can see, there are one clustered index and two non-clustered indexes, one non-clustered index is IX_ParentId_Include, and another one is FLIX_ParentId_Include

they are same apart from the second index is filtered.

CREATE TABLE [dbo].[PhotoRepo] (
    [PhotoRepoId]              INT            IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL,
    [TypeId]                   TINYINT        NULL,
    [ParentId]                 INT            NULL,
    [RemoteLocation]           VARCHAR (4000) NOT NULL,
    [UploadedAt]               DATETIME       NOT NULL,
    [UploadedBy]               VARCHAR (255)  NULL,
    [FileSize]                 INT            NOT NULL,
    [DefaultChild]             BIT            NOT NULL,
    [RemoteLocationUploadedAt] DATETIME       NULL,
    CONSTRAINT [PK_FileList] PRIMARY KEY NONCLUSTERED ([PhotoRepoId] ASC)
);


GO
CREATE CLUSTERED INDEX [IX_PhotoRepoId]
    ON [dbo].[PhotoRepo]([PhotoRepoId] ASC);


GO


CREATE NONCLUSTERED INDEX [IX_ParentId_Include]
    ON [dbo].[PhotoRepo]([ParentId] ASC)
    INCLUDE([RemoteLocation], [DefaultChild], [TypeId]);
GO

CREATE NONCLUSTERED INDEX [FLIX_ParentId_Include]
    ON [dbo].[PhotoRepo]([ParentId] ASC)
    INCLUDE([RemoteLocation], [DefaultChild], [TypeId])
    WHERE TypeId <> 7;

GO

Now when I run this query with execution plan enabled, the query optimizer doesn't use filter index, which I expect it should be more efficient one, it uses IX_ParentId_Include. It uses filter index only when i either force it to, or delete IX_ParentId_Include

Anyone knows why?

SELECT 
    *
FROM   PhotoRepo
WHERE  
    typeid <> 7 and
    parentid = 123

PS: there is 30% rows that have typeid = 7

share|improve this question

migrated from stackoverflow.com Apr 8 at 14:26

This question came from our site for professional and enthusiast programmers.

1  
Why do you have a non-clustered PK constraint on the IDENTITY column, and a clustered (but not unique!) index on the same column? –  Aaron Bertrand Apr 8 at 15:03
1  
it is Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) –  Ming Apr 8 at 15:05
    
sorry it should be clustered, I noticed that, and I haven't fixed it, will it be the bottom reason of this? –  Ming Apr 8 at 15:06
    
2008 RTM? Why haven't you applied at least Service Pack 3? I'm sure some optimization bugs with filtered indexes were fixed in that timeframe, even if most of them have been ignored... –  Aaron Bertrand Apr 8 at 15:07
    
@Ming no, I highly doubt it, it just stood out as a head-scratcher. –  Aaron Bertrand Apr 8 at 15:07

1 Answer 1

Why do you think it should use the filtered index? You've used SELECT * in your query and your predicate involves more than just the filter condition. SQL Server is going to have to look up those columns in another index that covers it anyway (your SELECT * plan should include a key lookup to retrieve those other columns).

SQL Server will decide based on stats/cardinality (e.g. how many rows currently match the filter, how many rows total) and decide at that point which index it should use. In this case, it is probably a coin flip, because it can seek to the 19 rows that match the ParentId predicate, and not cost any more in I/O to do so. It might choose the filtered index if it represented a much smaller portion of the table than 30%. You should see what it does, though, if you add OPTION (RECOMPILE) to the query. Depending on your parameterization settings, it may be using a plan that was cached with a different parameter value (e.g. one that doesn't match the index filter).

In this case, I believe you are seeing this behavior because, well, RTM. Please install Service Pack 3 and consider Cumulative Update 16. I browsed quickly and didn't see anything relating to filtered indexes, but this could be a by-product of some other optimizer wonkiness that has been fixed in the meantime. In any case, I can't really think of any good reason why you should continue running RTM at this time.

Could also be related to these are other "bugs known gaps in functionality":

I've also blogged about a whole slew of limitations with filtered indexes, and pointed to a ton of related Connect items. Most of these remain unfixed and, for the most part, filtered indexes have received absolutely no attention since they were first introduced. If you're going to use these objects, and expect them to be used to speed up your queries, you'd be wise to familiarize yourself with all of these limitations, and consider using an index hint to force them to be used in some scenarios. Caveats with using an index hint, of course:

  1. It may not work better (you don't always know better than the optimizer); it may work better for some queries but not all. Usually SQL Server will use the filtered index if (a) it can, and (b) it deems it to be the most efficient access method.
  2. Queries will fail if the index is dropped, or re-created with a new name.
share|improve this answer
    
Okay I added OPTION (RECOMPILE), and I also changed select all to select a single column 'ParentId', execution plan still shows IX_ParentId_Include is in use –  Ming Apr 8 at 14:45
    
@Ming did you try NOT USING SELECT *? –  Aaron Bertrand Apr 8 at 14:48
    
Yes, I changed select * to select parentid, same results (with recompile option) –  Ming Apr 8 at 14:50
1  
@Ming How many rows does the table have and how many rows do have parentid = 123? If the numbers are small, I guess the difference in efficiency of the two indexes is negligible. –  ypercube Apr 8 at 14:54
1  
@Ming why is your test environment at a much higher @@VERSION than production? This seems fraught with peril. –  Aaron Bertrand Apr 8 at 15:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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