Параметризованный запрос не использует отфильтрованный индекс
Есть таблица событий со статусом и источником события:
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 шт):
Ответ: "никак".
Фильтрованный индекс невозможно использовать, если значение представлено в виде параметра. В случае параметра генерируется план для «средней температуры по больнице». Для использования же фильтрованных индексов (а если обобщить – для построения оптимальных планов под конкретные значения) надо вместо параметров использовать константы. Значения параметров подставляются в уже готовый план. И значение параметра может оказаться вне фильтра, а значит и использовать этот индекс с параметрами никак нельзя
Update: для linq который транслируется в SQL можно указать чтобы генерировался не код WHERE ([Field] = @p_0) а WHERE ([Field] = 42: нужно чтобы константа была именно константой


