2

I have a table with a nonclustered index on a datetime2 field.

On that same table i have a field (char[1]) which is used to logically delete records and can have 2 distinct values : A(active) or D(deleted).

There are 451047 records with the datetime2 field set at NULL but only 7095 are marked as A.

Every query in the application looks for active records only, so, every query that's looking for NULLs in the datetime field was getting really bad estimates and, with that, bad execution plans.

Simple example: Plan without filtered index: look at estimated rows vs actual rows

I decided then to create a filtered non clustered index but estimates were still incorrect: Plan using a filtered index, still getting wrong estimates

It looks like i still get the old estimates, even if the query is using properly the filtered index. Does anyone know the reason of this behaviour?

These are the new statistics of the filtered index: Filtered index statistics

Table definition:

CREATE TABLE [TYDATPRD].[HAND00F](
    [STDRECSTS] [char](1) NULL,
    [HDHAND] [numeric](14, 0) IDENTITY(1,1) NOT NULL,
    [HDCHKINDT] [datetime2](7) NULL,
    --lots of other columns which I don't think are needed

    CONSTRAINT [PK_TYDATPRD_HAND00F] PRIMARY KEY CLUSTERED (
        [HDHAND] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

Nonclustered filtered index definition:

CREATE NONCLUSTERED INDEX [IX_HAND00F_HDCHKINDT] ON [TYDATPRD].[HAND00F] (
    [HDCHKINDT] ASC
)
WHERE [STDRECSTS]='A'
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 [INDEXES]
GO
2
  • I know two things that might help you. 1) Filtered Indexes are great when they work properly, but getting them to do so can be a challenge. 2) You can get better performance if you can set your RANGE_HI_KEY instead of NULL to be a "magic" date, such as 1/1/1900 or some such. I realize that at this late date that may have many unintended consequences for your application, but it really does give the engine something better to work with than NULL. Jun 6, 2018 at 13:17
  • That's funny: we just finished a db migration from db2 to sqlserver and with that we decided that it was better to change those placeholder dates into nulls! i really hope that's not the problem Jun 6, 2018 at 13:28

1 Answer 1

3

This is not intuitive but try adding the char[1] column to the filtered index definition:

CREATE NONCLUSTERED INDEX [IX_HAND00F_HDCHKINDT] 
ON [TYDATPRD].[HAND00F] 
(
    [HDCHKINDT], STDRECSTS
)
WHERE [STDRECSTS] = 'A'
WITH (DROP_EXISTING = ON) ON [INDEXES];

In my testing, this led to the right estimates.

As an aside, I noticed that the plans in your question were different - one was properly parameterized and the other had a constant. Be very careful about how you're doing local testing with local variables and typed constants, especially if in the real world this will be done by parameters to stored procedures.

Also, be very careful about case - literals in the where clause like = 'a' and = 'A' will generate different plans if they don't get parameterized, because the query text is different. Never mind that in some collations they won't yield the same results. (And does that char(1) column need to allow NULL?)

3
  • Thank you, now it shows good estitmates. That's still weird tho, why would that happen? Is that a bug or there's a specific reason? As for the notes: 1) the first time the query was parametrized by simple parametrization. 2) Yeah i'm aware but thanks for reminding, i appreciate it! (the application parametrize statements) 3) Yeah that field doesn't need to allow NULLs but this database is so old and full of definition-mistakes that we don't really care that much anymore Jun 6, 2018 at 14:14
  • 1
    The simple answer is they're not perfect and the optimizer can't always make perfect decisions as a result. I wrote a post about many of the other limitations back in 2013 (of course all the Connect links now go to /dev/null/): sqlperformance.com/2013/04/t-sql-queries/filtered-indexes Jun 6, 2018 at 15:24
  • I've seen this quite a few times recently in trying to optimize some filtered indexes. I have one massively filtered index which took a table down from 1.3 MILLION rows to 7 (seven) rows. The optimizer was happily using that index (with 7 total rows) but still estimating 200,000 rows. Changing the order of columns in the index definition changed the number of estimated rows. So try putting the 'filtered' column either first or last. I understand estimation is super complex, but telling me you're going to use an index with 7 rows in it and estimating 200,000 seems like a bug. May 12, 2021 at 2:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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