Why is an additional filtered statistic being ignored (EAV schema)?

I'm trying to improve a row estimate for this sub-query (of a larger query). The estimate is showing 1266 rows. The actual is 117k rows. This particular property (EAV schema) only has two values defined for it (2 and 3):

declare @pPropVal smallint = 2;

select Value, ObjectId 
  from Oav.ValueArray PropName
 where PropName.PropertyId = 897
   and PropName.Value  = @pPropVal
option (recompile)

The query plan shows the proper seek predicate on index IX_ValueArray_PropValObj on PropertyId and Value as expected.

(A) As an attempt to improve row estimates, an additional statistic was added which brought the row estimate up slightly to 3041:

create statistics [ST_SomePropertyName] ON [Oav].[ValueArray](PropertyId, Value, ObjectId)
 where 
     (     
             PropertyId = 897 
         and [Value] is not null
     )
  with fullscan

The histogram shows a single row. The HI key is just the PropertyId (the first column) which is not that useful so as I understand it, it is using the density information.

RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS  DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
897             0           196026   0                    1

All density Average Length  Columns
1           4               PropertyId
0.5         8               PropertyId, Value

Name    Updated Rows    Rows Sampled    Steps   Density Average key length  String Index    Filter Expression   Unfiltered Rows
ST_SomePropertyName May 20 2014  2:01PM 196026  196026  1   0   8   NO  ([PropertyId]=(897) AND [Value] IS NOT NULL)    9317055

(B) Since there is a filter on PropertyId = 897, I thought I could re-create the statistic like this:

create statistics [ST_SomePropertyName] ON [Oav].[ValueArray](Value, ObjectId)
where
    (       
       PropertyId = 897 
       and [Value] is not null
    )
 with fullscan

The histogram looks useful to my eyes but the estimator appears to be ignoring it because it reverts to the original estimate of 1266.

```
RANGE_HI_K

Filtered indexes and statistics won't come into play when you're using local variables, unless you use the OPTION (RECOMPILE) query hint, and are running SQL Server 2008 R2 or later.

Tim Chapman's MSDN blog post explains with examples.

StackExchange Database Administrators Q#65431, answer score: 6