Understanding SQL server memory grant – Tips, Tricks, and Advice from the SQL Server Query Processing Team

This article describes how query memory grant works in Microsoft SQL Server. It applies to both SQL2005 and SQL2008. Written by Jay Choe, Microsoft SQL Server Engine. ——————————————————————————– Query memory grant (a.k.a. query work buffer) is a part of server memory used to store temporary row data while sorting and joining rows. It is called “grant” because the server requires those queries to “reserve” before actually using memory. This reservati

Источник: Understanding SQL server memory grant – Tips, Tricks, and Advice from the SQL Server Query Processing Team

 

В этой статье описывается, как поддержка памяти запросов работает в Microsoft SQL Server. Он применяется как для SQL2005, так и для SQL2008. Написано Jay Choe, Microsoft SQL Server Engine.

————————————————– ——————————

Предоставление запроса памяти (рабочий столбег запросов a.k.a.) является частью памяти сервера, используемой для хранения временных данных строки при сортировке и объединении строк. Он называется «грант», потому что сервер требует, чтобы эти запросы «резервировались» до фактического использования памяти. Это резервирование повышает надежность запросов при загрузке сервера, потому что запрос с зарезервированной памятью с меньшей вероятностью ударит из памяти во время работы, а сервер не позволяет одному запросу доминировать над всей памятью сервера. В следующих разделах я расскажу, как SQL Server оценивает потребность в памяти запросов и как он дросселирует гранты памяти, когда конкурирует несколько запросов.

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

 

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

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

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

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

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

 

Параметры гранта
Когда SQL-сервер создает скомпилированный план, он вычисляет два параметра выделения памяти, называемых «необходимая память» и «дополнительная память».

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

Дополнительная память: сумма, необходимая для хранения всех временных строк в памяти. Это зависит от оценки мощности (ожидаемые строки чисел и средний размер строки). Это называется дополнительным, поскольку запрос может выжить из-за отсутствия такой памяти, сохраняя часть временных строк на жестком диске. В запросе не гарантируется полная сумма, если сумма превышает пресет.

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

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

Процесс предоставления памяти
В предыдущем разделе мы обсудили, как параллелизм влияет на требования к памяти запроса. В этом разделе мы обсудим, как SQL Server принимает серверную память и количество параллельных запросов в соображениях. Серверу необходимо учитывать такие динамические факторы, чтобы избежать фиксации памяти за пределами ее физического предела. Это делается в 2 разных шага. Во-первых, сервер вычисляет, сколько памяти выделяется для данного запроса. Затем он использует внутреннее средство под названием Resource Semaphore для резервирования реальной памяти или дроссельной заслонки, если слишком много запросов запрашивают память. Во-первых, следующие шаги показывают, как определяется размер запроса.

 

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

Сервер проверяет, нужна ли поддержка памяти. Если это не требуется, сервер может сразу запустить запрос. Например, простой последовательный запрос без «ORDER BY» или «GROUP BY» может не нуждаться в предоставлении памяти.

Сервер вычисляет ограничение памяти для одного запроса. По умолчанию это 25% (20% от 32-битного SQL 2005) общей памяти запросов (которая установлена ​​брокером памяти как около 90% памяти сервера). Этот предел для каждого запроса помогает предотвратить доминирование одного запроса на всем сервере. Этот процент настраивается на SQL 2008.

Сервер рассчитывает идеальную память запроса по мере необходимости * DOP + дополнительный (+ обмен на SQL 2008). Это сумма, которую запрос хотел бы получить, исходя из оценки мощности.

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

Ресурсный семафор
Resource Semaphore отвечает за удовлетворение запросов на предоставление грантов памяти, сохраняя при этом общие возможности предоставления памяти в пределах сервера.

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

Когда Resource Semaphore получает новый запрос, он сначала проверяет, ожидает ли какой-либо запрос. Если он находит один, он ставит новый запрос в очередь для справедливости, потому что очередь ожидания спроектирована как первая очередь.

Ресурс Семафор проверяет ожидающий запрос в своей очереди. Если он находит один, он ставит новый запрос в очередь ожидания для обеспечения справедливости, потому что очередь ожидания сконструирована в качестве основы «первым пришел-первым» с небольшим весом для поддержки небольших запросов.

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

Resource Semaphore пытается предоставить память, когда нет ожидающего запроса или когда запрос возвращает память.

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

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

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

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

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

Ресурс Семафор пробуждает запросы в очереди ожидания, когда становится доступной свободная память.

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

sys.dm_exec_query_resource_semaphores
Этот DMV показывает текущий статус Семафора ресурсов, упомянутый в предыдущем разделе. В SQL 2005 вы найдете 2 из них. Один с ненулевым столбцом max_target_memory_kb называется регулярным ресурсным семафором, а другой называется Small Resource Semaphore. Как следует из его названия, регулярный ресурс-семафор используется всеми запросами в нормальном состоянии, в то время как Small Resource Semaphore используется небольшими запросами (менее 5 МБ), когда им приходится ждать (см. Шаг 6 в предыдущем разделе). Это улучшит время отклика небольших запросов, которые, как ожидается, закончатся очень быстро. Примечание. В столбце max_target_memory_kb указан лимит памяти сервера, используемый в шаге 2 предыдущего раздела.

sys.dm_exec_query_memory_grants
Этот DMV отображает все запросы, потребляющие гранты памяти, в том числе ожидающие очереди Resource Paraphore. Ожидающие запросы будут иметь нулевой столбец grant_time. Ресурс Семафор использует внутреннюю оценку стоимости запроса, чтобы приоритезировать гранты памяти, а столбец is_next_candidate показывает, какой запрос пробуждается, когда доступна память.

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

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

 

Find all queries waiting in the memory queue:
SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null

Find who uses the most query memory grant:
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)

Search cache for queries with memory grants:
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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *