Устранение проблем, связанных с низкой производительностью или нехваткой памяти, вызванных временно предоставляемым буфером памяти в SQL Server
Что такое предоставление памяти?
Предоставление памяти, также называемое резервированиями выполнения запросов (QE), памятью выполнения запросов, памятью рабочей области и резервированиями памяти, описывает использование памяти во время выполнения запроса. SQL Server выделяет эту память во время выполнения запроса для одной или нескольких следующих целей:
- Сортирование операций
- Хэш-операции
- Операции массового копирования (не распространенная проблема)
- Создание индекса, включая вставку в индексы COLUMNSTORE, так как хэш-словари и таблицы используются во время выполнения для построения индекса (не распространенная проблема)
Для предоставления определенного контекста в течение своего существования запрос может запрашивать память от разных распределителей памяти или клерков в зависимости от того, что нужно сделать. Например, когда запрос анализируется и компилируется изначально, он потребляет память компиляции. После компиляции запроса она освобождается, а результирующий план запроса хранится в памяти кэша плана. После кэширования плана запрос готов к выполнению. Если запрос выполняет какие-либо операции сортировки, операции хэш-сопоставления (JOIN или агрегаты) или вставки в индексы COLUMNSTORE, он использует память из распределителя выполнения запросов. Изначально запрос запрашивает эту память выполнения, а затем, если эта память предоставлена, запрос использует все или часть памяти для сортировки результатов или хэш-сегментов. Эта память, выделенная во время выполнения запроса, называется предоставлением памяти. Как вы можете себе представить, как только операция выполнения запроса завершится, предоставление памяти освобождается обратно в SQL Server, чтобы использовать для другой работы. Таким образом, выделения предоставления памяти являются временными в природе, но могут длиться долго. Например, если выполнение запроса выполняет операцию сортировки в очень большом наборе строк в памяти, сортировка может занять много секунд или минут, а предоставленная память используется в течение всего времени существования запроса.
Пример запроса с предоставлением памяти
Ниже приведен пример запроса, использующего память выполнения и его план запроса, показывающий предоставление:
SELECT *
FROM sys.messages
ORDER BY message_id
Этот запрос выбирает набор строк более 300 000 строк и сортирует его. Операция сортировки вызывает запрос на предоставление памяти. При выполнении этого запроса в SSMS можно просмотреть его план запроса. При выборе левого SELECT оператора плана запроса можно просмотреть сведения о предоставлении памяти для запроса (нажмите клавишу F4 , чтобы отобразить свойства):
Кроме того, если щелкнуть правой кнопкой мыши пробел в плане запроса, можно выбрать команду Show Execution Plan XML и найти XML-элемент, отображающий те же сведения о предоставлении памяти.
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="41232" RequiredMemory="5248" DesiredMemory="46016" RequestedMemory="46016" GrantWaitTime="0" GrantedMemory="46016" MaxUsedMemory="45816" MaxQueryMemory="277688" LastRequestedMemory="0" IsMemoryGrantFeedbackAdjusted="No: First Execution" />
Здесь требуется объяснение нескольких терминов. Запрос может потребовать определенного объема памяти выполнения (DesiredMemory) и обычно запрашивать эту сумму (RequestMemory). Во время выполнения SQL Server предоставляет все или часть запрошенной памяти в зависимости от доступности (GrantedMemory). В конце концов запрос может использовать больше или меньше первоначально запрошенной памяти (MaxUsedMemory). Если оптимизатор запросов переоценил необходимый объем памяти, он использует меньше запрошенного размера. Но эта память тратится, так как она могла бы использоваться другим запросом. С другой стороны, если оптимизатор недооценил размер памяти, лишние строки могут быть переброжены на диск, чтобы выполнить работу во время выполнения. Вместо выделения больше памяти, чем первоначально запрошенный размер, SQL Server отправляет дополнительные строки на диск и использует его в качестве временной рабочей области. Дополнительные сведения см. в разделе "Вопросы предоставления памяти" в файлах Workfiles и Worktables.
Терминология
Давайте рассмотрим различные термины, которые могут возникнуть в отношении этого потребителя памяти. Опять же, все эти понятия описываются, связанные с одинаковыми выделениями памяти.
Память выполнения запросов (память QE): этот термин используется для выделения того факта, что во время выполнения запроса используется сортировать или хэш-память. Обычно память QE является крупнейшим потребителем памяти во время выполнения запроса.
Резервирования выполнения запросов (QE) или резервирования памяти. Если запросу требуется память для операций сортировки или хэша, он делает запрос на резервирование памяти. Запрос на резервирование вычисляется во время компиляции на основе предполагаемого кратности. Позже при выполнении запроса SQL Server предоставляет запрос частично или полностью в зависимости от доступности памяти. В конце концов запрос может использовать процент предоставленной памяти. Существует клерок памяти (бухгалтер памяти) с именем "MEMORYCLERK_SQLQERESERVATIONS", который отслеживает эти выделения памяти (ознакомьтесь с DBCC MEMORYSTATUS или sys.dm_os_memory_clerks).
Гранты памяти. Когда SQL Server предоставляет запрошенную память выполнению запроса, говорится, что произошло предоставление памяти. Существует несколько счетчиков производительности, использующих термин "grant". Эти счетчики и
Memory Grants Pendingотображают количество предоставленныхMemory Grants Outstandingпамяти или ожиданий. Они не учитывают размер предоставления памяти. Один запрос может использовать один запрос, например, 4 ГБ памяти для выполнения сортировки, но это не отражается ни в одном из этих счетчиков.Память рабочей области — это другой термин, описывающий ту же память. Часто этот термин отображается в счетчике
Granted Workspace Memory (KB)Perfmon, который отражает общий объем памяти, используемой в настоящее время для сортировки, хэша, массового копирования и создания индексов, выраженных в КБ. ДругойMaximum Workspace Memory (KB)счетчик учитывает максимальный объем памяти рабочей области, доступный для любых запросов, которые могут потребоваться выполнять такие хэш-операции, сортировки, массового копирования и создания индекса. Термин "Память рабочей области" встречается редко за пределами этих двух счетчиков.
Влияние на производительность использования большого объема памяти QE
В большинстве случаев, когда поток запрашивает память внутри SQL Server, чтобы сделать что-то, и память недоступна, запрос завершается ошибкой из памяти. Однако существует несколько сценариев исключений, в которых поток не завершается ошибкой, но ожидает, пока память не станет доступной. Один из этих сценариев — предоставление памяти, а другой — память компиляции запросов. SQL Server использует объект синхронизации потоков, называемый семафором, для отслеживания объема памяти, предоставленной для выполнения запроса. Если SQL Server выходит из предопределенной рабочей области QE, вместо сбоя запроса с ошибкой нехватки памяти он вызывает ожидание запроса. Учитывая, что память рабочей области может занять значительный процент общей памяти SQL Server, ожидая памяти в этом пространстве, имеет серьезные последствия для производительности. Большое количество одновременных запросов запрашивало память выполнения, и вместе они исчерпали пул памяти QE, или несколько одновременных запросов запрашивают очень большие гранты. В любом случае результирующая проблема с производительностью может иметь следующие симптомы:
- Страницы данных и индексов из буферного кэша, вероятно, были удалены, чтобы освободить место для запросов на предоставление большой памяти. Это означает, что операции чтения страниц, поступающие из запросов, должны быть удовлетворены с диска (значительно медленная операция).
- Запросы на выделение других ресурсов памяти могут завершиться сбоем из-за ошибок памяти, так как ресурс связан с операциями сортировки, хэша или сборки индексов.
- Запросы, которым требуется память выполнения, ожидают, чтобы ресурс стал доступным и занимает много времени. Другими словами, для конечного пользователя эти запросы медленны.
Таким образом, если вы наблюдаете ожидание памяти выполнения запросов в Perfmon, динамических административных представлениях (DMV) или DBCC MEMORYSTATUSнеобходимо действовать для устранения этой проблемы, особенно если проблема возникает часто. Дополнительные сведения см. в разделе "Что может сделать разработчик о операциях сортировки и хэша".
Определение ожиданий памяти выполнения запроса
Существует несколько способов определения ожиданий резервирования QE. Выберите те, которые служат вам лучше, чтобы увидеть более крупное изображение на уровне сервера. Некоторые из этих средств могут быть недоступны для вас (например, Perfmon недоступен в База данных SQL Azure). После выявления проблемы необходимо выполнить детализацию на уровне отдельного запроса, чтобы узнать, какие запросы требуют настройки или перезаписи.
На уровне сервера используйте следующие методы:
- См. sys.dm_exec_query_resource_semaphores sys.dm_exec_query_resource_semaphores sys.dm_exec_query_resource_semaphores семафора ресурсов.
- счетчики Монитор производительности Дополнительные сведения см. в разделе sql Server Memory Manager.
- DBCC MEMORYSTATUS Дополнительные сведения см. в разделе DBCC MEMORYSTATUS.
- Дополнительные сведения см. в sys.dm_os_memory_clerks sys.dm_os_memory_clerks динамического интеллектуального анализа памяти.
- Определение предоставления памяти с помощью расширенных событий (XEvents) дополнительные сведения см. в разделе "Расширенные события( XEvents)".
На отдельном уровне запроса используйте следующие методы:
- Определите конкретные запросы с помощью sys.dm_exec_query_memory_grants: в настоящее время выполняются запросы. Дополнительные сведения см. в sys.dm_exec_query_memory_grants.
- Определите конкретные запросы с помощью sys.dm_exec_requests: в настоящее время выполняются запросы. Дополнительные сведения см. в sys.dm_exec_requests.
- Определите конкретные запросы с помощью sys.dm_exec_query_stats: историческая статистика по запросам. Дополнительные сведения см. в sys.dm_exec_query_stats.
- Определите конкретные запросы с помощью хранилище запросов (QDS) с помощью sys.query_store_runtime_stats: историческая статистика по запросам с QDS. Дополнительные сведения см. в sys.query_store_runtime_stats.
Статистические статистические данные об использовании памяти
Sys.dm_exec_query_resource_semaphores семафора ресурса
Это динамическое административное представление разбивает память резервирования запросов по пулу ресурсов (внутренним, по умолчанию и созданному пользователем) и resource_semaphore (регулярным и небольшим запросам). Полезный запрос может быть следующим:
SELECT
pool_id
,total_memory_kb
,available_memory_kb
,granted_memory_kb
,used_memory_kb
,grantee_count, waiter_count
,resource_semaphore_id
FROM sys.dm_exec_query_resource_semaphores rs
В следующем примере выходных данных показано, что в 22 запросах используется около 900 МБ памяти выполнения запроса, а еще 3 ожидания. Это происходит в пуле по умолчанию (pool_id = 2) и регулярном семафоре запроса (resource_semaphore_id = 0).
pool_id total_memory_kb available_memory_kb granted_memory_kb used_memory_kb grantee_count waiter_count resource_semaphore_id
------- --------------- ------------------- ----------------- -------------- ------------- ------------ ---------------------
1 30880 30880 0 0 0 0 0
1 5120 5120 0 0 0 0 1
2 907104 0 907104 898656 22 3 0
2 40960 40960 0 0 0 0 1
(4 rows affected)
Счетчики системного монитора
Аналогичная информация доступна через счетчики Монитор производительности, где можно наблюдать за текущими предоставленными запросами (Memory Grants Outstanding), запросами на предоставление ожидания (Memory Grants Pending) и объемом памяти, используемой грантами памяти (Granted Workspace Memory (KB)). На следующем рисунке выдающиеся гранты 18, ожидающие гранты 2, а предоставленная память рабочей области составляет 828 288 КБ. Счетчик Memory Grants Pending Perfmon со значением ненулевого значения указывает, что память исчерпана.
Дополнительные сведения см. в разделе sql Server Memory Manager.
- SQLServer, Memory Manager: максимальная память рабочей области (КБ)
- SQLServer, Memory Manager: память предоставляет невыполненные ресурсы
- SQLServer, диспетчер памяти: ожидающие предоставления памяти
- SQLServer, Memory Manager: предоставленная память рабочей области (КБ)
DBCC MEMORYSTATUS
Другим местом, где можно просмотреть сведения о памяти резервирования запросов, является DBCC MEMORYSTATUS (раздел "Объекты памяти запросов"). Вы можете просмотреть выходные Query Memory Objects (default) данные для запросов пользователей. Если вы включили регулятор ресурсов с пулом ресурсов с именем PoolAdmin, например, можно просмотреть оба Query Memory Objects (default) и Query Memory Objects (PoolAdmin).
Ниже приведен пример выходных данных из системы, в которой 18 запросов были предоставлены памяти выполнения запросов, и 2 запроса ожидают памяти. Доступное значение счетчика равно нулю, что означает, что объем памяти рабочей области больше недоступен. Этот факт объясняет два ожидающих запроса. Отображается Wait Time истекшее время в миллисекундах, так как запрос был помещен в очередь ожидания. Дополнительные сведения об этих счетчиках см. в разделе "Запросы объектов памяти".
Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 18
Waiting 2
Available 0
Current Max 103536
Future Max 97527
Physical Max 139137
Next Request 5752
Waiting For 8628
Cost 16
Timeout 401
Wait Time 2750
(11 rows affected)
Small Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 0
Waiting 0
Available 5133
Current Max 5133
Future Max 5133
DBCC MEMORYSTATUS также отображает сведения о клерке памяти, которая отслеживает память выполнения запроса. В следующих выходных данных показано, что страницы, выделенные для резервирования запросов (QE), превышают 800 МБ.
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
------------------------------------------------------------------------ -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 824640
Sys.dm_os_memory_clerks dmerks памяти
Если вам требуется больше табличного результированного набора, отличающегося от раздела DBCC MEMORYSTATUS, можно использовать sys.dm_os_memory_clerks для аналогичных сведений. MEMORYCLERK_SQLQERESERVATIONS Найдите клерка памяти. Объекты памяти запросов недоступны в этом динамическом административном представлении.
SELECT type, memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
Ниже приведен пример выходных данных.
type memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS 0 824640
MEMORYCLERK_SQLQERESERVATIONS 64 0
Определение ресурсов памяти с помощью расширенных событий (XEvents)
Существует несколько расширенных событий, которые предоставляют сведения о предоставлении памяти и позволяют записывать эти сведения с помощью трассировки:
- sqlserver.additional_memory_grant. Происходит при попытке запроса получить больше памяти во время выполнения. Сбой получения этого дополнительного предоставления памяти может привести к замедлению запроса.
- sqlserver.query_memory_grant_blocking. Происходит, когда запрос блокирует другие запросы во время ожидания предоставления памяти.
- sqlserver.query_memory_grant_info_sampling. Происходит в конце случайно выборочных запросов, предоставляющих сведения о предоставлении памяти (например, для телеметрии).
- sqlserver.query_memory_grant_resource_semaphores. Выполняется через пять минут для каждого пула ресурсов регулятора ресурсов.
- sqlserver.query_memory_grant_usage. Происходит в конце обработки запросов с объемом памяти более 5 МБ, чтобы сообщить пользователям о несоответствиях в памяти.
- sqlserver.query_memory_grants. Выполняется через пять минут для каждого запроса с предоставлением памяти.
Расширенные события предоставления памяти
Сведения о функциях предоставления отзывов о предоставлении памяти для обработки запросов см. в разделе "Отзыв о предоставлении памяти".
- sqlserver.memory_grant_feedback_loop_disabled. Происходит при отключении цикла обратной связи предоставления памяти.
- sqlserver.memory_grant_updated_by_feedback. Происходит при обновлении предоставления памяти обратной связью.
Предупреждения о выполнении запросов, относящиеся к грантам памяти
- sqlserver.execution_warning. Происходит, когда инструкция T-SQL или хранимая процедура ожидает более одной секунды для предоставления памяти или при первоначальной попытке получить память сбоем. Используйте это событие в сочетании с событиями, которые определяют ожидания для устранения проблем, влияющих на производительность.
- sqlserver.hash_spill_details. Происходит в конце хэш-обработки, если недостаточно памяти для обработки входных данных сборки хэш-соединения. Используйте это событие вместе с любым из
query_pre_execution_showplanсобытий илиquery_post_execution_showplanсобытий, чтобы определить, какая операция в созданном плане вызывает хэш-утечку. - sqlserver.hash_warning. Возникает, когда недостаточно памяти для обработки входных данных сборки хэш-соединения. Это приводит к рекурсии хэша, если входные данные сборки секционированы или хэш-выход, когда секционирование входных данных сборки превышает максимальный уровень рекурсии. Используйте это событие вместе с любым из
query_pre_execution_showplanсобытий илиquery_post_execution_showplanсобытий, чтобы определить, какая операция в созданном плане вызывает предупреждение хэша. - sqlserver.sort_warning. Происходит, когда операция сортировки в выполняемом запросе не помещается в память. Это событие не создается для операций сортировки, вызванных созданием индекса, только для операций сортировки в запросе. (Например, в
Order ByинструкцииSelect.) Используйте это событие для идентификации запросов, которые выполняются медленно из-за операции сортировки, особенно еслиwarning_typeзначение = 2, указывающее на несколько проходов по данным, необходимы для сортировки.
Планирование создания событий, содержащих сведения о предоставлении памяти
Следующий план запроса, создающий расширенные события, содержит поля granted_memory_kb и ideal_memory_kb по умолчанию:
- sqlserver.query_plan_profile
- sqlserver.query_post_execution_plan_profile
- sqlserver.query_post_execution_showplan
- sqlserver.query_pre_execution_showplan
Сборка индекса хранилища столбцов
Одним из областей, охваченных XEvents, является память выполнения, используемая во время создания хранилища столбцов. Это список доступных событий:
- sqlserver.column_store_index_build_low_memory: подсистема хранилища обнаружила низкое состояние памяти, а размер группы строк был сокращен. Здесь есть несколько интересных столбцов.
- sqlserver.column_store_index_build_memory_trace. Трассировка использования памяти во время сборки индекса.
- sqlserver.column_store_index_build_memory_usage_scale_down: подсистема хранилища сократила масштаб.
- sqlserver.column_store_index_memory_estimation. Показывает результат оценки памяти во время сборки группы строк COLUMNSTORE.
Определение конкретных запросов
Существует два типа запросов, которые можно найти при просмотре отдельного уровня запроса. Запросы, которые используют большой объем памяти выполнения запроса, и те, которые ожидают той же памяти. Последняя группа может состоять из запросов с скромными потребностями в предоставлении памяти, и если да, вы можете сосредоточить внимание на других местах. Но они также могут быть виновными, если они запрашивают огромные размеры памяти. Сосредоточьтесь на них, если вы обнаружите, что это так. Это может быть распространено, чтобы найти, что один конкретный запрос является преступником, но многие экземпляры его создаются. Эти экземпляры, получающие предоставление памяти, вызывают другие экземпляры того же запроса, чтобы ждать предоставления. Независимо от конкретных обстоятельств, в конечном счете необходимо определить запросы и размер запрошенной памяти выполнения.
Определение конкретных запросов с помощью sys.dm_exec_query_memory_grants
Чтобы просмотреть отдельные запросы и размер памяти, которые они запрашивали и предоставили, можно запросить динамическое sys.dm_exec_query_memory_grants представление управления. В этом динамическом административном представлении отображаются сведения о текущем выполнении запросов, а не об исторических сведениях.
Следующая инструкция получает данные из dmV, а также извлекает текст запроса и план запроса в результате:
SELECT
session_id
,requested_memory_kb
,granted_memory_kb
,used_memory_kb
,queue_id
,wait_order
,wait_time_ms
,is_next_candidate
,pool_id
,text
,query_plan
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Ниже приведен сокращенный пример выходных данных запроса во время активного потребления памяти QE. Большинство запросов предоставляют память, как показано granted_memory_kb в числовых значениях, used_memory_kb отличных от NULL. Запросы, которые не получили предоставленный запрос, ожидают памяти выполнения и granted_memory_kb = NULL. Кроме того, они помещаются в очередь ожидания с значением queue_id 6. Они wait_time_ms указывают около 37 секунд ожидания. Сеанс 72 находится в очереди, чтобы получить грант, как указано wait_order = 1, а сеанс 74 приходит после него с wait_order = 2.
session_id requested_memory_kb granted_memory_kb used_memory_kb queue_id wait_order wait_time_ms is_next_candidate pool_id
---------- -------------------- -------------------- -------------------- -------- ----------- -------------------- ----------------- -------
80 41232 41232 40848 NULL NULL NULL NULL 2
83 41232 41232 40848 NULL NULL NULL NULL 2
84 41232 41232 40848 NULL NULL NULL NULL 2
74 41232 NULL NULL 6 2 37438 0 2
78 41232 41232 40848 NULL NULL NULL NULL 2
81 41232 41232 40848 NULL NULL NULL NULL 2
71 41232 41232 40848 NULL NULL NULL NULL 2
75 41232 NULL NULL 6 0 37438 1 2
82 41232 41232 40848 NULL NULL NULL NULL 2
76 41232 41232 40848 NULL NULL NULL NULL 2
79 41232 41232 40848 NULL NULL NULL NULL 2
85 41232 41232 40848 NULL NULL NULL NULL 2
70 41232 41232 40848 NULL NULL NULL NULL 2
55 41232 41232 40848 NULL NULL NULL NULL 2
59 41232 NULL NULL 6 3 37438 0 2
62 41232 41232 40848 NULL NULL NULL NULL 2
54 41232 41232 40848 NULL NULL NULL NULL 2
77 41232 41232 40848 NULL NULL NULL NULL 2
52 41232 41232 40848 NULL NULL NULL NULL 2
72 41232 NULL NULL 6 1 37438 0 2
69 41232 41232 40848 NULL NULL NULL NULL 2
73 41232 41232 40848 NULL NULL NULL NULL 2
66 41232 NULL NULL 6 4 37438 0 2
68 41232 41232 40848 NULL NULL NULL NULL 2
63 41232 41232 40848 NULL NULL NULL NULL 2
Определение конкретных запросов с помощью sys.dm_exec_requests
В SQL Server есть тип ожидания, указывающий, что запрос ожидает предоставления RESOURCE_SEMAPHOREпамяти. Этот тип ожидания может наблюдаться для sys.dm_exec_requests отдельных запросов. Это последнее динамическое административное представление является лучшей отправной точкой, чтобы определить, какие запросы являются жертвами недостаточной памяти гранта. Вы также можете наблюдать за ожиданием RESOURCE_SEMAPHORE в sys.dm_os_wait_stats как агрегированные точки данных на уровне SQL Server. Этот тип ожидания отображается, когда запрос памяти запроса не может быть предоставлен из-за других параллельных запросов, которые использовали память. Большое количество ожидающих запросов и длительное время ожидания указывает на чрезмерное количество одновременных запросов с использованием памяти выполнения или больших размеров запросов памяти.
Примечание.
Время ожидания предоставления памяти ограничено. После чрезмерного ожидания (например, более 20 минут) SQL Server время ожидания запроса и вызывает ошибку 8645, "Время ожидания ресурсов памяти для выполнения запроса. Повторно выполните запрос". Вы можете увидеть значение времени ожидания на уровне сервера, просмотрев timeout_sec его sys.dm_exec_query_memory_grants. Значение времени ожидания может немного отличаться от версий SQL Server.
С помощью sys.dm_exec_requestsэтого запроса можно увидеть, какие запросы были предоставлены памяти и размер этого гранта. Кроме того, можно определить, какие запросы в настоящее время ожидают предоставления памяти, найдите RESOURCE_SEMAPHORE тип ожидания. Ниже приведен запрос, показывающий как предоставленные, так и ожидающие запросы:
SELECT session_id, wait_type, wait_time, granted_query_memory, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE granted_query_memory > 0
OR wait_type = 'RESOURCE_SEMAPHORE'
Пример выходных данных показывает, что два запроса были предоставлены памяти, и два десятка других ожидают грантов. Столбец granted_query_memory сообщает размер на страницах размером 8 КБ. Например, значение 34 709 означает 34 709 * 8 КБ = 277 672 КБ памяти.
session_id wait_type wait_time granted_query_memory text
---------- ----------------------------------- -------------------- -------------------------------------------------------------------
65 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
66 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
67 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
68 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
69 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
70 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
71 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
72 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
73 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
74 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
75 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
76 ASYNC_NETWORK_IO 11 34709 select * from sys.messages order by message_id option (maxdop 1)
77 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
78 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
79 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
80 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
81 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
82 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
83 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
84 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
85 ASYNC_NETWORK_IO 14 34709 select * from sys.messages order by message_id option (maxdop 1)
86 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
87 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
88 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
89 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
Определение конкретных запросов с помощью sys.dm_exec_query_stats
Если проблема предоставления памяти в данный момент не происходит, но вы хотите определить обидеющие запросы, вы можете просмотреть исторические данные запроса с помощью sys.dm_exec_query_stats. Время существования данных привязано к плану запроса каждого запроса. При удалении плана из кэша планов соответствующие строки удаляются из этого представления. Другими словами, dmV хранит статистику в памяти, которая не сохраняется после перезапуска SQL Server или после нехватки памяти, приводит к выпуску кэша планов. Это говорится, вы можете найти информацию здесь ценную, особенно для статистической статистики запросов. Возможно, кто-то недавно видел большие гранты памяти от запросов, но при просмотре рабочей нагрузки сервера может возникнуть проблема. В этой ситуации можно предоставить аналитические сведения о том, sys.dm_exec_query_stats что другие виртуальные машины не могут. Ниже приведен пример запроса, который поможет найти первые 20 операторов, которые потребляли наибольшие объемы памяти выполнения. В выходных данных отображаются отдельные операторы, даже если их структура запроса одинакова. Например, SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 является отдельной строкой ( SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 только значение предиката фильтра зависит). Запрос получает первые 20 операторов с максимальным размером гранта, превышающим 5 МБ.
SELECT TOP 20
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
,CONVERT(DECIMAL (10,2), max_grant_kb /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), min_grant_kb /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (total_grant_kb / execution_count) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), max_used_grant_kb /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), min_used_grant_kb /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_used_grant_kb/ execution_count) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ execution_count) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ 1024.0)) AS total_grant_for_all_executions_mb
,execution_count
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
WHERE max_grant_kb > 5120 -- greater than 5 MB
ORDER BY max_grant_kb DESC
Еще более мощные аналитические сведения можно получить, просмотрев запросы, агрегированные query_hashпо . В этом примере показано, как найти средний, максимальный и минимальный размер предоставления инструкции запроса во всех его экземплярах, так как план запроса был впервые кэширован.
SELECT TOP 20
MAX(SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1)) AS sample_statement_text
,CONVERT(DECIMAL (10,2), SUM(max_grant_kb) /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), SUM(min_grant_kb) /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (SUM(total_grant_kb) / SUM(execution_count)) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), SUM(max_used_grant_kb) /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), SUM(min_used_grant_kb) /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_used_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_ideal_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), SUM(total_grant_kb) /1024.0) AS total_grant_all_executions_mb
,SUM(execution_count) AS execution_count
,query_hash
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
GROUP BY query_hash
HAVING SUM(max_grant_kb) > 5120 -- greater than 5 MB
ORDER BY SUM(max_grant_kb) DESC
OPTION (MAX_GRANT_PERCENT = 5)
В Sample_Statement_Text столбце показан пример структуры запроса, которая соответствует хэшу запроса, но она должна быть прочитана без учета определенных значений в инструкции. Например, если инструкция содержитWHERE Id = 5, его можно прочитать в более универсальной форме: WHERE Id = @any_value
Ниже приведен сокращенный пример выходных данных запроса с выбранными столбцами:
sample_statement_text max_grant_mb avg_grant_mb max_grant_used_mb avg_grant_used_mb avg_ideal_grant_mb total_grant_all_executions_mb execution_count
----------------------------------------- ------------ ------------- ----------------- ----------------- ------------------ ----------------------------- ----------------
select de.ObjectName,de.CounterName,d 282.45 282.45 6.50 6.50 282.45 282.45 1
SELECT SCHEMA_NAME(udf.schema_id) AS [Sch 33.86 8.55 7.80 1.97 8.55 42.74 5
insert into #tmpCounterDateTime (CounterD 32.45 32.45 3.11 3.11 32.45 32.45 1
select db_id() dbid, db_name() dbname, * 20.80 1.30 5.75 0.36 1.30 20.80 16
SELECT SCHEMA_NAME(obj.schema_id) AS [Sch 20.55 5.19 5.13 1.28 5.19 25.93 5
SELECT xmlplan FROM (SELECT ROW_NUMBER() 19.69 1.97 1.09 0.11 1.97 19.69 10
if ( select max(cast(countervalue as floa 16.39 8.20 0.77 0.38 8.20 16.39 2
SELECT udf.name AS [Name], udf.object_id 11.36 5.08 1.66 0.83 5.08 20.33 4
select --* Database_I 10.94 5.47 1.98 0.99 5.47 10.94 2
IF (select max(cast(dat.countervalue as f 8.00 1.00 0.00 0.00 0.53 8.00 8
insert into #tmpCounterDateTime (CounterD 5.72 2.86 1.98 0.99 2.86 5.72 2
INSERT INTO #tmp (CounterDateTime, Counte 5.39 1.08 1.64 0.33 1.08 6.47 6
Определение конкретных запросов с помощью хранилище запросов (QDS) с помощью sys.query_store_runtime_stats
Если вы включили хранилище запросов, вы можете воспользоваться сохраняемой статистикой истории. В отличие от данных, эти статистические данные sys.dm_exec_query_statsсохраняют нагрузку на перезапуск SQL Server или нагрузку на память, так как они хранятся в базе данных. QDS также имеет ограничения размера и политику хранения. Дополнительные сведения см. в разделе "Задание оптимального режима записи хранилище запросов" и "Сохранение наиболее релевантных данных" в разделах хранилище запросов в рекомендациях по управлению хранилище запросов.
Определите, включены ли базы данных хранилище запросов с помощью этого запроса:
SELECT name, is_query_store_on FROM sys.databases WHERE is_query_store_on = 1Выполните следующий диагностический запрос в контексте определенной базы данных, которую вы хотите исследовать:
SELECT MAX(qtxt.query_sql_text) AS sample_sql_text ,CONVERT(DECIMAL(10,2), SUM(rts.avg_query_max_used_memory) / 128) AS avg_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.min_query_max_used_memory) / 128) AS min_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.max_query_max_used_memory) / 128) AS max_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.stdev_query_max_used_memory) / 128) AS stdev_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.last_query_max_used_memory) / 128) AS last_mem_grant_used_mb ,SUM(count_executions) AS count_query_executions FROM sys.query_store_runtime_stats rts JOIN sys.query_store_plan p ON p.plan_id = rts.plan_id JOIN sys.query_store_query q ON p.query_id = q.query_id LEFT OUTER JOIN sys.query_store_query_text qtxt ON q.query_text_id = qtxt.query_text_id GROUP BY q.query_hash HAVING SUM(rts.avg_query_max_used_memory) /128 > 5 -- greater than 5 MB ORDER BY SUM(avg_query_max_used_memory) DESC OPTION (MAX_GRANT_PERCENT = 5)Ниже приведены те же принципы, что
sys.dm_exec_query_statsи статистические статистические данные для инструкций. Однако одно из различий заключается в том, что при использовании QDS вы просматриваете только запросы в области этой базы данных, а не весь SQL Server. Поэтому может потребоваться знать базу данных, в которой был выполнен конкретный запрос на предоставление памяти. В противном случае выполните этот диагностический запрос в нескольких базах данных, пока не найдете доступные объемные ресурсы памяти.Ниже приведен сокращенный пример выходных данных:
sample_sql_text avg_mem_grant_used_mb min_mem_grant_used_mb max_mem_grant_used_mb stdev_mem_grant_used_mb last_mem_grant_used_mb count_query_executions ----------------------------------------- ---------------------- ---------------------- ---------------------- ------------------------ ----------------------- ---------------------- SELECT qtxt.query_sql_text ,CONVERT(D 550.16 550.00 550.00 0.00 550.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 61.00 36.00 65.00 10.87 51.00 14 SELECT qtxt.query_sql_text ,q.* ,rts 25.46 25.00 25.00 0.00 25.00 2 insert into #tmpStats select 5 'Database 13.69 13.00 13.00 0.03 13.00 16 SELECT q.* ,rts 11.93 11.00 12.00 0.23 12.00 2 SELECT * ,rts.avg_query_max_used_memory 9.70 9.00 9.00 0.00 9.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 9.32 9.00 9.00 0.00 9.00 1 select db_id() dbid, db_name() dbname, * 7.33 7.00 7.00 0.00 7.00 9 SELECT q.* ,rts.avg_query_max_used_memo 6.65 6.00 6.00 0.00 6.00 1 (@_msparam_0 nvarchar(4000),@_msparam_1 5.17 4.00 5.00 0.68 4.00 2
Настраиваемый диагностический запрос
Ниже приведен запрос, который объединяет данные из нескольких представлений, включая три перечисленных ранее. Он обеспечивает более тщательное представление сеансов и их грантов через sys.dm_exec_requests и sys.dm_exec_query_memory_grants, помимо статистики на уровне сервера, предоставляемой sys.dm_exec_query_resource_semaphores.
Примечание.
Этот запрос вернет две строки на сеанс из-за использования sys.dm_exec_query_resource_semaphores (одна строка для регулярного семафора ресурса и другого для семафора ресурса малого запроса).
SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime
, r.session_id
, r.wait_time
, r.wait_type
, mg.request_time
, mg.grant_time
, mg.requested_memory_kb
/ 1024 requested_memory_mb
, mg.granted_memory_kb
/ 1024 AS granted_memory_mb
, mg.required_memory_kb
/ 1024 AS required_memory_mb
, max_used_memory_kb
/ 1024 AS max_used_memory_mb
, rs.pool_id as resource_pool_id
, mg.query_cost
, mg.timeout_sec
, mg.resource_semaphore_id
, mg.wait_time_ms AS memory_grant_wait_time_ms
, CASE mg.is_next_candidate
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Memory has been granted'
END AS 'Next Candidate for Memory Grant'
, r.command
, ltrim(rtrim(replace(replace (substring (q.text, 1, 1000), char(10), ' '), char(13), ' '))) [text]
, rs.target_memory_kb
/ 1024 AS server_target_grant_memory_mb
, rs.max_target_memory_kb
/ 1024 AS server_max_target_grant_memory_mb
, rs.total_memory_kb
/ 1024 AS server_total_resource_semaphore_memory_mb
, rs.available_memory_kb
/ 1024 AS server_available_memory_for_grants_mb
, rs.granted_memory_kb
/ 1024 AS server_total_granted_memory_mb
, rs.used_memory_kb
/ 1024 AS server_used_granted_memory_mb
, rs.grantee_count AS successful_grantee_count
, rs.waiter_count AS grant_waiters_count
, rs.timeout_error_count
, rs.forced_grant_count
, mg.dop
, r.blocking_session_id
, r.cpu_time
, r.total_elapsed_time
, r.reads
, r.writes
, r.logical_reads
, r.row_count
, s.login_time
, d.name
, s.login_name
, s.host_name
, s.nt_domain
, s.nt_user_name
, s.status
, c.client_net_address
, s.program_name
, s.client_interface_name
, s.last_request_start_time
, s.last_request_end_time
, c.connect_time
, c.last_read
, c.last_write
, qp.query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_connections c
ON r.connection_id = c.connection_id
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
INNER JOIN sys.databases d
ON r.database_id = d.database_id
INNER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
INNER JOIN sys.dm_exec_query_resource_semaphores rs
ON mg.resource_semaphore_id = rs.resource_semaphore_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle ) AS q
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
OPTION (MAXDOP 1, LOOP JOIN )
Примечание.
Указание LOOP JOIN используется в этом диагностическом запросе, чтобы избежать предоставления памяти самим запросом, и предложение не ORDER BY используется. Если диагностический запрос заканчивается ожиданием самого гранта, его цель диагностики предоставления памяти будет поражена. Указание LOOP JOIN может привести к замедлению диагностического запроса, но в этом случае важно получить результаты диагностики.
Ниже приведен сокращенный пример выходных данных из этого диагностического запроса только с выбранными столбцами.
| session_id | wait_time | wait_type | requested_memory_mb | granted_memory_mb | required_memory_mb | max_used_memory_mb | resource_pool_id |
|---|---|---|---|---|---|---|---|
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 1 |
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 2 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
В примере выходных данных четко показано, как запрос, отправленный session_id = 60 успешно получил запрошенное разрешение памяти размером 9 МБ, но для успешного запуска выполнения запроса требовалось только 7 МБ. В конце концов, запрос использовал только 1 МБ из 9 МБ, полученных от сервера. В выходных данных также показано, что сеансы 75 и 86 ожидают предоставления памяти, таким образом RESOURCE_SEMAPHORE wait_type. Время ожидания было более 1300 секунд (21 минут), и их granted_memory_mb .NULL
Этот диагностический запрос является примером, поэтому вы можете изменить его таким образом, чтобы соответствовать вашим потребностям. Версия этого запроса также используется в средствах диагностики, которые поддерживает Microsoft SQL Server.
Средства диагностики
Существуют средства диагностики, которые служба технической поддержки Microsoft SQL Server использует для сбора журналов и более эффективного устранения неполадок. Диспетчер конфигурации Sql LogScout и Pssdiag (вместе с SQLDiag) собирают выходные данные ранее описанных динамических административных представлений и счетчиков Монитор производительности, которые помогают диагностировать проблемы с предоставлением памяти.
При запуске SQL LogScout с помощью LightPerf, GeneralPerf или DetailedPerf средство собирает необходимые журналы. Затем можно вручную проверить YourServer_PerfStats.out и найти -- dm_exec_query_resource_semaphores -- и -- dm_exec_query_memory_grants -- выходные данные. Кроме того, вместо ручного изучения можно использовать SQL Nexus для импорта выходных данных, поступающих из SQL LogScout или PSSDIAG в базу данных SQL Server. Sql Nexus создает две таблицы и tbl_dm_exec_query_memory_grantsсодержит сведения, tbl_dm_exec_query_resource_semaphores необходимые для диагностики предоставления памяти. SQL LogScout и PSSDIAG также собирают журналы Perfmon в виде. ФАЙЛЫ BLG, которые можно использовать для проверки счетчиков производительности, описанных в разделе счетчиков Монитор производительности.
Почему ресурсы памяти важны для разработчика или DBA
На основе поддержки Майкрософт проблемы предоставления памяти, как правило, являются одними из наиболее распространенных проблем, связанных с памятью. Приложения часто выполняют, казалось бы, простые запросы, которые могут привести к проблемам с производительностью в SQL Server из-за огромных операций сортировки или хэша. Такие запросы не только потребляют много памяти SQL Server, но и вызывают другие запросы, чтобы ожидать доступности памяти, таким образом, узкие места производительности.
Используя средства, описанные здесь (динамические административные представления, счетчики Perfmon и фактические планы запросов), можно определить, какие запросы являются потребителями больших грантов. Затем можно настроить или перезаписать эти запросы для разрешения или уменьшения использования памяти рабочей области.
Что может сделать разработчик о операциях сортировки и хэша
После выявления конкретных запросов, использующих большое количество памяти резервирования запросов, можно выполнить шаги по сокращению предоставления памяти путем перепроектирования этих запросов.
Причины сортировки и хэш-операций в запросах
Первый шаг заключается в том, чтобы узнать, какие операции в запросе могут привести к предоставлению памяти.
Причины, по которым запрос будет использовать оператор SORT:
ORDER BY (T-SQL) приводит к сортировке строк перед потоковой передачей в конечный результат.
GROUP BY (T-SQL) может ввести оператор сортировки в плане запроса перед группировкой, если базовый индекс не присутствует, чтобы упорядочить сгруппированные столбцы.
ФУНКЦИЯ DISTINCT (T-SQL) ведет себя аналогично
GROUP BY. Чтобы определить отдельные строки, промежуточные результаты упорядочены, а затем дубликаты удаляются. Оптимизатор используетSortоператор до этого оператора, если данные еще не отсортированы из-за упорядоченного поиска или сканирования индекса.Оператор объединения слиянием при выборе оптимизатора запросов требует сортировки обоих присоединенных входных данных. SQL Server может активировать сортировку, если кластеризованный индекс недоступен в столбце соединения в одной из таблиц.
Причины, по которым запрос будет использовать оператор плана запроса HASH:
Этот список не является исчерпывающим, но включает наиболее распространенные причины для хэш-операций. Анализ плана запроса для определения операций хэш-сопоставления.
JOIN (T-SQL): при присоединении таблиц SQL Server имеет выбор между тремя физическими операторами,
Nested LoopMerge JoinиHash Join. Если SQL Server в конечном итоге выбирает хэш-соединение, для хранения и обработки промежуточных результатов требуется память QE. Как правило, отсутствие хороших индексов может привести к этому наиболее ресурсоемком оператору соединения.Hash JoinСведения о плане запроса для идентификацииHash Matchсм. в справочнике по логическим и физическим операторам.DISTINCT (T-SQL):
Hash Aggregateоператор может использоваться для устранения дубликатов в наборе строк. Сведения оHash Matchпоиске (Aggregate) в плане запроса см . в справочнике по логическим и физическим операторам.UNION (T-SQL): это похоже
DISTINCTна . МожноHash Aggregateиспользовать для удаления дубликатов для этого оператора.SUM/AVG/MAX/MIN (T-SQL): любая статистическая операция может быть выполнена в качестве.
Hash AggregateСведения оHash Matchпоиске (Aggregate) в плане запроса см . в справочнике по логическим и физическим операторам.
Зная эти распространенные причины, вы можете устранить как можно больше запросов на предоставление памяти, поступающих в SQL Server.
Способы уменьшения количества операций сортировки и хэша или размера гранта
- Следите за актуальной статистикой . Этот фундаментальный шаг, который повышает производительность запросов на многих уровнях, гарантирует, что оптимизатор запросов имеет наиболее точные сведения при выборе планов запросов. SQL Server определяет размер запроса на предоставление памяти на основе статистики. Устаревшая статистика может привести к чрезмерному оценке или недооценению запроса на предоставление и, следовательно, привести к ненужно высокому запросу на предоставление или к разливу результатов на диск соответственно. Убедитесь, что статистика автоматического обновления включена в базах данных и (или) сохраняет статические данные с помощью UPDATE STATISTICS или sp_updatestats.
- Уменьшите количество строк, поступающих из таблиц. Если вы используете более строгий фильтр WHERE или JOIN и уменьшите количество строк, последующий сорт в плане запроса получает порядок или агрегировать меньший результирующий набор. Для меньшего промежуточного результирующих набора требуется меньше рабочей памяти. Это общее правило, которое разработчики могут следовать не только за сохранением памяти рабочего набора, но и для уменьшения ЦП и операций ввода-вывода (этот шаг не всегда возможен). Если уже существуют хорошо написанные и эффективные запросы, это руководство было выполнено.
- Создайте индексы для столбцов соединения, помогая объединить соединения. Промежуточные операции в плане запроса влияют на индексы базовой таблицы. Например, если таблица не имеет индекса в столбце соединения, а соединение слиянием является наиболее эффективным оператором соединения, все строки из этой таблицы должны быть отсортированы перед выполнением соединения. Если вместо этого индекс существует в столбце, можно устранить операцию сортировки.
- Создайте индексы, чтобы избежать хэш-операций. Обычно базовая настройка запросов начинается с проверки того, имеют ли запросы соответствующие индексы, чтобы помочь им сократить количество операций чтения и свести к минимуму или исключить крупные операции сортировки или хэширования, где это возможно. Хэш-соединения обычно выбираются для обработки больших, несортированных и неиндексированных входных данных. Создание индексов может изменить эту стратегию оптимизатора и ускорить извлечение данных. Дополнительные сведения о создании индексов см. в разделе помощник по настройке ядра СУБД и настройка некластеризованных индексов с отсутствующими предложениями по индексу.
- Используйте индексы COLUMNSTORE, подходящие для запросов агрегирования, которые используются
GROUP BY. Аналитические запросы, которые работают с очень большими наборами строк и обычно выполняют агрегирование "группировать по" может потребовать больших блоков памяти для выполнения работы. Если индекс недоступен, предоставляющий упорядоченные результаты, сортировка автоматически вводится в плане запроса. Такой большой результат может привести к дорогостоящим грантам памяти. - Удалите его
ORDER BY, если он не нужен. В случаях, когда результаты передаются в приложение, которое сортирует результаты по своему усмотрению или позволяет пользователю изменять порядок просмотра данных, вам не нужно выполнять сортировку на стороне SQL Server. Просто потоковая передача данных в приложение в том порядке, когда сервер создает его и позволяет конечным пользователям самостоятельно отсортировать его. Такие приложения отчетов, как Power BI или Reporting Services, являются примерами таких приложений, которые позволяют конечным пользователям сортировать свои данные. - Учитывайте, хотя и осторожно, использование указания LOOP JOIN при наличии соединений в запросе T-SQL. Этот метод может избежать хэш-соединений или соединений слиянием, использующих предоставление памяти. Однако этот параметр предлагается только в качестве последнего способа, так как принудительное присоединение может привести к значительно более медленному запросу. Протестируйте рабочую нагрузку, чтобы убедиться, что это вариант. В некоторых случаях соединение вложенного цикла может даже не быть вариантом. В этом случае SQL Server может завершиться ошибкой MSSQLSERVER_8622" "Обработчик запросов не смог создать план запроса из-за указаний, определенных в этом запросе".
Указание запроса на предоставление памяти
Так как SQL Server 2012 с пакетом обновления 3 (SP3) существует указание запроса, позволяющее управлять размером предоставления памяти для каждого запроса. Ниже приведен пример использования этого указания:
SELECT Column1, Column2
FROM Table1
ORDER BY Column1
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )
Мы рекомендуем использовать здесь консервативные значения, особенно в тех случаях, когда ожидается одновременное выполнение большого количества экземпляров запроса. Убедитесь, что вы протестируете рабочую нагрузку в соответствии с рабочей средой и определите, какие значения следует использовать.
Дополнительные сведения см. в MAX_GRANT_PERCENT и MIN_GRANT_PERCENT.
Resource Governor
Память QE — это память, которую регулятор ресурсов фактически ограничивает при использовании параметров MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT . Когда вы определите запросы, вызывающие большие объемы памяти, можно ограничить память, используемую сеансами или приложениями. Следует отметить, что default группа рабочих нагрузок позволяет запросу занять до 25 % памяти, которую можно предоставить на экземпляре SQL Server. Дополнительные сведения см. в разделе Пулы ресурсов регулятора ресурсов и CREATE WORKLOAD GROUP.
Обратная связь с адаптивной обработкой запросов и предоставлением памяти
SQL Server 2017 представила функцию предоставления отзывов о предоставлении памяти. Он позволяет обработчику выполнения запросов настроить предоставленный запросу на основе предыдущей истории. Цель состоит в том, чтобы уменьшить размер гранта, если это возможно или увеличить его, когда требуется больше памяти. Эта функция была выпущена в трех волнах:
- Отзыв о предоставлении памяти в пакетном режиме в SQL Server 2017
- Отзыв о предоставлении памяти в режиме строк в SQL Server 2019
- Обратная связь о предоставлении памяти на диске с помощью хранилище запросов и процентильного гранта в SQL Server 2022
Дополнительные сведения см. в отзыве о предоставлении памяти. Функция предоставления памяти может уменьшить размер грантов памяти для запросов во время выполнения и, таким образом, уменьшить проблемы, связанные с большими запросами на предоставление. Благодаря этой функции, особенно в SQL Server 2019 и более поздних версиях, где доступна адаптивная обработка в режиме строк, вы даже не заметите проблемы с памятью, поступающие от выполнения запроса. Однако если у вас есть эта функция (по умолчанию) и по-прежнему отображается большое потребление памяти QE, примените шаги, описанные ранее для перезаписи запросов.
Увеличение памяти SQL Server или ОС
После выполнения действий по сокращению ненужных грантов памяти для запросов, если у вас по-прежнему возникают проблемы с низкой памятью, рабочая нагрузка, скорее всего, требует больше памяти. Поэтому рекомендуется увеличить объем памяти для SQL Server с помощью max server memory параметра, если в системе достаточно физической памяти. Следуйте рекомендациям по выходу около 25% памяти для ОС и других потребностей. Дополнительные сведения см. в разделе "Параметры конфигурации памяти сервера". Если в системе недостаточно памяти, попробуйте добавить физический ОЗУ или если это виртуальная машина, увеличьте выделенную память для виртуальной машины.
Внутренние ресурсы предоставления памяти
Дополнительные сведения о некоторых внутренних компонентах в памяти выполнения запросов см. в записи блога о предоставлении памяти SQL Server.
Создание сценария производительности с большим объемом использования памяти
Наконец, в следующем примере показано, как имитировать большое потребление памяти выполнения запроса и вводить запросы, RESOURCE_SEMAPHOREожидающие. Это можно сделать, чтобы узнать, как использовать средства диагностики и методы, описанные в этой статье.
Предупреждение
Не используйте это в рабочей системе. Это моделирование предоставляется, чтобы помочь вам понять концепцию и помочь вам лучше узнать его.
На тестовом сервере установите служебные программы RML и SQL Server.
Используйте клиентское приложение, например SQL Server Management Studio, чтобы снизить максимальный размер памяти сервера sql Server до 1500 МБ:
EXEC sp_configure 'max server memory', 1500 RECONFIGUREОткройте командную строку и измените каталог на папку служебных программ RML:
cd C:\Program Files\Microsoft Corporation\RMLUtilsИспользуйте ostress.exe для создания нескольких одновременных запросов к тестовой среде SQL Server. В этом примере используется 30 одновременных сеансов, но это значение можно изменить:
ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30Используйте средства диагностики, описанные ранее, для выявления проблем с предоставлением памяти.
Сводка способов обработки больших объемов памяти
- Перезапись запросов.
- Обновляйте статистику и регулярно обновляйте их.
- Создайте соответствующие индексы для определенного запроса или запроса. Индексы могут уменьшить большое количество обработанных строк, изменяя
JOINалгоритмы и уменьшая размер грантов или полностью устраняя их. - Используйте подсказку
OPTION(min_grant_percent = XX, max_grant_percent = XX). - Используйте регулятор ресурсов.
- SQL Server 2017 и 2019 используют адаптивную обработку запросов, позволяя механизму обратной связи предоставления памяти динамически настраивать размер предоставления памяти во время выполнения. Эта функция может предотвратить проблемы с предоставлением памяти в первую очередь.
- Увеличьте память SQL Server или ОС.