SQL Server не использует индекс в хранимой процедуре

это может быть анализ параметров, поэтому попробуйте что-нибудь вроде этого:

ALTER PROCEDURE MyProcedure (@fid BIGINT)
AS BEGIN
    DECLARE @fid_sniff BIGINT
    SET @fid_sniff=@fid
    SELECT foreignId
    FROM STAT_Statistieken
    WHERE foreignId = @fid_sniff
END

подробнее об обнаружении выходных параметров: http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

Какой тип данных является foreignId в таблице? Если это int, вы, вероятно, получаете неявное преобразование, которое предотвращает поиск по индексу. Если тип данных в таблице - int, тогда переопределите параметр на int, и вы должны получить поиск по индексу (а не сканирование индекса) для этого запроса.

Полученное вами сообщение об ошибке может быть сгенерировано, если в вашем запросе есть конфликтующие подсказки.

Можете ли вы выполнить запрос, включая подсказку, вне хранимой процедуры?

Альтернативный ход мыслей, вы тестировали / запускали свою хранимую процедуру с другим значением параметра? Возможно, значение параметра, использованное для создания исходного плана выполнения, не подходит для всех действий. Вы можете захотеть перекомпилировать свою хранимую процедуру, чтобы увидеть, создается ли другой план выполнения между разными запусками с разными параметрами.

Если вы хотите, чтобы новый план запроса вычислялся для каждого выполнения вашей хранимой процедуры, вы можете используйте предложение WITH RECOMPILE. Это должно быть исключением, а НЕ нормой. Подтвердите поведение вашей процедуры и создание плана с помощью тестирования.

Попробуйте это и получите результат:

DBCC FLUSHPROCINDB: Используется для очистки кеша хранимых процедур для конкретной базы данных на SQL Server, а не всего SQL Server. Идентификационный номер базы данных, на которую будет воздействовать, необходимо ввести как часть команды.

Вы можете использовать эту команду перед тестированием, чтобы убедиться, что предыдущие планы хранимых процедур не повлияют отрицательно на результаты тестирования.

Пример:

] DECLARE @intDBID INTEGER SET @intDBID = (ВЫБРАТЬ dbid ИЗ master.dbo.sysdatabases WHERE name = 'database_name') DBCC FLUSHPROCINDB (@intDBID)

Когда вы передаете параметр, сколько строк в таблице соответствует JOIN относительно общего количества строк в таблице? SQL Server выбирает индекс, используя, среди прочего, соотношение совпадающих строк, возвращаемых JOIN, к общему количеству строк в таблице. Если имеется большое количество возвращаемых строк относительно общего числа в таблице, индекс может быть проигнорирован как индексы предпочтений SQL Server, где количество совпадающих строк меньше по сравнению с общим числом.

Итак, если ваш SELECT и ваш Stored Вызов процедуры использовал разные значения для @fid, тогда вы можете иногда использовать индекс, а иногда нет.

Я видел подобное поведение раньше, когда он фактически принимал подсказку индекса и сделать с ним что-нибудь похуже (сканирование нефильтрованного индекса с поиском по закладкам).

Один из этих четырех должен помочь:

1) Добавить; -T4102; -T4118 к параметрам запуска SQL Server 2005 (может применяться к SQL 2008) . Примечание: это возвращает SQL 2000 неправильную обработку запросов IN и NOT IN в SQL 2005.

2) UPDATE STATISTICS [dbo]. [STAT_Statistieken] WITH FULLSCAN

3) ОПЦИЯ (MAXDOP 1) - иногда параллелизм приводит к генерации действительно глупых запросов

4) Убедитесь, что индекс находится в оперативном режиме.

Также обратите внимание, что если вы создаете индекс в таблице, созданной в хранимой процедуре, этот индекс не существует при компиляции запросов хранимой процедуры, поэтому он не будет использоваться. Поскольку ваша таблица создается глобально в dbo, я предполагаю, что здесь это НЕ.

РЕДАКТИРОВАТЬ: иногда мне хотелось бы, чтобы был настоящий форс-план, где вы могли бы ввести план напрямую, и любой возможный план будет выполнен: своего рода сборка -подобный язык для БД.

Во-первых, я должен сказать, что созданные вами индексы не оптимальны, поскольку их можно использовать только для фильтрации по foreignId .

SQL Server не может выполнить SKIP SCAN , и у вас есть secondaryId в вашем индексе, который не фильтруется с условием диапазона.

Следовательно, ваше условие на foreignId, actieGroep , dagNummer не дает ограниченного числа диапазонов и не может быть полностью выделен. Он может фильтровать только по foreignID , а не по всему набору.

Теперь, к вашему текущему индексу.

Я только что создал ваши таблицы и заполнил их случайными данными с помощью этого скрипта:

DROP TABLE STAT_Statistieken

CREATE TABLE [dbo].[STAT_Statistieken](
    [statistiekId] [bigint] IDENTITY(1,1) NOT NULL,
    [foreignId] [bigint] NOT NULL,
    [datum] [datetime] NOT NULL, --date
    [websiteId] [int] NOT NULL,
    [actieId] [int] NOT NULL, --actionId
    [objectSoortId] [int] NOT NULL, --kindOfObjectId
    [aantal] [bigint] NOT NULL, --count
    [secondaryId] [int] NOT NULL DEFAULT ((0)),
    [dagnummer]  AS (datediff(day,CONVERT([datetime],'2009-01-01 00:00:00.000',(121)),[datum])) PERSISTED, --daynumber
    [actieGroep]  AS (substring(CONVERT([varchar](4),[actieId],0),(1),(1))) PERSISTED,
    CONSTRAINT [STAT_Statistieken_PK] PRIMARY KEY CLUSTERED --actionGroup
    (
        [statistiekId] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_1] ON [dbo].[STAT_Statistieken] 
(
    [foreignId] DESC,
    [secondaryId] ASC,
    [actieGroep] ASC,
    [dagnummer] DESC, 
    [aantal] ASC --count
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF,  ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_2] ON [dbo].[STAT_Statistieken] 
(
    [foreignId] DESC,
    [secondaryId] ASC,
    [actieId] ASC,
    [dagnummer] DESC,
    [aantal] ASC -- count
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

;WITH    nums AS
        (
        SELECT  1 AS num
        UNION ALL
        SELECT  num + 1
        FROM    nums
        )
INSERT
INTO    STAT_Statistieken (
        [foreignId], [datum], [websiteId], [actieId],
        [objectSoortId], [aantal])
SELECT  TOP 100000
        500, GETDATE(), num, num, num, num % 5
FROM    nums
UNION ALL
SELECT  TOP 100000
        num % 1000, GETDATE(), num, num, num, num % 5
FROM    nums
OPTION (MAXRECURSION 0)

UPDATE STATISTICS STAT_Statistieken

], и он использует INDEX SEEK независимо от того, что, скорее всего, означает, что проблема связана с вашим распределением данных.

I '

Попробуйте создать свой индекс следующим образом:

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_2] ON [dbo].[STAT_Statistieken] 
(
    [foreignId] DESC,
    [secondaryId] ASC,
    [actieId] ASC,
    [dagnummer] DESC,
    [aantal] ASC -- count
)
INCLUDE (actieGroep);       
WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

А потом воссоздайте процедуру

select AU.*
FROM SYS.Allocation_units AS AU
INNER JOIN SYS.Partitions AS P
ON AU.Container_id = P.Partition_id
WHERE Object_ID = object_id('STAT_Statistieken')

Попробуйте это и проверьте, содержит ли НЕКЛАСТЕРНЫЙ ИНДЕКС больше страниц, чем КЛАСТЕРНЫЙ ИНДЕКС (ЭТО ОЗНАЧАЕТ, ЧТО ДЕШЕВЛЕ ДЛЯ ЧТЕНИЯ КЛАСТЕРИРОВАННОГО ИНДЕКСА)