По материалам статьи Craig Freedman

В этой статье будет дан краткий обзор трех интересных свойств итераторов, которые влияют на исполнение запроса: использование памяти, отсутствие или наличие блокировок и поддержка динамических курсоров.

Память

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

Содержащий потребляющие память итераторы запрос часто ожидает выделения ему необходимой памяти и, если сервер, выполняющий в это время другие такие же запросы, не имеет достаточного количества доступной памяти, это может напрямую повлиять на работу запроса, в виде задержек в его исполнении.
Если конкурирующих за ограниченные ресурсы памяти сервера запросов слишком много, все они могут пострадать от ухудшения параллелизма и/или производительности. Это не является критичной проблемой для информационных хранилищ, но нежелательно для OLTP-систем.
Даже если потребляющий память итератор запрашивает не много памяти, в течение выполнения может возникнуть необходимость сохранения данных на диске. Такой сброс данных на диск может негативно повлиять на производительность запроса и работу системы из-за дополнительной нагрузки ввода-вывода. Кроме того, если итератор сбрасывает слишком много данных, это может привести к исчерпанию ресурсов tempdb и сбою в работе.

Потребляющие оперативную память итераторы, это: сортировка, хэш-агрегация и хэш-соединение.

Неблокирующие и блокирующие итераторы

Большинство итераторов можно разделить на две категории:

Итераторы, получающие на входе строки данных и одновременно поставляющие строки на выходе (метод GetRow). Такие итераторы принято называть неблокирующими.
Итераторы, которые вначале получают на вход строки (обычно это метод Open), а потом уже генерируют какой-нибудь набор строк на выходе. Эти итераторы принято называть блокирующими или стоп-энд-гоу итераторами.

Итератор Compute Scalar является наиболее простым примером неблокирующего итератора. Он считывает строку на входе и вычисляет новое значение для вывода. Для каждого вычисления используются входные значения текущей строки, после че��о Compute Scalar сразу отдаёт на выход новое значение, переходя к обработке следующей строки на входе.
Итератор Sort не плохой пример блокирующего итератора. Sort не может определить первую строку для вывода, пока он не прочитает и не отсортирует все строки на входе (последняя строка на входе может оказаться первой строкой на выходе; нет никакого способа узнать это, пока не будут получены все строки).
Блокирующие итераторы часто (но не всегда) потребляют память. Например, как уже было отмечено ранее, итератор Sort потребляет память. Зато операторы count(*) (который использовался в предыдущей статье) и другие скалярные агрегаты sum(*), min(*), max(*) и т.д., не потребляют память, но являются блокирующими. Невозможно узнать число строк, пока все они не будут считаны.
Если у итератора имеется две дочерние записи, итератор может для одной быть блокирующим, а для другой неблокирующим. Итератор Hash Join (который будет рассмотрен в одной из следующих статей) является хорошим примером итератора такого типа. Неблокирующие итераторы обычно оптимальны для OLTP-запросов, для которых время отклика является важным параметром. Они особенно желательны для запросов с TOP N или запросов с подсказкой FAST N. Поскольку целю таких запросов является максимально быстрое получение строк, оптимизатор старается избежать использования блокирующих операторов, которые потенциально могут обработать больше данных, чем необходимо для возврата первых строк. Неблокирующие итераторы также могут быть полезны при оценке подзапроса EXISTS, где тоже желательно избегать обработки большего, чем необходимо, объёма данных для определения того, что существует не менее одной возвращаемой строки.
Совет: Если в запросе задано упорядочивание вывода, создание соответствующего этому порядку индекса может помочь оптимизатору найти такой план запроса, который будет использования индекс для заданного упорядочивания и не использовать при этом блокирующий итератор сортировки. Это может существенно сократить время отклика. Конечно, есть и другие причины, по которым стоит использовать индексы.

Поддержка динамических курсоров

Используемые в плане запроса динамического курсора итераторы обладают особенными свойствами. Среди всего прочего, динамический курсор должен уметь возвращать подмножество результирующего набора, одновременно предоставляя возможность прокрутки вперед или назад, и уметь накладывать блокировки прокрутки (scroll locks). Для поддержки такой функциональности, итератор должен иметь возможность сохранения и восстановления своего состояния, уметь прокручивать данные вперед и назад, а также должен уметь обрабатывать одну строку на входе для каждой строки на выходе (которые он порождает), и к тому же он должен быть неблокирующим. Не все итераторы имеют перечисленные свойства.

Для исполнения использующего динамический курсор запроса оптимизатор должен
найти такой план запроса, который бы использовал только поддерживающие
динамические курсоры итераторы. Это не всегда возможно, и из-за этого некоторые
запросы не допускают использование динамических курсоров.

Совет: Только создание правильного индекса даёт оптимизатору возможность
устранить операцию сортировки, и по этой же причине, оптимизатору иногда
все-таки удаётся найти план запроса для динамического курсора. К сожалению,
пока это не всегда возможно, хотя материализованные представления порой
помогают обойти подобные ограничения.

Свойства итераторов