This post is based on a question I received last week…

If we have a filtered index that was not a match for a query due to parameterization (or a variation of which the parameter is not known at compile time), we can see the unmatched filtered index name in the UnmatchedIndexes element of SHOWPLAN_XML output.

The question I received on this subject was, “what about for filtered statistics?”  Can those still be used for parameterized scenarios? The following is a quick test to see if the stats also don’t qualify for matching… I’ll start by showing the filtered statistics matching behavior using the following statistics object:

USE [Credit];
GO

CREATE STATISTICS fstat_charge_provider_no
ON [dbo].[charge] ([member_no], [category_no])
WHERE [provider_no] = 484;
GO

The following query will use the statistics, and I’ve included the trace flags to show the usage from a query optimizer perspective:

SELECT DISTINCT
        1.[member_no],
        1.[category_no],
        1.[provider_no]
FROM    [dbo].[charge] AS c
WHERE   1.[provider_no] = 484 AND
        1.[member_no] = 9527 AND
        1.[category_no] = 2
OPTION  (QUERYTRACEON 3604, QUERYTRACEON 9204);
GO

This returns the following message output based on an initial compilation:

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 2, ColumnName: category_no, EmptyTable: FALSE

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 3, ColumnName: provider_no, EmptyTable: FALSE

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 6, ColumnName: member_no, EmptyTable: FALSE

Filtered stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 7, ColumnName: member_no, 
Expr: ([provider_no]=(484)), EmptyTable: FALSE

Filtered stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 7, ColumnName: member_no, 
Expr: ([provider_no]=(484)), EmptyTable: FALSE

Filtered stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 7, ColumnName: member_no, 
Expr: ([provider_no]=(484)), EmptyTable: FALSE

Now let’s use a local variable instead for the provider_no predicate (used for the filtered statistics):

DECLARE @provider_no INT = 484;

SELECT DISTINCT
        1.[member_no],
        1.[category_no],
        1.[provider_no]
FROM    [dbo].[charge] AS c
WHERE   1.[provider_no] = @provider_no AND
        1.[member_no] = 9527 AND
        1.[category_no] = 2
OPTION  (QUERYTRACEON 3604, QUERYTRACEON 9204);
GO

This time we see the following trace flag 9204 output:

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 2, ColumnName: category_no, EmptyTable: FALSE

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 3, ColumnName: provider_no, EmptyTable: FALSE

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 6, ColumnName: member_no, EmptyTable: FALSE

Even though the provider_no value matched “484”, the filtered stats were not loaded as the value was not known at compile time. This is one specific scenario – so if you have a different one on this subject, please feel free to share on the comments of this post.

Thanks!