Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Фильтрованный индекс не используется в запросе.  [new]
VicSO
Member

Откуда:
Сообщений: 189
Есть код
DECLARE @InVarId1 int = 4848 -- Давл. на фильеру экструдера
DECLARE @LastValue float
DECLARE @LastDate datetime

Select Top 1 @LastValue = Result, @LastDate = Result_On From [SOADB].[dbo].[Test_History]
Where Var_Id = @InVarId1
--And datepart(minute, Result_On) = 0
--Отсекаем секунды вместо минут, в следующих переменных по аналогии.
And datepart(SECOND, Result_On) = 0 
Order By Result_On Desc

И есть индекс ~70ГБ
CREATE NONCLUSTERED INDEX [TestHistory_IX_VarIdResultOn]
ON [dbo].[Test_History]
([Var_Id] , [Result_On] DESC)
INCLUDE ([Result], [Entry_On])

Решил сделать оптимизацию. Так как @InVarId1 принимает ограниченный набор значений (4848), (4849), (4873), (4874), (8326), (8327), (8495), (8496)
Решил создать фильтрованный индекс И есть индекс ~200МБ
CREATE NONCLUSTERED INDEX [TestHistory1_IX_VarIdResultOn]
ON [dbo].[Test_History]
([Var_Id] , [Result_On] DESC)
INCLUDE ([Result], [Entry_On])
WHERE (([Var_Id] IN ((4848), (4849), (4873), (4874), (8326), (8327), (8495), (8496))))

Но запрос не хочет его использовать. Отключаю [TestHistory_IX_VarIdResultOn] и поиск начинается по кластерному индексу.
Когда добавляю к запросу подсказку
WITH (INDEX(TestHistory1_IX_VarIdResultOn))

Выдает ошибку
Обработчик запросов не может предоставить план запроса из-за подсказок, определенных в запросе. Заново запустите запрос без указания подсказок и без использования SET FORCEPLAN.

То есть [TestHistory1_IX_VarIdResultOn] вообще индекс не хочет использовать.
Нужно чтобы без подсказки все заработало, код переписывать не могу. могу ток индексы создавать, удалять.
Для чего нужно, индекс [TestHistory_IX_VarIdResultOn] используется только этим запросом, весит много, хотелось бы сократить объем да и быстрее будет по второму, ток вот понять не могу почему не работает.
30 май 19, 07:23    [21897376]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
VicSO
Member

Откуда:
Сообщений: 189
Забыл указать
Microsoft SQL Server Standard (64-bit) 11.0.7001.0
30 май 19, 07:39    [21897385]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30821
VicSO
Нужно чтобы без подсказки все заработало, код переписывать не могу. могу ток индексы создавать, удалять.
Даже не представляю, как сделать, что бы заработало без изменения запросов... Чтоб можно было option(recompile) добавить (тогда можно не указывать индекус, он сам подцепит)
Тут же смысл в том, что сервер не строит план для конкретного значения, он строит план для типового варианта, и сохраняет, а типовой вариант не может использовать ваш фильтрованный индекс, потому что значение переменной в условии не фиксировано.
30 май 19, 08:03    [21897396]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
VicSO
Member

Откуда:
Сообщений: 189
alexeyvg,

когда делаю
Select Top 1 @LastValue = Result, @LastDate = Result_On From [SOADB].[dbo].[Test_History]
Where Var_Id = 4848
--And datepart(minute, Result_On) = 0
--Отсекаем секунды вместо минут, в следующих переменных по аналогии.
And datepart(SECOND, Result_On) = 0 
Order By Result_On Desc
все равно не берет, но правда подсказка начинает работать. И тогда нужный индекс берет, но тут я явно его задал.
а без явного задания его можно чтобы его брал?

Получается сократить объем индекса не получится, так как фильтр будет работать ток по явно заданному значению и при условии задания подсказки какой индекс использовать?
30 май 19, 08:23    [21897407]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
aleks222
Member

Откуда:
Сообщений: 855
VicSO
[/src]
Решил сделать оптимизацию. Так как @InVarId1 принимает ограниченный набор значений (4848), (4849), (4873), (4874), (8326), (8327), (8495), (8496)
Решил создать фильтрованный индекс И есть индекс ~200МБ
CREATE NONCLUSTERED INDEX [TestHistory1_IX_VarIdResultOn]
ON [dbo].[Test_History]
([Var_Id] , [Result_On] DESC)
INCLUDE ([Result], [Entry_On])
WHERE (([Var_Id] IN ((4848), (4849), (4873), (4874), (8326), (8327), (8495), (8496))))



Чтобы оптимизатор применил фильтрованный индекс, надо в условии запроса ЯВНО повторить условие фильтрации индекса
WHERE (([Var_Id] IN ((4848), (4849), (4873), (4874), (8326), (8327), (8495), (8496))))
                    and ...
30 май 19, 08:26    [21897410]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
VicSO
Member

Откуда:
Сообщений: 189
aleks222,

то есть я не правильно сделал индекс?
CREATE NONCLUSTERED INDEX [TestHistory1_IX_VarIdResultOn]
ON [dbo].[Test_History]
([Var_Id] , [Result_On] DESC)
INCLUDE ([Result], [Entry_On])
WHERE ([Var_Id] = 8496)

и так для каждого значения?
30 май 19, 08:30    [21897413]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
VicSO
Member

Откуда:
Сообщений: 189
aleks222,

то есть я не правильно сделал индекс?
CREATE NONCLUSTERED INDEX [TestHistory1_IX_VarIdResultOn]
ON [dbo].[Test_History]
([Var_Id] , [Result_On] DESC)
INCLUDE ([Result], [Entry_On])
WHERE ([Var_Id] = 8496)

и так для каждого значения?
30 май 19, 08:37    [21897419]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
aleks222
Member

Откуда:
Сообщений: 855
VicSO
aleks222,

то есть я не правильно сделал индекс?
CREATE NONCLUSTERED INDEX [TestHistory1_IX_VarIdResultOn]
ON [dbo].[Test_History]
([Var_Id] , [Result_On] DESC)
INCLUDE ([Result], [Entry_On])
WHERE ([Var_Id] = 8496)

и так для каждого значения?

Чем тебя не устраивает список?
30 май 19, 08:41    [21897420]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
VicSO
Member

Откуда:
Сообщений: 189
aleks222,
но я не могу повторить
CREATE NONCLUSTERED INDEX [TestHistory4848_IX_VarIdResultOn]
ON [dbo].[Test_History]
([Var_Id] , [Result_On]Desc)
INCLUDE ([Result], [Entry_On])
WHERE ([Var_Id] = 4848 And datepart(SECOND, Result_On) = 0 )

а так создается

CREATE NONCLUSTERED INDEX [TestHistory4848_IX_VarIdResultOn]
ON [dbo].[Test_History]
([Var_Id] , [Result_On]Desc)
INCLUDE ([Result], [Entry_On])
WHERE ([Var_Id] = 4848 )

Но при выполнении
DECLARE @InVarId1 int = 4848 -- Давл. на фильеру экструдера
DECLARE @LastValue float
DECLARE @LastDate datetime

Select Top 1 @LastValue = Result, @LastDate = Result_On From [SOADB].[dbo].[Test_History]
Where Var_Id = 4848 
--And datepart(minute, Result_On) = 0
--Отсекаем секунды вместо минут, в следующих переменных по аналогии.
And datepart(SECOND, Result_On) = 0 
Order By Result_On Desc

Индекс все равно не используется.
30 май 19, 08:42    [21897421]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
aleks222
Member

Откуда:
Сообщений: 855
WHERE (([Var_Id] IN ((4848), (4849), (4873), (4874), (8326), (8327), (8495), (8496))))
                    and [Var_Id] = @Var_Id
30 май 19, 08:42    [21897422]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30821
VicSO
когда делаю
Select Top 1 @LastValue = Result, @LastDate = Result_On From [SOADB].[dbo].[Test_History]
Where Var_Id = 4848
--And datepart(minute, Result_On) = 0
--Отсекаем секунды вместо минут, в следующих переменных по аналогии.
And datepart(SECOND, Result_On) = 0 
Order By Result_On Desc

все равно не берет, но правда подсказка начинает работать. И тогда нужный индекс берет, но тут я явно его задал.
а без явного задания его можно чтобы его брал?
У меня берёт без подсказки с явным указанием индекса

При этом у меня нет большого индекса. У вас он есть, или нет? Если есть, то сервер его и использует, он ведь не хуже, по большому счёту.
30 май 19, 08:50    [21897425]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
VicSO
Member

Откуда:
Сообщений: 189
aleks222,

А разве в индекс фильтр такой можно добавлять (у меня ругается)?
или вы предлагаете его в select добавить?
все равно не использует данный индекс.
30 май 19, 08:57    [21897432]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
VicSO
Member

Откуда:
Сообщений: 189
alexeyvg,

Да есть, то есть SQL считает что время поиска в обоих случаях одинаковое будет и берет что нравится (на данный момент большой индекс)

То есть не получится про анализировать, что большой индекс больше не востребован и его можно удалить.
30 май 19, 09:00    [21897437]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
aleks222
Member

Откуда:
Сообщений: 855
VicSO
aleks222,

А разве в индекс фильтр такой можно добавлять (у меня ругается)?
или вы предлагаете его в select добавить?
все равно не использует данный индекс.

В запрос.
30 май 19, 09:26    [21897457]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
VicSO
Member

Откуда:
Сообщений: 189
aleks222,

А вот тут и проблема что я не могу менять процедуры :(

в MSSSQL есть типа такого что если встречает
Select Top 1 @LastValue = Result, @LastDate = Result_On From [SOADB].[dbo].[Test_History]
Where Var_Id = @InVarId1
--And datepart(minute, Result_On) = 0
--Отсекаем секунды вместо минут, в следующих переменных по аналогии.
And datepart(SECOND, Result_On) = 0 
Order By Result_On Desc

подменяет на
Select Top 1 @LastValue = Result, @LastDate = Result_On From [SOADB].[dbo].[Test_History]
Where (([Var_Id] IN ((4848), (4849), (4873), (4874), (8326), (8327), (8495), (8496)))) and 
Var_Id = @InVarId1
--And datepart(minute, Result_On) = 0
--Отсекаем секунды вместо минут, в следующих переменных по аналогии.
And datepart(SECOND, Result_On) = 0 
Order By Result_On Desc

Если есть то статью или где почитать можно (если возможность есть такая то буду искать).
А если нету такой возможности то жаль.
30 май 19, 10:13    [21897496]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
invm
Member

Откуда: Москва
Сообщений: 9128
VicSO
И есть индекс ~70ГБ
CREATE NONCLUSTERED INDEX [TestHistory_IX_VarIdResultOn]
ON [dbo].[Test_History]
([Var_Id] , [Result_On] DESC)
INCLUDE ([Result], [Entry_On])
И вы уверены, что данный индекс используется исключительно для показанного запроса?
30 май 19, 10:45    [21897517]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7403
Фильтрованный индекс не используется для оптимизации, если включена принудительная параметризация в настройках базы.
30 май 19, 11:44    [21897571]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4392
VicSO,

Сделайте принудительное обновление статистики
30 май 19, 15:34    [21897774]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30821
VicSO
Да есть, то есть SQL считает что время поиска в обоих случаях одинаковое будет и берет что нравится (на данный момент большой индекс)

То есть не получится про анализировать, что большой индекс больше не востребован и его можно удалить.
Если с подсказкой использует, то и без подсказки будет, если большой индекс удалить.

Другое дело, что большой индекс может использоваться где то ещё, в других запросах.

VicSO
в MSSSQL есть типа такого что если встречает
...
подменяет на
Нет, такого нет.
Если вы не можете менять процедуры (запросы), то задача не решаема, придётся оставить большой индекс.
30 май 19, 17:01    [21897881]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
VicSO
Member

Откуда:
Сообщений: 189
invm,
на 90%, так как провожу на тестовом, а он как известно на все 100% не может быть объективным.
Хотел создать на боевой базе и потом проанализировать есть обращения к большому индексу и если нету то и грохнуть его.
А по факту так не получится, надо быть уверенным на 100% что он не нужен и грохать, а как это сделать не понятно.

Владислав Колосов,
Так без разнице что стоять будет в базе так как он уже с параметром запрос

a_voronin,
Первое что и делаю после создания удаления индексов.
alexeyvg,

Понятно, изменить можно, но это долгий и нудный процесс.
Быстрее было если возможность была подмены.

Кстати может знаете есть ли какие-то интересные статьи как по оптимизации, создания индексов без вмешательство в сам код?
31 май 19, 05:27    [21898143]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
aleks222
Member

Откуда:
Сообщений: 855
VicSO
Кстати может знаете есть ли какие-то интересные статьи как по оптимизации, создания индексов без вмешательство в сам код?

Опять кнопку [Сделать фсе как я хачу] ищем?

Мозги надо включать, а не херней маяться.
Если подумать, в описанном контексте, польза от фильтрованного индекса равна нулю. Если не меньше.

1. Обычный сработает с той же скоростью.
2. Великой экономии памяти тоже не ожидается.
Нафига?
31 май 19, 06:06    [21898146]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
VicSO
Member

Откуда:
Сообщений: 189
aleks222,

Дисковое пространство экономия 100 кратное.
Жмотятся на пространство, я как пришло тут постоянно шринт делали что логов что баз. Сначала ребиллд, а потом шринк, и толк от этого?
вот путаюсь поменять ситуацию. Пытаюсь как-то выиграть пространство, уже один индекс нашел на 100гб :) не кому на хрен не нужный удалили. Теперь хоть куда-то расти есть базе. но боюсь это не на долго прирост где-то 1,2ГБ/в день, сейчас в лучшем случае хватит на 200-300 дней :) и все приплыли.
Раз больше дельных советов нету, будем пробовать другой подход выбивания пространства. Не хотите чтобы все встало нужно увеличить объем диска :)
Я пока исчерпал свои ресурсы (раз фильтрованный индексом не получилось, то все)
31 май 19, 08:57    [21898212]     Ответить | Цитировать Сообщить модератору
 Re: Фильтрованный индекс не используется в запросе.  [new]
aleks222
Member

Откуда:
Сообщений: 855
VicSO
aleks222,

Дисковое пространство экономия 100 кратное.
Жмотятся на пространство, я как пришло тут постоянно шринт делали что логов что баз. Сначала ребиллд, а потом шринк, и толк от этого?
вот путаюсь поменять ситуацию. Пытаюсь как-то выиграть пространство, уже один индекс нашел на 100гб :) не кому на хрен не нужный удалили. Теперь хоть куда-то расти есть базе. но боюсь это не на долго прирост где-то 1,2ГБ/в день, сейчас в лучшем случае хватит на 200-300 дней :) и все приплыли.
Раз больше дельных советов нету, будем пробовать другой подход выбивания пространства. Не хотите чтобы все встало нужно увеличить объем диска :)
Я пока исчерпал свои ресурсы (раз фильтрованный индексом не получилось, то все)


Ну, мне ваших запросов не видать - хрустальный шар чего-то треснул.

Рассмотрите модификацию кластерного индекса.
31 май 19, 09:53    [21898273]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить