Автор: Remus Rusanu, Understanding Hash, Sort and Exchange Spill events
Некоторые операции при выполнении запросов SQL Server рассчитаны на наилучшую производительность при использовании (относительно) большого объёма памяти в качестве промежуточного хранилища. Оптимизатор запросов выбирает план и оценивает стоимость, основываясь на том, что эти операторы используют эту «черновую» память. Но это, конечно, лишь оценка. Во время выполнения оценки могут оказаться неверными, и план должен продолжить работу, несмотря на нехватку памяти. В таком случае эти операторы выполняют сброс на диск (spill). Когда происходит сброс, «черновая» память сбрасывается в tempdb, и новые данные размещаются в (теперь) свободной памяти. Когда данные, сброшенные в tempdb, снова нужны, они читаются с диска. Само собой разумеется, сброс в tempdb на порядок медленнее, чем использование только «черновой» памяти. Мониторинг сбросов особенно важен в ETL-задачах, поскольку эти случаи могут растянуть выполнение ETL на многие минуты, а иногда даже часы. Для исчерпывающего обсуждения ETL, включая некоторые ссылки на сбросы, см. Руководство по производительности загрузки данных.
Событие предупреждения о хэше (Hash Warning)
Хэш-рекурсия возникает, когда входные данные для построения хэша не помещаются в доступную память, что приводит к разделению входных данных на несколько разделов, которые обрабатываются отдельно. Если какой-либо из этих разделов всё ещё не помещается в доступную память, он разделяется на подразделы, которые также обрабатываются отдельно. Этот процесс разделения продолжается до тех пор, пока каждый раздел не поместится в доступную память или пока не будет достигнут максимальный уровень рекурсии (отображается в столбце данных IntegerData).
Это один из самых распространённых сбросов. Хэш-соединение — любимец оптимизатора запросов, поскольку это очень быстрый оператор, который может эффективно соединять два неотсортированных источника, требуя одного прохода по каждому источнику. Кроме того, он может использоваться для удаления дубликатов (например, предложение DISTINCT) и группирующих агрегатов. См. Основные сведения о хэш-соединениях для получения более подробной информации.
К сожалению, он также требует много памяти. Сбросы хэша обычно указывают на плохие оценки кардинальности, которые, в свою очередь, чаще всего вызваны отсутствующей или устаревшей статистикой. Первое действие при обнаружении предупреждений о сбросе хэш-соединения — обновить (или создать) статистику по задействованным столбцам. Если проблема сохраняется, необходимо прибегнуть к другому типу соединения. Поскольку оптимизатор уже выбрал бы лучшее соединение, если бы мог, вам нужно посмотреть на проблему под другим углом: как помочь оптимизатору выбрать другое соединение без принудительного указания.
Оптимизатор выбрал бы LOOP (вложенный цикл), если бы мог выполнять поиск по внутренней стороне разумное количество раз, поэтому, возможно, вам нужен индекс на внутренней стороне для поиска и/или индекс на внешней стороне, который бы фильтровал выходные данные на раннем этапе, чтобы уменьшить количество зондирований внутренней стороны. См. Understanding Nested Loops Joins для получения более подробной информации.
Другая альтернатива — MERGE (соединение слиянием), но для него требуется, чтобы входные данные с обеих сторон были отсортированы. Добавление индекса к каждому из источников по обе стороны соединения, который гарантирует порядок, вероятно, склонит оптимизатор к использованию MERGE-соединения. См. Основные сведения о соединении вложенных циклов для получения более подробной информации.
Событие предупреждения о сортировке (Sort Warning)
Класс событий Sort Warnings указывает, что операции сортировки не помещаются в память. Это не включает операции сортировки, связанные с созданием индексов, только операции сортировки в рамках запроса (например, предложение ORDER BY, используемое в инструкции SELECT).
Если запрос требует гарантии порядка (например, у него есть предложение ORDER BY, или он проецирует такую функцию, как ROW_NUMBER), и нет индекса, гарантирующего порядок, то выбора мало: выполнение должно отсортировать входные данные перед продолжением. Если входные данные малы, сортировка происходит в памяти и очень дешева, но в этом случае предупреждение о сбросе не возникнет. Обычно оптимизатор запросов достаточно умён, чтобы отложить сортировку до завершения всей фильтрации, поэтому если сброс всё же происходит, это обычно означает, что больше нет фильтрации, которую можно применить. Решение при сбросах сортировки обычно заключается в добавлении покрывающего индекса, который обеспечивает желаемый порядок.
Другой случай предупреждения о сбросе сортировки — когда оптимизатор запросов проявляет творческий подход и добавляет сортировку в план, который технически не требует гарантии порядка. Однако такие события очень редки и экзотичны. Действия, которые необходимо предпринять, действительно зависят от конкретного случая.
Событие сброса обмена (Exchange Spill)
Класс событий Exchange Spill указывает, что буферы связи в плане параллельного запроса были временно записаны в базу данных tempdb. Это происходит редко и только тогда, когда план запроса имеет несколько диапазонных сканирований.
Вероятно, вы никогда не столкнётесь с этой проблемой. Если вы столкнётесь, я рекомендую ознакомиться с рекомендациями в связанной статье: Exchange Spill, класс событий.
Заключение
Технически существует ещё один класс сброса: сброс спула (spool spill). Поскольку спулы предназначены для сброса, наличие сброса спула обычно вызывает меньше беспокойства.
Цель этой статьи — показать, что в MS доступна обширная документация по этим событиям сброса. Сбросы в tempdb легко обнаруживаются и достаточно хорошо объяснены в документации продукта. Наличие сбросов может указывать на потенциальные проблемы с производительностью, поскольку сброс включает чтение и запись с диска и во много раз медленнее, чем соответствующая операция только в памяти. Они также создают дополнительную нагрузку на tempdb и могут вызывать конкуренцию.
Комментариев нет:
Отправить комментарий