Memory Grant

Автор: Jay Choe, Microsoft SQL Server Engine. Understanding SQL server memory grant

В этой статье описывается, как в Microsoft SQL Server организовано предоставление запросам памяти. Выделенную запросам память (эту область памяти называют рабочим буфером запросов – «Query Work Buffer») сервер использует для временного хранения данных при сортировке и соединении строк. Такое предоставление памяти имеет название «Grant», потому что сервер требует, чтобы для запросов происходило резервирование памяти перед её фактическим использованием. Такое резервирование повышает надежность исполнения запросов для нагруженных серверов, поскольку запрос с зарезервированной памятью скорее всего не выйдет за пределы памяти сервера во время исполнения, и сервер не позволит одному запросу забрать всю память. В статье я опишу, как SQL Server оценивает потребность запроса в памяти и как он управляет объёмом выделяемой памяти, когда за ресурс конкурируют несколько запросов.

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

Потребители памяти

Теперь, когда мы кратко рассмотрели что происходит с запросом во время исполнения, давайте рассмотрим как выделение памяти запросу вписывается в общее использование памяти сервером. Из того, что было сказано выше, видно, что успешное исполнение запроса задействует три основных потребителя памяти (Memory Consumers): компиляцию, кэширование и выделение памяти.

·        Компиляция (оптимизация запроса): поиск и построение наиболее оптимального плана, обычно требует значительного объема памяти. Продолжительность использования памяти обычно невелика, поскольку оптимизатор освобождает память, как только найден оптимальный план. Недостаток доступной памяти может привести к задержкам в компиляции и потенциально неэффективным (медленным) планам.

·        Кэширование: поиск оптимального плана требует больших затрат процессорного времени и памяти. SQL Server пытается сохранить скомпилированные планы в кэшах для последующего повторного использования. Продолжительность использования памяти тут уже подольше. Нехватка памяти для кэша приведет к увеличению числа ненужных повторных компиляций.

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

SQL Server балансирует использование памяти между этими тремя потребителями с помощью внутреннего механизма, называемого «брокером памяти» (Memory Broker). Основываясь на данных об использовании и наличии доступной физической памяти, это брокер задаёт лимиты и сигнализирует каждому из названных компонент о необходимости сократить использование памяти, если он прогнозирует недостаток памяти. Как правило, на хорошо работающем сервере вклад от этих потребителей будет примерно одинаковый.

Параметры выделения (Grant)

Когда SQL Server создает скомпилированный план, он вычисляет два параметра выделения памяти, которые называются «требуемая память» (required memory) и «дополнительная память» (additional memory).

·        Требуемая память: минимальный объём памяти, необходимый для выполнения сортировки и хэш-соединения. Она называется «required», поскольку запрос не запустится, если этот объём не будет доступен. SQL Server использует эту память для создания внутренних структур данных для указанных операций.

·        Дополнительная память: объем, необходимый для временного хранения всех строк в памяти. Зависит от оценки кардинальности (ожидаемого количества строк и среднего размера строки). Она называется (additional), потому что запрос может пережить недостаток такой памяти, скинув часть временных строк на диск. Запросу не гарантируется, что он получит весь объём памяти, если общая сумма превысит лимит.

Например, давайте рассмотрим простой запрос, который должен сортировать миллион строк по 10 байт каждая. Требуемая память для этого запроса составляет 512КБ, поскольку это минимальный объем, необходимый SQL Serverу для построения внутренних структур данных для обслуживания сортировки. Поскольку для хранения всех строк требуется 10МБ, дополнительная память составит 10МБ (на самом деле немного больше, если учесть накладные расходы). Этот расчет усложняется, если скомпилированный план имеет несколько сортировок и соединений, поскольку SQL Server также учитывает время жизни каждого оператора для более эффективного использования памяти. Обычно вы увидите меньшую оценку, чем сумма всех сортировок и соединений. Если вас интересует относительное использование памяти между операторами, вы можете проверить тег <MemoryFractions> в Showplan XML. В следующих разделах показано, как эти параметры используются при расчете размера гранта во время выполнения, лимитирующего использование памяти.

Зависимость от DOP

Если SQL Server доступно больше одного процессора, он может выполнять запросы в параллельном режиме. Это может способствовать повышению производительности путем распределения работы между исполнителями (workers). Эти исполнители работают независимо друг от друга и используют оператор параллелизма «Parallelism» (он же Exchange)», который нужен для передачи затронутых запросом строк. Этот параллельный режим увеличивает использование памяти, поскольку каждому исполнителю нужна своя собственная копия для сортировки или хэш-соединения, а оператору параллелизма нужны буферы для временного хранения переданных строк. Поскольку DOP N будет использовать N параллельных исполнителей, запросу потребуется в N раз больше требуемой памяти. С другой стороны, общее количество строк для обработки (и стоимость памяти для их хранения) не меняется из-за DOP. Это означает, что дополнительная память останется прежней независимо от указания DOP. Начиная с SQL 2008, используемая оператором параллелизма память для буферов также включается в обязательное выделение памяти.

Процесс предоставления памяти

Мы кратко рассмотрели как параллелизм влияет на увеличение требований к памяти запросов. Далее мы обсудим, как SQL Server вычисляет необходимую память для сервера и определяет количество одновременных запросов. Серверу необходимо учитывать многие факторы, чтобы избежать выделения памяти больше её физического лимита. Это достигается за два шага. Сначала сервер вычисляет, сколько памяти предоставить для конкретного запроса. Затем он использует внутреннюю функцию, называемую Resource Semaphore, для резервирования фактической памяти или её сокращения, когда её запрашивают слишком много запросов. Начиная с первого, следующие шаги показывают, как вычисляется размер памяти для запроса:

1.      Сервер принимает решение о параллелизме (DOP) анализируя план запроса и состояния сервера.

2.      Проверяет, нужно ли выделять память. Если нет, сервер может исполнить запрос немедленно. Например, простому прямолинейному запросу без "ORDER BY" или "GROUP BY" может не понадобится выделении памяти.

3.      Вычисляет лимит памяти для одного запроса. По умолчанию это 25% от общей памяти запроса (которая ограничивается брокером памяти до 90% от памяти сервера). Этот лимит на запрос помогает предотвратить доминирование одного запроса над всем сервером. Процент можно настроить.

4.      Вычисляет оптимальный размер памяти для запроса, умножая требуемую на DOP + дополнительную + для параллелизма. Это объем памяти, который запросу хотелось бы получить на основе оценки его кардинальности.

5.      Проверяет, превышает ли оптимальный объём лимит для запроса. Если да, то сервер уменьшает дополнительную память до тех пор, пока общая сумма не уложится в лимит. Получившийся объём называется запрошенной памятью (requested memory). После этого, сервер запрашивает у Resource Semaphore выделение запрошенной памяти.

Resource Semaphore

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

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

2.      Когда приходит новый запрос, то сначала проверяется, есть ли ждущие в очереди запросы. Если таких нет, сервер помещает новый запрос в очередь, организованную по принципу «first-come-first-served».

3.      Проверяет наличие запросов в очереди. Если находит, он помещает такой запрос в очередь first-come-first-served, которая обходится недорого и подходит для обслуживания небольших запросов.

4.      Пытается выделить память, если не обнаруживает запросов в ожидании ресурсов или когда возвращает память завершившийся запрос.

5.      Пытается выделить память, если другой запрос высвобождает память.

6.      Пытается выделить память, когда другой запрос возвращает зарезервированную память.

7.      Если же он находит ожидающий запрос, он помещает новый запрос в очередь ожидания.

8.      Если в очереди нет запросов, выполняется проверка доступной свободной памяти.

9.      Если он находит достаточно свободной памяти, то запрошенная память предоставляется и запрос может быть запущен.

10.   Если свободной памяти недостаточно, текущий запрос помещается в очередь.

11.   Активирует запросы из очереди, когда свободной памяти становится достаточно.

Выявление проблем выделения памяти

SQL Server имеет несколько административных динамических представлений (DMV), помогающих исследовать связанные с выделение памяти проблемы. Вы можете получить подробную информацию о перечисленных ниже DMV в BOL (Books-On-Line):

sys.dm_exec_query_resource_semaphores

Показывает текущий статус семафора ресурса, описанного выше. Одна строка с ненулевым столбцом max_target_memory_kb называется Regular Resource Semaphore, а вторая называется Small Resource Semaphore. Как следует из названия, Regular Resource Semaphore используется всеми запросами в обычных условиях, а Small Resource Semaphore используется маленькими запросами (менее 5 МБ), когда они на стадии ожидания (см. шаг 6 в предыдущем разделе). Такое разделение уменьшает время отклика небольших запросов, чтобы они отрабатывали как можно быстрее. Значение max_target_memory_kb показывает ограничение памяти сервера, используемое на шаге 2 выше.

sys.dm_exec_query_memory_grants

Показывает запросы с выделением памяти, включая ждущие в очереди Resource Semaphore. Ждущие запросы будут те, у которых в grant_time ноль. Resource Semaphore использует оценку стоимости запроса для приоритезации выделения памяти, а значение is_next_candidate показывает, какой запрос следует первым выбрать из очереди когда высвободится необходимый объём памяти.

sys.dm_os_wait_stats

Показывает статистику ожиданий всех объектов сервера. Выделение памяти помечается ожиданием RESOURCE_SEMAHORE. Если таких много, это может указывать на проблемы с большими запросами.

 

Примеры запросов

Примеры показывают, как используются DMV выделения памяти.

Показать все запросы, ожидающие в очереди выделения памяти:

SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null

Показать запросы, которые больше всего задействует выделения памяти:

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan

FROM sys.dm_exec_query_memory_grants AS mg

CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t

CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp

ORDER BY 1 DESC OPTION (MAXDOP 1);

Поиск в кэше запросов с выделением памяти:

SELECT t.text, cp.objtype,qp.query_plan

FROM sys.dm_exec_cached_plans AS cp

JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t

WHERE qp.query_plan.exist('declare namespace n=" http://schemas.microsoft.com/sqlserver/2004/07/showplan "; //n:MemoryFractions') = 1;