Краткий ответ

Spill означает, что оператор сортировки не смог полностью разместить рабочие данные в выделенной памяти и записал промежуточные результаты во временное хранилище. Обычно причина — недостаточный memory grant из-за ошибки оценки числа строк или их ширины, но spill также возможен из-за ограничений памяти и конкуренции запросов.

Исторический контекст

Сортировка больших наборов данных изначально проектировалась не только для работы в оперативной памяти. Когда набор не помещается в доступную память, СУБД разбивает его на части, сохраняет их во временные файлы, а затем выполняет внешнее слияние.

Такой подход позволяет завершить запрос при ограниченной памяти, но переносит часть работы на дисковое или иное временное хранилище. В современных СУБД это проявляется, например, как предупреждение о spill, внешняя сортировка или использование временных рабочих файлов.

Постановка проблемы

Перед выполнением сортировки оптимизатор оценивает количество строк и среднюю ширину строки, после чего оператор получает memory grant. Если фактический объём оказывается больше доступной рабочей памяти, сортировка начинает сбрасывать промежуточные данные во временное хранилище.

Это увеличивает задержку из-за дополнительных операций записи и чтения, а также может создать нагрузку на tempdb в SQL Server или временные файлы в PostgreSQL. При одновременном запуске многих запросов spill способен вызвать конкуренцию за ввод-вывод и ухудшить производительность всей системы.

Подробное решение

Сортировка обычно работает в памяти, пока помещает туда свои рабочие структуры. При нехватке памяти она формирует отсортированные части, записывает их во временное хранилище, а затем сливает эти части в итоговый порядок; при тяжёлом spill возможны несколько проходов слияния.

Наиболее частая причина — неверная оценка кардинальности. Если оптимизатор ожидает мало строк, он выдаёт небольшой memory grant, а фактический результат оказывается значительно больше. Ошибка также возникает, когда недооценена ширина строк: например, запрос сортирует широкие строки, хотя для результата нужны лишь несколько столбцов.

Spill не обязательно означает устаревшую статистику. Memory grant может быть ограничен настройками, уменьшен из-за конкуренции за память или рассчитан корректно, но оказаться недостаточным для фактического пикового потребления конкретного алгоритма. Поведение и диагностические признаки зависят от СУБД: в SQL Server анализируют предупреждения оператора и фактический план, а в PostgreSQL — способ сортировки и объём временных файлов.

Исправление выбирают по причине:

исправляют оценки кардинальности и обновляют статистику, если проблема вызвана неверным прогнозом;
уменьшают объём сортируемых данных, перенося фильтрацию раньше и не передавая лишние столбцы;
создают индекс, порядок ключей которого уже соответствует фильтрации и сортировке, если это оправдано чтениями;
настраивают доступную рабочую память осторожно, учитывая число одновременно выполняющихся операторов;
используют адаптивные механизмы конкретной СУБД, например обратную корректировку memory grant, если она доступна.

Безусловное увеличение памяти или принудительное устранение одного spill может ухудшить систему: крупные запросы начнут резервировать больше памяти, а множество параллельных запросов столкнётся с её дефицитом. Индекс тоже не является универсальным решением: он увеличивает стоимость записи и хранения и может не устранить сортировку при несовпадающем порядке ключей или необходимости дополнительной обработки.

Ситуация из практики

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

Рассматривались три варианта. Увеличение рабочей памяти было быстрым, но создавало риск для параллельных отчётов. Принудительное использование индекса уменьшало сортировку в одном варианте распределения данных, но делало план нестабильным при изменении фильтра. Обновление статистики и уменьшение ширины сортируемого набора требовали проверки запроса, зато устраняли причину ошибки оценки.

Выбрали обновление статистики, фильтрацию до сортировки и чтение только нужных столбцов. После этого memory grant стал ближе к фактической потребности, временные записи исчезли, а решение не зависело от глобального увеличения лимита памяти.

Что кандидаты часто упускают

Доказывает ли spill, что в запросе обязательно отсутствует подходящий индекс?

Нет. Индекс может устранить сортировку только тогда, когда его порядок совместим с условиями доступа и требуемым порядком результата. Spill может происходить в другой сортировке, например после соединения или группировки, либо при сортировке широкого промежуточного набора. Сначала определяют конкретный оператор и причину его большого объёма данных, а не добавляют индекс по одному признаку spill.

Почему увеличение доступной памяти не всегда устраняет проблему производительности?

Память выделяется не одному запросу навсегда, а конкурирующим операторам и сеансам. Если каждый запрос получает крупный grant, система может одновременно держать меньше запросов, начать ожидать выдачи памяти или вытеснять другие полезные данные из кэша. Поэтому увеличение лимита оправдано только после проверки оценок, фактического потребления и параллельной нагрузки.

Может ли spill возникнуть при формально правильной оценке числа строк?

Да. Оценка количества строк может быть близкой к фактической, но ширина строк, внутренние структуры алгоритма, ограничение grant или конкуренция за память могут привести к нехватке рабочего пространства. Кроме того, некоторые операторы потребляют память пиково, а планировщик оценивает её по модели, которая не отражает каждый фактический промежуточный объём. Поэтому анализируют не только кардинальность, но и фактическую ширину данных, grant, доступную память и нагрузку на временное хранилище.