Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Почему SQL Server игнорирует индекс в запросе  [new]
Max_11111
Member

Откуда:
Сообщений: 198
Не знаю почему (подозреваю что поломалась статистика), но изменился план выполнения для одного из запросов: вместо Index Seek стал выполняться Index Skan. Эту таблицу я постоянно загружаю в обмене, и из-за такой досадной неприятности обмен сломался.
Есть 2 таблицы: одна с данными (больше 1 млрд строк), другая содержит ключи с изменениями. Для получения изменившихся данных я соединяю эти 2 таблицы и получаю все изменившиеся строки.
Если изменений < 5000, либо при использовании LOOP JOIN, либо если принудительно указать индекс WITH (INDEX=) - то происходит Index Seek. Иначе - Index Skan.
Есть несколько аналогичных запросов к другим таблицам, схожим по структуре и объёму данных. С ними всё в порядке.
Кеш сервера сбрасывали, статистику для таблицы обновляли - ничего не помогло. Что ещё можно сделать чтобы оптимизатор снова правильно построил план?
23 авг 18, 10:48    [21651702]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
Max_11111
Member

Откуда:
Сообщений: 198


К сообщению приложен файл (Неправильный.sqlplan - 38Kb) cкачать
23 авг 18, 10:48    [21651703]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
Max_11111
Member

Откуда:
Сообщений: 198


К сообщению приложен файл (правильный.sqlplan - 119Kb) cкачать
23 авг 18, 10:49    [21651705]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
Max_11111
Member

Откуда:
Сообщений: 198
Правильный и неправильный планы запросов приложил. Не хочется принудительно указывать SQL как строить план запроса, хотелось бы чтобы он сам "догадался"
23 авг 18, 10:51    [21651712]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Max_11111,

т.е. вы не видите что вашего индекса не достаточно?
23 авг 18, 11:36    [21651775]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
Max_11111
Member

Откуда:
Сообщений: 198
TaPaK
Max_11111,
т.е. вы не видите что вашего индекса не достаточно?

Вы про то что не все поля содержатся в индексе? Так это и незачем. Index Seek + обращение по ключу выполняется мгновенно - если смотреть по времени. Почему SQL приписывает ему такую сложность - не понимаю
23 авг 18, 11:43    [21651784]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Max_11111
TaPaK
Max_11111,
т.е. вы не видите что вашего индекса не достаточно?

Вы про то что не все поля содержатся в индексе? Так это и незачем. Index Seek + обращение по ключу выполняется мгновенно - если смотреть по времени. Почему SQL приписывает ему такую сложность - не понимаю

sql не оперирует временем как ресурсом
23 авг 18, 11:45    [21651787]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
Max_11111
Member

Откуда:
Сообщений: 198
TaPaK,
В таком случае как дать понять SQL что сложность запроса с Index Seek действительно будет меньше, чем с Index Skan?
Пихать всю таблицу в каждый индекс (которых там штук 5) - точно не решение
23 авг 18, 11:48    [21651790]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Max_11111,

попробуйте FORCESEEK. Можно и WITH INDEX (не знаю решилась ли проблема с удалением индекса, вроде пофиксили) но это не самое здравое решение. По остальному не ясно, что на самом деле в actual plan
23 авг 18, 11:52    [21651795]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
Max_11111
Member

Откуда:
Сообщений: 198
TaPaK
Max_11111,
попробуйте FORCESEEK. Можно и WITH INDEX (не знаю решилась ли проблема с удалением индекса, вроде пофиксили) но это не самое здравое решение. По остальному не ясно, что на самом деле в actual plan

FORCESEEK построил "правильный план".
А что нужно сделать чтобы оптимизатор сам догадался без подсказок? У меня из идей пока только обновить статистику с полным сканированием таблицы. Но из-за размера таблица может зависнуть на долго, чего очень не хотелось бы
23 авг 18, 11:58    [21651807]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Max_11111
TaPaK
Max_11111,
попробуйте FORCESEEK. Можно и WITH INDEX (не знаю решилась ли проблема с удалением индекса, вроде пофиксили) но это не самое здравое решение. По остальному не ясно, что на самом деле в actual plan

FORCESEEK построил "правильный план".
А что нужно сделать чтобы оптимизатор сам догадался без подсказок? У меня из идей пока только обновить статистику с полным сканированием таблицы. Но из-за размера таблица может зависнуть на долго, чего очень не хотелось бы

посмотрите в актуальный план. Если в нём некритично расходится estimate / actual то статистика вам больше ничего не даст
23 авг 18, 12:01    [21651818]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
TaPaK
Max_11111
пропущено...

FORCESEEK построил "правильный план".
А что нужно сделать чтобы оптимизатор сам догадался без подсказок? У меня из идей пока только обновить статистику с полным сканированием таблицы. Но из-за размера таблица может зависнуть на долго, чего очень не хотелось бы

посмотрите в актуальный план. Если в нём некритично расходится estimate / actual то статистика вам больше ничего не даст
Явная ошибка в оценке; пишет, что запрос вернёт 170 миллиардов строк, а это вряд ли, т.к. ТС написал, что запрос "выполняется мгновенно".

Нужно попробовать сделать полное обновление, ну или оставить FORCESEEK
23 авг 18, 12:18    [21651847]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
alexeyvg
TaPaK
пропущено...

посмотрите в актуальный план. Если в нём некритично расходится estimate / actual то статистика вам больше ничего не даст
Явная ошибка в оценке; пишет, что запрос вернёт 170 миллиардов строк, а это вряд ли, т.к. ТС написал, что запрос "выполняется мгновенно".

Нужно попробовать сделать полное обновление, ну или оставить FORCESEEK

Ну
1. я не вижу 170 млрд.
2. вся таблица 1.2
3. что hash что nl даёт похозжие на правду цифры
4. Как вы так умеете гадать "запрос "выполняется мгновенно". -> "Явная ошибка в оценке"
23 авг 18, 12:26    [21651864]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
TaPaK
1. я не вижу 170 млрд.
Ой, миллионов.
TaPaK
3. что hash что nl даёт похозжие на правду цифры
4. Как вы так умеете гадать "запрос "выполняется мгновенно". -> "Явная ошибка в оценке"
Автор же пишет: "Index Seek + обращение по ключу выполняется мгновенно - если смотреть по времени"
Если бы запрос действительно возвращал бы 170 млн строк, он бы так не говорил?

Ну и да, про оценку статистики я погорячился, в общем, сервер почему то выбирает неправильный план.
23 авг 18, 12:44    [21651903]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 308
Меня вероятно запинают, но все ж интересно.
Может быть, что SQL Server по какой-то причине стал полагать SORT слишком тяжеловесным? Изменится ли план, если внутри CTE заранее отсортировать данные? Ну с чем-то типа TOP 10000000
23 авг 18, 15:19    [21652155]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Glebanski
Меня вероятно запинают, но все ж интересно.
Может быть, что SQL Server по какой-то причине стал полагать SORT слишком тяжеловесным? Изменится ли план, если внутри CTE заранее отсортировать данные? Ну с чем-то типа TOP 10000000

абстрактные вопросы? это всегда весело...
1. Что значить посчитал "тяжеловесным" и что выбросил?
2. Самостоятельно (без явных ORDER BY) добавляет во многих случаях и может убирать в зависимости от текущего положения дел (когда план строит)
23 авг 18, 15:26    [21652164]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Max_11111
Правильный и неправильный планы запросов приложил. Не хочется принудительно указывать SQL как строить план запроса, хотелось бы чтобы он сам "догадался"
Вы хотите чтобы мы вам сказали где и как сервер ошибается в эстимейтах по оценочным планам? Где актуальные то? Вот эти 82 тысячи из 200 тысячной таблицы это насколько далеко от реальности?

В видели что у вас нет нормального поиска по [dns_m].[dbo].[_InfoRg4781]. [_Fld4784]=(0.) ? И вообще, 0 тут это константа или может меняться?

Какое из этих условий наиболее селективное? И есть ли среди этих значений константы?
		WHERE
			НомерСообщения = 0
			AND Узел_ТипМета = 0x08
			AND Узел_ТипСсылки = 0x00004168
			AND Узел = 0xB71700155D03330711E65847D2B9B30E

Сервер ошибается на выборке из этой таблицы или на джойне?
Если первое, то можно попробовать создать правильные многоколоночные статистики с полным сканом, или если возможно то фильтрованные. Второй вариант - селектить chng_gr во временную таблицу и потом джойнить.
Если проблема в джойне (маловероятно), то это может быть из-за того что соединение по 2м полям. Сервер плохо перевиривает такие джойны. Тут или попробовать создать вручную стастистику по обоим полям на обеих таблицах в обе стороны с полным сканом, либо переходить на 2014, там с этим вроде получше.
23 авг 18, 21:58    [21652490]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Glebanski
Меня вероятно запинают, но все ж интересно.
Может быть, что SQL Server по какой-то причине стал полагать SORT слишком тяжеловесным? Изменится ли план, если внутри CTE заранее отсортировать данные? Ну с чем-то типа TOP 10000000
То есть стоимость 170 миллионов лукапов вас вообще не смутила?
23 авг 18, 21:59    [21652494]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Mind,

Зачем я это прочитал... Что это за поток мыслей, если не сказать похуже.
23 авг 18, 22:07    [21652501]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
TaPaK
Mind,

Зачем я это прочитал... Что это за поток мыслей, если не сказать похуже.
Не понимаете о чем речь - не читайте. Вы что-то ничего дельного вообще не сказали.
TaPaK
3. что hash что nl даёт похозжие на правду цифры
Вы наверное телепат и знаете всю правду? Даже без актуальных планов. Данных по-оценке стало чуть-чуть больше - запрос сломался. Классика.
Ошибка явно в оценках, потому что 170 миллионов из миллиардной таблицы это явно много для эффективного поиска и лукапа. Мне интересно как он вообще умудрялся выбирать правильный план при таких съехавших оценках.
23 авг 18, 23:12    [21652542]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Mind
TaPaK
Mind,

Зачем я это прочитал... Что это за поток мыслей, если не сказать похуже.
Не понимаете о чем речь - не читайте. Вы что-то ничего дельного вообще не сказали.
TaPaK
3. что hash что nl даёт похозжие на правду цифры
Вы наверное телепат и знаете всю правду? Даже без актуальных планов. Данных по-оценке стало чуть-чуть больше - запрос сломался. Классика.
Ошибка явно в оценках, потому что 170 миллионов из миллиардной таблицы это явно много для эффективного поиска и лукапа. Мне интересно как он вообще умудрялся выбирать правильный план при таких съехавших оценках.

>10% от таблицы NL это по вашему не верно? Ещё раз, ваши фантазии про промахивание оценки могут быть и верны, влияние на план 0 не факт
23 авг 18, 23:19    [21652547]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
nvv
Member

Откуда:
Сообщений: 54
Max_11111,
1c обмен на sql? интересно.
"Правильный" - вообще не правильный. 1,6млрд циклов. Сколько каждый выполняется?

80000 изменений в РС - терпимо. Что если попробовать получить по каждому регистратору мин и макс периода и наложить условие
по периоду + регистратору? Теперь кластерный возможно задействуется, без NL
23 авг 18, 23:33    [21652550]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
Max_11111
Member

Откуда:
Сообщений: 198
TaPaK
посмотрите в актуальный план. Если в нём некритично расходится estimate / actual то статистика вам больше ничего не даст

Критично. Запрос возвращает порядка 50000 строк, при этом оценочное количество на 4 порядка выше!

alexeyvg
Нужно попробовать сделать полное обновление, ну или оставить FORCESEEK

Тоже к этому склоняюсь, как к самому простому что можно сделать. Надеюсь поможет.
24 авг 18, 02:33    [21652578]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
Max_11111
Member

Откуда:
Сообщений: 198
Glebanski
Меня вероятно запинают, но все ж интересно.
Может быть, что SQL Server по какой-то причине стал полагать SORT слишком тяжеловесным? Изменится ли план, если внутри CTE заранее отсортировать данные? Ну с чем-то типа TOP 10000000

В меньшую сторону оценка может сдвинуться, а вот в большую - скорее всего нет. Как я понимаю:
- если оптимизатор оценивает объём выборки в 100 строк, а вы говорите TOP 1000 - то оптимизатор проигнорирует это значение, ведь 100 - это максимум, который он ожидает.
- Если оптимизатор даёт оценку в 1000 строк, а вы пишите TOP 100 - то оптимизатор с радостью изменит оценку на 100 строк.
24 авг 18, 02:42    [21652581]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server игнорирует индекс в запросе  [new]
Max_11111
Member

Откуда:
Сообщений: 198
Mind
Вы хотите чтобы мы вам сказали где и как сервер ошибается в эстимейтах по оценочным планам?

В идеале - да :)

Mind
В видели что у вас нет нормального поиска по [dns_m].[dbo].[_InfoRg4781]. [_Fld4784]=(0.) ? И вообще, 0 тут это константа или может меняться?
Какое из этих условий наиболее селективное? И есть ли среди этих значений константы?
		WHERE
			НомерСообщения = 0
			AND Узел_ТипМета = 0x08
			AND Узел_ТипСсылки = 0x00004168
			AND Узел = 0xB71700155D03330711E65847D2B9B30E

Все условия передаются в SQL уже константами, т.е. это не параметризованный запрос.
Наиболее селективно, наверное, условие на узел, но при чем тут этот запрос? Тут SQL всё верно оценивает - запрос полностью ложится на индекс и оценка строк совпадает с фактической выборкой

Mind
Сервер ошибается на выборке из этой таблицы или на джойне?
Если первое, то можно попробовать создать правильные многоколоночные статистики с полным сканом, или если возможно то фильтрованные. Второй вариант - селектить chng_gr во временную таблицу и потом джойнить.
Если проблема в джойне (маловероятно), то это может быть из-за того что соединение по 2м полям. Сервер плохо перевиривает такие джойны. Тут или попробовать создать вручную стастистику по обоим полям на обеих таблицах в обе стороны с полным сканом, либо переходить на 2014, там с этим вроде получше.

Ошибка на JOIN.
Временная таблица не поможет, т.к. запрос вызывается в SSIS потоке данных, а там временные таблицы запрещены. Табличная переменная решила бы эту проблему, т.к. оптимизатор всегда считает что в ней 1 строка - поэтому план бы строил с Index Seek.
База крутится на 2014 SQL.
Статистику обновим, скорее всего на выходных
24 авг 18, 02:54    [21652582]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить