Параметризованный запрос не использует отфильтрованный индекс

Есть таблица событий со статусом и источником события:

CREATE TABLE [dbo].[InteropTask] (
    [Id] [int] IDENTITY (1, 1) NOT NULL
   ,[Source] [int] NOT NULL
   ,[Created] [datetime] NOT NULL
   ,[Category] [int] NOT NULL
   ,[Status] [int] NOT NULL
   ,[Parameters] [nvarchar](MAX) NOT NULL
   ,CONSTRAINT [PK_InteropTask] PRIMARY KEY CLUSTERED
    (
    [Id] ASC
    )
) 

И из кода C# генерируется запрос вида:

exec sp_executesql N'SELECT [Category], [Created], [Id], [Parameters], [Source], [Status]
FROM [dbo].[InteropTask]
WHERE ([Source] = @p_0)
AND ([Status] = @p_1)
',N'@p_0 int,@p_1 int',@p_0=2,@p_1=0

Далее для удобства смотрел из SSMS такой запрос:

declare @p_0 int  = 2
declare @p_1 int = 0

SELECT
    [Category]
   ,[Created]
   ,[Id]
   ,[Parameters]
   ,[Source]
   ,[Status]
FROM [dbo].[InteropTask]
WHERE ([Source] = @p_0)
AND ([Status] = @p_1)

И захотелось мне попробовать фильтрованный индекс создать. Ну вот например такой:

CREATE NONCLUSTERED INDEX [IDX_1111] ON [dbo].[InteropTask]
(
[Source] ASC,
[Status] ASC
)
WHERE ([status] = (0))

При этом вижу что в запросе этот индекс не используется:

введите сюда описание изображения

Только если убрать параметризацию, тогда индекс начинает использоваться:

введите сюда описание изображения

введите сюда описание изображения

Перечитал офдок (Create Filtered Indexes en ru), но так и не понял, что оптимизатору не нравится.

Пробовал создавать индекс с WHERE ([status] = (0) and source = (2)) - эффект тот же.

В идеале хочется, чтобы параметризованный запрос видел индекс. Версия сервера: SQL Server 2012 SP4


Ответы (1 шт):

Автор решения: A K

Ответ: "никак".

Фильтрованный индекс невозможно использовать, если значение представлено в виде параметра. В случае параметра генерируется план для «средней температуры по больнице». Для использования же фильтрованных индексов (а если обобщить – для построения оптимальных планов под конкретные значения) надо вместо параметров использовать константы. Значения параметров подставляются в уже готовый план. И значение параметра может оказаться вне фильтра, а значит и использовать этот индекс с параметрами никак нельзя

Update: для linq который транслируется в SQL можно указать чтобы генерировался не код WHERE ([Field] = @p_0) а WHERE ([Field] = 42: нужно чтобы константа была именно константой

См. тут: https://stackoverflow.com/q/27225626/5752652

→ Ссылка