sqlskills-logo-2015-white.png

Are Filtered Statistics Useful with Parameterization?

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
        [c].[member_no],
        [c].[category_no],
        [c].[provider_no]
FROM    [dbo].[charge] AS c
WHERE   [c].[provider_no] = 484 AND
        [c].[member_no] = 9527 AND
        [c].[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
        [c].[member_no],
        [c].[category_no],
        [c].[provider_no]
FROM    [dbo].[charge] AS c
WHERE   [c].[provider_no] = @provider_no AND
        [c].[member_no] = 9527 AND
        [c].[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!

4 thoughts on “Are Filtered Statistics Useful with Parameterization?

  1. Filtered statistics and indexes can still be used with local variable/parameterized queries if the OPTION (RECOMPILE) hint is used, though only when the statement is actually executed (and recompiled); requesting an estimated plan is not enough. The unmatched index warning also disappears from the actual plan (it still appears in the estimated one).

    It’s interesting to think about whether we would want the optimizer to use filtered statistics when sniffed parameters happen to match. It doesn’t do this today.

    1. Thanks Paul. Agreed. I’m not sure what the proper solution would be compared to the current behavior – other than some kind of directive that “filtered indexes are safe to use and I’m okay with failing if you break the contract.” Of course than we could force via a hint, but that removes the flexibility.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.