Устранение проблем с низкой производительностью или нехваткой памяти, вызванных предоставлением памяти в SQL Server
Что такое предоставление памяти?
Предоставление памяти, также называемое резервированиями выполнения запросов( QE), памятью выполнения запросов, памятью рабочей области и резервированием памяти, описывает использование памяти во время выполнения запроса. SQL Server выделяет эту память во время выполнения запроса для одной или нескольких из следующих целей:
- Операции сортировки
- Операции хэширования
- Операции массового копирования (не распространенная проблема)
- Создание индекса, включая вставку в индексы COLUMNSTORE, так как хэш-словари и таблицы используются во время выполнения для построения индекса (не распространенная проблема).
Чтобы обеспечить определенный контекст, запрос может запрашивать память от разных распределительных средств или клерков в зависимости от того, что ему нужно сделать. Например, при первоначальном анализе и компиляции запроса он потребляет память компиляции. После компиляции запроса эта память освобождается, а полученный план запроса сохраняется в памяти кэша планов. После кэширования плана запрос готов к выполнению. Если запрос выполняет какие-либо операции сортировки, операции хэш-сопоставления (JOIN или агрегаты) или вставки в индексы COLUMNSTORE, он использует память из распределителя выполнения запроса. Первоначально запрос запрашивает эту память выполнения, а затем, если эта память предоставлена, запрос использует всю память или часть памяти для сортировки результатов или хэш-сегментов. Эта память, выделенная во время выполнения запроса, называется предоставлением памяти. Как вы можете себе представить, после завершения операции выполнения запроса выделение памяти освобождается обратно в SQL Server, чтобы использовать для других работ. Таким образом, выделение памяти является временным по своей природе, но может продолжаться долго. Например, если выполнение запроса выполняет операцию сортировки для очень большого набора строк в памяти, сортировка может занять много секунд или минут, а предоставленная память используется на время существования запроса.
Пример запроса с предоставлением памяти
Ниже приведен пример запроса, который использует память выполнения и его план запроса, показывающий предоставление:
SELECT *
FROM sys.messages
ORDER BY message_id
Этот запрос выбирает набор строк из более чем 300 000 строк и сортирует его. Операция сортировки вызывает запрос на предоставление памяти. При выполнении этого запроса в SSMS можно просмотреть его план запроса. При выборе левого SELECT оператора плана запроса можно просмотреть сведения о предоставлении памяти для запроса (нажмите клавишу F4 , чтобы отобразить свойства):
Кроме того, если щелкнуть правой кнопкой мыши пробел в плане запроса, можно выбрать Показать 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 and Worktables in Memory Grant Considerations (Рекомендации по предоставлению памяти).
Терминология
Давайте рассмотрим различные термины, которые могут возникнуть в отношении этого потребителя памяти. Опять же, все они описывают концепции, связанные с тем же выделением памяти.
Память выполнения запросов (память QE): Этот термин используется, чтобы подчеркнуть тот факт, что сортировка или хэш-память используется во время выполнения запроса. Как правило, память QE является крупнейшим потребителем памяти в течение всего срока действия запроса.
Резервирования выполнения запросов (QE) или резервирования памяти: Если запросу требуется память для сортировки или хэш-операций, он выполняет запрос на резервирование памяти. Этот запрос на резервирование вычисляется во время компиляции на основе предполагаемой кратности. Позже при выполнении запроса SQL Server предоставить этот запрос частично или полностью в зависимости от доступности памяти. В конечном итоге запрос может использовать процент предоставленной памяти. Существует клерк памяти (бухгалтер памяти) с именем "MEMORYCLERK_SQLQERESERVATIONS", который отслеживает эти выделения памяти (проверка dbCC MEMORYSTATUS или sys.dm_os_memory_clerks).
Предоставление памяти: Когда SQL Server предоставляет запрошенную память выполняющимся запросам, сообщается, что произошло предоставление памяти. Существует несколько счетчиков производительности, использующих термин "предоставление". Эти счетчики и
Memory Grants OutstandingMemory Grants Pendingотображают количество предоставленных памяти, удовлетворенных или ожидающих. Они не учитывают размер предоставления памяти. Только один запрос может потреблять, например, 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 недоступен в базе данных Azure SQL). После выявления проблемы необходимо выполнить детализацию на уровне отдельных запросов, чтобы узнать, какие запросы нуждаются в настройке или перезаписи.
На уровне сервера используйте следующие методы:
- Sys.dm_exec_query_resource_semaphores динамического административного управления семафором ресурса Дополнительные сведения см. в разделе sys.dm_exec_query_resource_semaphores.
- Монитор производительности счетчики Дополнительные сведения см. в разделе SQL Server объект диспетчера памяти.
- 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
В следующем примере выходных данных показано, что около 900 МБ памяти для выполнения запросов используется 22 запросами, а еще 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 объект диспетчера памяти.
- SQLServer, диспетчер памяти: максимальный объем памяти рабочей области (КБ)
- SQLServer, диспетчер памяти: невыполненные разрешения на использование памяти
- SQLServer, диспетчер памяти: ожидается предоставление памяти
- SQLServer, диспетчер памяти: предоставленная память рабочей области (КБ)
DBCC MEMORYSTATUS
Еще одно место, где можно просмотреть сведения о памяти резервирования запросов, — (DBCC MEMORYSTATUSраздел Объекты памяти запросов). Вы можете просмотреть выходные Query Memory Objects (default) данные для пользовательских запросов. Например, если вы включили Resource Governor с пулом ресурсов с именем 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 динамического административного представления клерков памяти
Если вам требуется больше табличного результирующий набор, отличный от набора на основе 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 представление. В этом динамическом административном представлении отображаются сведения о выполняемых в настоящее время запросах, а не исторические сведения.
Следующая инструкция получает данные из динамического административного представления, а также получает текст запроса и план запроса в результате:
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_secsys.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. Время существования данных привязано к плану запроса каждого запроса. При удалении плана из кэша планов соответствующие строки удаляются из этого представления. Другими словами, динамическое административное представление сохраняет статистику в памяти, которая не сохраняется после перезапуска 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_SEMAPHOREwait_type. Их время ожидания составляет более 1300 секунд (21 минута), а их granted_memory_mb значение — NULL.
Этот диагностический запрос является примером, поэтому вы можете изменить его любым способом в соответствии с вашими потребностями. Версия этого запроса также используется в средствах диагностики, которые поддерживает microsoft SQL Server.
Средства диагностики
Существуют средства диагностики, которые служба технической поддержки Майкрософт SQL Server использует для сбора журналов и более эффективного устранения неполадок. Sql LogScout и Pssdiag Configuration Manager (вместе с 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оператор до этого оператора, если данные еще не отсортированы из-за упорядоченного поиска или сканирования индекса.Оператор Merge Join , выбранный оптимизатором запросов, требует сортировки обоих объединенных входных данных. SQL Server может активировать сортировку, если кластеризованный индекс недоступен в столбце соединения в одной из таблиц.
Причины, по которым в запросе используется оператор плана запроса HASH:
Этот список не является исчерпывающим, но включает наиболее часто встречающиеся причины для хэш-операций. Проанализируйте план запроса , чтобы определить операции сопоставления хэша.
JOIN (T-SQL): при соединении таблиц SQL Server имеет выбор между тремя физическими операторами,
Nested Loop,Merge 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 — это память, которая Resource Governor фактически ограничивается при использовании параметров MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT. Определив запросы, которые вызывают большие объемы памяти, можно ограничить объем памяти, используемый сеансами или приложениями. Следует отметить, что группа рабочей нагрузки default позволяет запросу занимать до 25 % памяти, которая может быть предоставлена экземпляру SQL Server. Дополнительные сведения см. в разделах Resource Governor Пулы ресурсов и 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). - Используйте Resource Governor.
- SQL Server 2017 и 2019 годах используют адаптивную обработку запросов, что позволяет механизму обратной связи с предоставлением памяти динамически изменять размер предоставления памяти во время выполнения. Эта функция может в первую очередь предотвратить проблемы с выделением памяти.
- Увеличьте объем памяти SQL Server или ОС.
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по