План запроса SQL. Index Seek выполняется много раз и возвращает больше строк чем есть в таблице.
Здравствуйте!
Помогите понять возникающую ситуацию. Есть задача - отчет для руководства. Есть постановщики задачи, которые меняются раз в полгода, год. Соответственно задача постоянно меняется. В связи с этим принято решение написать отчет так, чтобы можно было его достаточно быстро дорабатывать и он выполнялся в приемлемое время (до 2-х минут в зависимости от настроек).
Так вот с недавнего момента отчет, можно сказать, перестал работать. Один из запросов, который выполняется в цикле, начал вести себя странно, то выполняется за доли секунды, то выполняется 30 сек. Было принято решение посмотреть план запроса. В результате возникли вопросы, на которые хочется найти ответ, а работа с интернетом не дала результатов))).
К технической стороне:
Есть регистр сведений, в котором около 1 млн записей и каждый день происходит добавление около 1000 записей.
Есть запрос (в коде выполняется в цикле). Его суть - получить дату последней актуальной информации в регистре по контрагенту. В регистре есть еще измерения "Договор", "ИдентификаторДокумента".
"ВЫБРАТЬ ПЕРВЫЕ 1
| ПросроченнаяДебиторскаяЗадолженностьСрезПоследних.Период КАК Период,
| ПросроченнаяДебиторскаяЗадолженностьСрезПоследних.Организация,
| ПросроченнаяДебиторскаяЗадолженностьСрезПоследних.Контрагент
|ИЗ
| РегистрСведений.ПросроченнаяДебиторскаяЗадолженность.СрезПоследних(
| &Период,
| Организация = &Организация
| И Контрагент = &Контрагент) КАК ПросроченнаяДебиторскаяЗадолженностьСрезПоследних
|
|УПОРЯДОЧИТЬ ПО
| Период УБЫВ";
Когда запрос выполняется быстро - файл "План запроса быстро".
Когда запрос выполняется медленно - файл "План запроса медленно".
В двух словах при быстром выполнении Index Seek выполняется 1 раз и возвращает несколько сотен записей в зависимости от переданных параметров!
При медленном выполнении Index Seek выполняется, в приведенном примере, 3057 раз и возвращает > 26 млн записей! Хотя в таблице всего около 1 млн записей. Может кто подскажет, почему так происходит?
Помогите понять возникающую ситуацию. Есть задача - отчет для руководства. Есть постановщики задачи, которые меняются раз в полгода, год. Соответственно задача постоянно меняется. В связи с этим принято решение написать отчет так, чтобы можно было его достаточно быстро дорабатывать и он выполнялся в приемлемое время (до 2-х минут в зависимости от настроек).
Так вот с недавнего момента отчет, можно сказать, перестал работать. Один из запросов, который выполняется в цикле, начал вести себя странно, то выполняется за доли секунды, то выполняется 30 сек. Было принято решение посмотреть план запроса. В результате возникли вопросы, на которые хочется найти ответ, а работа с интернетом не дала результатов))).
К технической стороне:
Есть регистр сведений, в котором около 1 млн записей и каждый день происходит добавление около 1000 записей.
Есть запрос (в коде выполняется в цикле). Его суть - получить дату последней актуальной информации в регистре по контрагенту. В регистре есть еще измерения "Договор", "ИдентификаторДокумента".
"ВЫБРАТЬ ПЕРВЫЕ 1
| ПросроченнаяДебиторскаяЗадолженностьСрезПоследних.Период КАК Период,
| ПросроченнаяДебиторскаяЗадолженностьСрезПоследних.Организация,
| ПросроченнаяДебиторскаяЗадолженностьСрезПоследних.Контрагент
|ИЗ
| РегистрСведений.ПросроченнаяДебиторскаяЗадолженность.СрезПоследних(
| &Период,
| Организация = &Организация
| И Контрагент = &Контрагент) КАК ПросроченнаяДебиторскаяЗадолженностьСрезПоследних
|
|УПОРЯДОЧИТЬ ПО
| Период УБЫВ";
Когда запрос выполняется быстро - файл "План запроса быстро".
Когда запрос выполняется медленно - файл "План запроса медленно".
В двух словах при быстром выполнении Index Seek выполняется 1 раз и возвращает несколько сотен записей в зависимости от переданных параметров!
При медленном выполнении Index Seek выполняется, в приведенном примере, 3057 раз и возвращает > 26 млн записей! Хотя в таблице всего около 1 млн записей. Может кто подскажет, почему так происходит?
Прикрепленные файлы:
План запроса быстро.txt
План запроса медленно.txt
По теме из базы знаний
Ответы
Подписаться на ответы
Инфостарт бот
Сортировка:
Древо развёрнутое
Свернуть все
(29)
Дмитрий, помимо (33), я бы ещё глянул регламентное задание по обновлению статистики, выполняется ли и как часто.
Поскольку один и тот же запрос выполняется по разному, значит такой план выбирает планировщик.
Планировщик опирается на статистику.
Сброс процедурного кэша при чудесах в планах так же полезен, это команда DBCC FREEPROCCACHE - выполняется очень быстро, можно прям "наживую".
Приведите полную версию SQL, выполнив запрос select @@version, пожалуйста. Надо почитать есть ли в обновлениях для этой версии исправления ошибок по выбору плана.
Дмитрий, помимо (33), я бы ещё глянул регламентное задание по обновлению статистики, выполняется ли и как часто.
Поскольку один и тот же запрос выполняется по разному, значит такой план выбирает планировщик.
Планировщик опирается на статистику.
Сброс процедурного кэша при чудесах в планах так же полезен, это команда DBCC FREEPROCCACHE - выполняется очень быстро, можно прям "наживую".
Приведите полную версию SQL, выполнив запрос select @@version, пожалуйста. Надо почитать есть ли в обновлениях для этой версии исправления ошибок по выбору плана.
(36)
Вадим, так как 1С это почти всегда OLTP нагрузка (много маленьких транзакций на запись и чтение), поэтому почти всегда проигрыш на объединении результата распараллеленного запроса больше, чем выигрыш по скорости его выполнения относительно 1 ядра.
Например на 1 ядре запрос выполняется 1 сек
На 2-х ядрах 0,7 сек и на объединение результата этих 2-х ядер уходит ещё 0,5 сек, получаем проигрыш
Опять же, в каждой системе надо это тестировать, но общая рекомендация Max Degree of Parrallelism = 1
Вадим, так как 1С это почти всегда OLTP нагрузка (много маленьких транзакций на запись и чтение), поэтому почти всегда проигрыш на объединении результата распараллеленного запроса больше, чем выигрыш по скорости его выполнения относительно 1 ядра.
Например на 1 ядре запрос выполняется 1 сек
На 2-х ядрах 0,7 сек и на объединение результата этих 2-х ядер уходит ещё 0,5 сек, получаем проигрыш
Опять же, в каждой системе надо это тестировать, но общая рекомендация Max Degree of Parrallelism = 1
(38) Плюсом ко всему выше сказанному.
1С генерит запросы по шаблону и не нет возможности их оптимизировать под конкретные потребности. как в системах где запросы пишутся на прямую к серверу БД.
И есть очень не маленькие шансы в таких случаях когда параллельные ветки одного запроса застревают на блокировках между собой.
1С генерит запросы по шаблону и не нет возможности их оптимизировать под конкретные потребности. как в системах где запросы пишутся на прямую к серверу БД.
И есть очень не маленькие шансы в таких случаях когда параллельные ветки одного запроса застревают на блокировках между собой.
(41)Для понимания:
Указывать MDOP = 1 - по крайне мере глупо, ИМХО.
Цитата1 |
|---|
|
Само по себе параллельное выполнение запросов не является проблемой, но тут есть несколько нюансов, связанных с вполне конкретной областью применения. 1. В SQL 2000 были некоторые проблемы с распараллеливанием запросов (см. статью "Использование опции "max degree of parallelism" в Microsoft SQL Server 2000" на диске ИТС 1С) 2. Параллельные планы обычно всплывают, когда выполняется скан таблицы или индекса. Скан таблицы или индекса обычно свидетельствует о неэффективном выполнении запроса. Чаще всего это бывает из-за одной из следующих причин: - отсутствие (невозможность использования) нужного индекса - низкая селективность "нужного" индекса - сильная фрагментированность / низкая "плотность" заполнения страниц индекса - неактуальная статистика нужного индекса При наличии этих проблем (которые, безусловно, требуют решения) построенный параллельный план часто бывает медленнее нормального плана в разы и накладывает больше блокировок, чем ожидалось. Применительно к 1С это часто бывает после массовых перепроведений задним числом. 3. Генерируемый платформой 1С запрос на получение остатков/оборотов по регистру накопления/бухгалтерии написан так, что он эффективно работает с использованием существующих индексов, но очень неэффективно в случаях, когда распараллеливается. А структура данных такова, что при определённых условиях (например, при массовом исправлении документов прошлых периодов) этот запрос часто "сваливается" в параллельность. Предпослыки этого примерно такие: - много документов и движений в последний день месяца (неравномерно распределена статистика) - перепроведение документов приводит к сильной фрагментации индексов - переформирование движений по партионному учету приводит к большому количеству нулевых записей, которые приводят к низкой селективности индекса по итогам - расчет остатков на середину периода обычно выполняется как разница между остатком на конец периода (месяца) и движениями меджу датой остатков и концом периода (и тут-то все три вышеприведённые предпосылки заходят с козырей) 4. В 1С 8.0 (не 8.1) на многих таблицах не было кластерных индексов. Это приводило к очень "специфичным" планам выполнения после большого количества вставок/удалений. Из-за этих особенностей обычно бывает полезным (мой опыт подсказывает) повысить "cost threshold for parallelism" на сервере с БД 1С до 100-10000. Но это ни в коем случае не панацея, а скорее "обезбаливающее". Это позволит в "плохих" случаях сохранить планы запросов, которые используют индексы, даже если эти индексы находятся в плохом состоянии. Ну и само по себе это к "все остальное встанет колом" имеет достаточно слабое отношение. Если есть запрос, который распараллеливается, то: а) отловите его при помощи SQL Profiler, б) по возможности упростите его так, чтобы параллельность не пропала в) приведите его и структуру используемых таблиц, индексы этих таблиц г) приведите результаты DBCC SHOWCONTIG WITH TABLERESULTS для этих таблих. Тогда, скорее всего, можно будет что-то посоветовать, чтобы этот запрос выполнялся эффективнее |
Цитата2 |
|---|
|
Запрос для вычисления остатков обычно имеет такую схему (это я сильно упростил):
SEL ECT [Измерения], [Ресурсы]
FR OM (SEL ECT [Измерения], SUM([Ресурсы]) [Ресурсы]
FR OM
(
SELECT [Измерения], [Ресурсы]
FR OM [Итоги]
WH ERE [Период] = @КонецМесяца AND <УсловиеНаИзмерения>
UNI ON ALL
SEL ECT [Измерения],
(CASE WHEN [ЭтоПриход] THEN -[Ресурсы]
WHEN NOT [ЭтоПриход] THEN [Ресурсы]
ELSE 0 END) [Ресурсы]
FR OM [Движения]
WH ERE [Период] >= @ДатаОстатков AND [Период] < @КонецМесяца AND
[ДвижениеАктивно] = 0x01 AND <УсловиеНаИзмерения>
) T
GROUP BY [Измерения]
HAVING SUM([Ресурсы]) <> 0)
) T
ПоказатьСтруктура таблицы [Итоги]: [Период] - DATETIME - границы месяцев или {ts '3999-11-01 00:00:00'} [Измерения] - одно или несколько полей (чаще всего BINARY(16), значения которых генерируются сервером приложений "примерно по возрастанию") [Ресурсы] - одно или несколько полей NUMERIC (N,M) Индексы таблицы [Итоги]: Кластерный - [Период], [Измерения] Структура таблицы [Движения]: [Период] - DATETIME - Дата и время движений (до секунд) [Регистратор_Тип] - BINARY(4) - ссылка на тип документа документ, создавший движения [Регистратор_Ссылка] - BINARY(16) - ссылка на документ, создавший движения [НомерСтроки] - NUMERIC (9,0) - номер строки документа-регистратора [ЭтоПриход] - признак приходного или расходного движения (в индексы не входит, поэтому тип не указываю) [ДвижениеАктивно] - признак того, что движение "включено" (отключение используется крайне редко) [Измерения] - как и в итогах [Ресурсы] - как и в итогах [Реквизиты] - 0 или несколько полей не участвующие в вычислении итогов Индексы таблицы [Движения]: Кластерный, уникальный - [Период], [Регистратор_Тип], [Регистратор_Ссылка], [НомерСтроки] Уникальный - [Регистратор_Тип], [Регистратор_Ссылка], [НомерСтроки] Уникальные - [ИзмерениеN], [Регистратор_Тип], [Регистратор_Ссылка], [НомерСтроки] - индексы по некоторым измерениям (настраиваются "конфигурастом") Самые частые проблемы этой схемы: 1. "Конденсация" движений в конец месяца Многие регламентные операции формируют записи в таблице движений последним днём месяца. Это приводит к тому, что в этот день может быть до 70% движений месяца. В таких условиях кластерный индекс движений по периоду может оказаться очень неселективным (особенно для "молодой" базы). Обычно лечится ребилдом индекса, пересчетом статистики. В современных версиях 1С появился режим, когда итоги считаются наоборот от начала к концу, что может быть применено для таких таблиц. 2. Избыточное хранение итогов. При удалнении строк из таблицы движений строки таблицы итогов не удаляются. Просто значение ресурсов становится 0. Это может привести к тому, что таблица итогов на 90% состоит из 0. Особенно часто эта ситуация возникает для регистров учета партий с изменениями типа <Склад, Товар, Партия> при изменениях приходов задним числом. Лечится регламентным (например, раз в месяц) пересчетом итогов и минимизацией исправлений задним числом. 3. Перепроведение документов. При перепроведении документов записи в таблице движений сначала удаляются, а затем записываются. Если это делать задним числом, то кластерный индекс от регулярных удалений из середины с последующими вставками в середину "протухает" напрочь. За кластерным индексом благополучно летят и остальные. Лечение - ребилд индекса, минимизация исправлений задним числом. 4. Прочие проблемы: - Если <УсловиеНаИзмерения> использует низкоселективные или сильно фрагментированные индексы по измерениям (движений), то план становится нестабильным. - Если <УсловиеНаИзмерения> использует фильтр не по всем измерениям от начала, то индекс по итогам тоже может не работать. - <УсловиеНаИзмерения> в некоторых случаях может содержать выражение типа ([ИзмерениеN] IN (SEL ECT t FR OM #tt)), где #tt - непроиндексированная временная таблица. В этом случае оптимизатор часто генерирует план со сканом, нестед лупсами, спул тэйблом и распараллеливанием. Да еще и на обе таблицы (итоги и движения). Превед, производительность.... - И уж я молчу о планах выполнения, если всю вышеприведённую конструкцию начинаем джойнить с какой-нибудь подобной вымученной конструкцией... И соответственно при выборе между планами - "распараллелить, сканировать всё, фильтровать, сложить" и "выбрать по индексу, найти в кластерном индексе, дофильтровать, сложить" оптимизатор может легко ошибиться (особенно если статистика неактуальна) и выбрать первый вариант. Основные рекомендации: 1. Следить за фрагментацией индексов (особенно после больших перепроведений и закрытий месяца) 2. Расчитывать и пересчитывать итоги. 3. Следить за тем, как написаны запросы. 4. Минимизировать исправления задним числом. |
Указывать MDOP = 1 - по крайне мере глупо, ИМХО.
(43)Да, конечно
Но, по умолчанию в ms sql стоит значение 0, т.е. использовать все доступные ядра.
И мы можем получить ситуацию, когда один тяжелый запрос, длительностью выше порога (5 сек) просто положит весь сервер и остальные пользователи встанут в очередь.
Для oltp это плохой сценарий.
Можно долго об этом спорить, но:
1. Это является рекомендаций Фирмы 1С
2. Все большие и нагруженные системы нужно тестировать и принимать взвешенные решения по настройкам
Но, по умолчанию в ms sql стоит значение 0, т.е. использовать все доступные ядра.
И мы можем получить ситуацию, когда один тяжелый запрос, длительностью выше порога (5 сек) просто положит весь сервер и остальные пользователи встанут в очередь.
Для oltp это плохой сценарий.
Можно долго об этом спорить, но:
1. Это является рекомендаций Фирмы 1С
2. Все большие и нагруженные системы нужно тестировать и принимать взвешенные решения по настройкам
(1)
Дело в том что выбираете параметр Период из виртуальной таблицы, которой нету в явно рассчитанном виде.
надо писать так
"ВЫБРАТЬ ПЕРВЫЕ 1
| ПросроченнаяДебиторскаяЗадолженностьСрезПоследних.Период КАК Период,
| ПросроченнаяДебиторскаяЗадолженностьСрезПоследних.Организация,
| ПросроченнаяДебиторскаяЗадолженностьСрезПоследних.Контрагент
|ИЗ
| РегистрСведений.ПросроченнаяДебиторскаяЗадолженность.СрезПоследних(
| &Период,
| Организация = &Организация
| И Контрагент = &Контрагент) КАК ПросроченнаяДебиторскаяЗадолженностьСрезПоследних
|
|УПОРЯДОЧИТЬ ПО
| Период УБЫВ";
| ПросроченнаяДебиторскаяЗадолженностьСрезПоследних.Период КАК Период,
| ПросроченнаяДебиторскаяЗадолженностьСрезПоследних.Организация,
| ПросроченнаяДебиторскаяЗадолженностьСрезПоследних.Контрагент
|ИЗ
| РегистрСведений.ПросроченнаяДебиторскаяЗадолженность.СрезПоследних(
| &Период,
| Организация = &Организация
| И Контрагент = &Контрагент) КАК ПросроченнаяДебиторскаяЗадолженностьСрезПоследних
|
|УПОРЯДОЧИТЬ ПО
| Период УБЫВ";
Дело в том что выбираете параметр Период из виртуальной таблицы, которой нету в явно рассчитанном виде.
надо писать так
ВЫБРАТЬ ПЕРВЫЕ 1
| ПросроченнаяДебиторскаяЗадолженностьСрезПоследних.Организация,
| ПросроченнаяДебиторскаяЗадолженностьСрезПоследних.Контрагент
|ИЗ
| РегистрСведений.ПросроченнаяДебиторскаяЗадолженность.СрезПоследних(
| &Период,
| Организация = &Организация
| И Контрагент = &Контрагент) КАК ПросроченнаяДебиторскаяЗадолженностьСрезПоследних
| ";
Показать
Вообще конечно маловероятно что запрос косячит в SQL Server.
Нет ли у вас под рукой вероятного злоумышленника.
Может быть все не так просто, как кажется. Записи дополнительные могут добавляться по ходу работы.
Вы заметили что планы запросов в одном и другом случае совершенно одинаковы.
Значит все сводится к тому, что данные разные.
По крайней мере это видно из ваших планов запросов
Нет ли у вас под рукой вероятного злоумышленника.
Может быть все не так просто, как кажется. Записи дополнительные могут добавляться по ходу работы.
Вы заметили что планы запросов в одном и другом случае совершенно одинаковы.
Значит все сводится к тому, что данные разные.
По крайней мере это видно из ваших планов запросов
(53) В примерах планов запросов, которые я привел, отличается только параметр "контрагент". Но когда проводил тестирование весь отчет мог выполняться около 30 сек в течение всего дня и по части планов запросов отличалось только количество возвращаемых строк Index seek (100-300 строк в зависимости от параметра "контрагент"). Потом резко начинал тупить и количество возвращаемых строк Index seek (2 млн - 30 млн строк в зависимости от параметра "контрагент"). Сначала пробовал перегружать агента SQL, помогало на день работы, потом понял что дело в чем то другом и полез в планы смотреть и т.п.
(27) Вы поймите , вокруг очень много неясных вопросов, если заниматься каждым из них, можно вообще никогда не решить поставленную задачу.
Вам тут именно это пытаются донести.
Вы скорее всего пошли тупиковым путем, и уперлись в стену. Не нужно до помутнения рассудка разбираться кто построил стену и из чего она сделана, стоит просто вернутся и попробовать пойти другим путем. Возможно так вы доберетесь быстрее.
Если бы вы описали свою изначальную задачу , возможно кто-то смог бы помочь более продуктивно...
Вам тут именно это пытаются донести.
Вы скорее всего пошли тупиковым путем, и уперлись в стену. Не нужно до помутнения рассудка разбираться кто построил стену и из чего она сделана, стоит просто вернутся и попробовать пойти другим путем. Возможно так вы доберетесь быстрее.
Если бы вы описали свою изначальную задачу , возможно кто-то смог бы помочь более продуктивно...
Для получения уведомлений об ответах подключите телеграм бот:
Инфостарт бот