Добрый день!
Имеем сервер Microsoft SQL Server 2019 (RTM-CU14) (KB5007182) - 15.0.4188.2 (X64)
Есть таблица 25 млн записей.
Для таблицы есть 2 индекса:
CREATE UNIQUE CLUSTERED INDEX [_AccumRg35623_1] ON [dbo].[_AccumRg35623]
(
[_Period] ASC,
[_RecorderTRef] ASC,
[_RecorderRRef] ASC,
[_LineNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [_AccumRg35623_2] ON [dbo].[_AccumRg35623]
(
[_RecorderTRef] ASC,
[_RecorderRRef] ASC,
[_LineNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

Есть запрос

SELECT
0x00000000000000000000000000000000,
MIN(T1._Period)
FROM dbo._AccumRg35623 T1
WHERE
(T1._RecorderTRef = 0x000001EB
AND T1._RecorderRRef = 0x9C575CB90199817511EC632194753AD9)

В какой то момент времени запрос перестал использовать индекс _AccumRg35623_2 и делает сканирование по индексу _AccumRg35623_1. Сканирование занимает около 3,5 сек. Процедурные кэши сбрасывались, статистики обновлялись, индексы перестраивались. Результат тот же.

если добавить 3й индекс только по полям _RecorderTRef и _RecorderRRef то запрос начинает работать по нему. Выборка мгновенная(меньше 1 мс), т.к. обычно в таблице существует 1 запись удовлетворяющая условиям поиска.

Индексы _AccumRg35623_1 и _AccumRg35623_2 поменять нельзя. Запрос тоже. Создавать дублирующий индекс не хочется, выглядит избыточным. Да и подобных ситуаций может быть много. Аналогичные по структуре таблицы исчисляются сотнями. На текущий момент есть проблема только с двумя.

План запроса со сканированием прикладываю.

Прошу помочь разобраться почему сервер не использует индекс _AccumRg35623_2.

aleks222,

Делали, не помогало.
На всякий случай сделал еще раз
UPDATE STATISTICS _AccumRg35623 WITH FULLSCAN, MAXDOP = 0, PERSIST_SAMPLE_PERCENT = ON;
результат не изменился.

Konst_One,

Не работает, это пробовали.

0wl,

Во вложении

план прикреплен к первому сообщению

Сделал для индекса _AccumRg35623_2 DATA_COMPRESSION = PAGE
После выполнения сбросил процедурный кэш.
Поведение не поменялось.

Да, это 1С. Индексы созданы платформой, менять их крайне не желательно(нельзя)
1.Наименование индекса зависит от версии платформы в которой создавался индекс. Наименование вида _AccumRg35623_ByRecorder было характерно для 8.2. Если сделать реструктуризацию на последней версии платформы то индекс будет иметь имя вида _AccumRg35623_2 ,по крайней мере у меня так. Возможно причина в методе реструктуризации. Не исследовал данный вопрос.
2. Разделители данных можно использовать, а можно не использовать. Зависит от логики конфигурации. В моем случае его нет. Привожу структуру таблицы:
CREATE TABLE [dbo].[_AccumRg35623](
[_Period] [datetime2](0) NOT NULL,
[_RecorderTRef] [binary](4) NOT NULL,
[_RecorderRRef] [binary](16) NOT NULL,
[_LineNo] [numeric](9, 0) NOT NULL,
[_Active] [binary](1) NOT NULL,
[_RecordKind] [numeric](1, 0) NOT NULL,
[_Fld35624RRef] [binary](16) NOT NULL,
[_Fld35625RRef] [binary](16) NOT NULL,
[_Fld35626] [datetime2](0) NOT NULL,
[_Fld35627] [numeric](15, 3) NOT NULL
) ON [PRIMARY]

Насчет "UPDATE STATISTICS _AccumRg35623 WITH FULLSCAN" Только решил проверить, запустил запрос чтобы опять увидеть скан, и о чудо - запрос выполнился по индексу _AccumRg35623_2. Не люблю совпадений. Все что менялось это ночью был перезагружен сервер. Но на счастье(или наоборот) у меня есть еще 1 таблица с таким же точно поведеним - _AccumRg25415. Так вот на ней скан продолжается. Отказ от PERSIST_SAMPLE_PERCENT в UPDATE STATISTICS не влияет.

Помогло. План прикладываю. Имя таблицы поменялось по причине того что _AccumRg35623 чудесным образом вылечилась, но проблема продолжает воспроизводится на другой таблице _AccumRg25415. Структура таблиц и индексов идентичны. Размер тоже.
Сейчас буду детальнее читать что дает использование FORCE_LEGACY_CARDINALITY_ESTIMATION, если поясните "на пальцах" сильно поможет в понимании проблемы.

Почитал про parameter sniffing. Везде упоминаются хранимые процедуры. В моем случае их нет. Не подскажите, распространяется ли это на параметризированные запросы?

К сожалению не осилил сделать это в SSMS. Не подскажите как это реализовать?

Я догадываюсь что он не знает. Чем ему помочь в сложившейся ситуации я пока не знаю. Повлиять на сам запрос явным образом(переписать его) не представляется возможным потому что запрос генерирует платформа 1С и разработчик на языке 1С не имеет возможности влиять на эти запросы. В большинстве случаев проблем нет, но бывают исключения. По-этому и ветка появилась.

Так и делаю. Но запрос все равно параметризируется. Или я не так интерпретирую то что вижу. скрин прикладываю.

[quot ErMiValRU#22413904]Аднаэснек,


Да, все делалось. Но для чистоты эксперимента повторил:
ALTER INDEX _AccumRg25415_1 ON _AccumRg25415 REBUILD
GO
ALTER INDEX _AccumRg25415_2 ON _AccumRg25415 REBUILD
GO
UPDATE STATISTICS _AccumRg25415 WITH FULLSCAN
GO
DBCC FREEPROCCACHE

Все еще скан.

Этот запрос генерируется платформой 1С. Я его не могу поменять равно как поменять структуру индексов. Как вариант добавить хинт через query store.

SELECT d.is_parameterization_forced
FROM sys.databases AS d
WHERE d.name = 'моя база'

Возвращает 0

План прикладываю

Да, я тоже заметил этот момент.
И таки не нашел ответа почему стоимость io 283, cpu 27 а стоимость скана 0,023

Сделал так:

SELECT
0x00000000000000000000000000000000,
MIN(T1._Period)
FROM dbo._AccumRg25415 T1 with (index= _AccumRg25415_1)
WHERE
(T1._RecorderTRef = 0x000001EB
AND T1._RecorderRRef = 0x9C575CB90199817511EC632194753AD9)
option (use hint('DISABLE_OPTIMIZER_ROWGOAL'))

План во вложении.

если подскажете как, желательно без рестарта сиквела, то с удовольствием соберу необходимую информацию.

Аднаэснек,

Ничего лучше не придумал как понижать уровень совместимости.
После установки уровня 130 (SQL 2016) план со сканированием стал сильно дороже и начался поиск по индексу.

Хотелось бы все таки разобраться как при уровне 150 у оптимизатора получалось 283+27 = 0.025

Владислав Колосов,

Я бы рад. Но не могу я хинты использовать и запрос изменить.
Для диагностики в SSMS могу, в "живых" запросах только через query store можно добавить.

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

Да и уровень совместимости вернул обратно. Сервер со среднего 20-30% по ЦП ушел в полку. Тоже не вариант.

Владислав Колосов,

В том смысле что я про данную вещь впервые услышал.
Спасибо! Буду разбираться.