Руководство по архитектуре управления памятью

Статья
05/30/2023

Применимо к:SQL Server Azure SQL DatabaseAzure Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Диспетчер виртуальной памяти Windows

Определенные области адресного пространства сопоставляются с физической памятью диспетчером виртуальной памяти Windows (VMM).

Дополнительные сведения об объеме физической памяти, поддерживаемой различными операционными системами, см. в разделе Предельный объем памяти для выпусков Windows в документации Windows.

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

Архитектура памяти SQL Server

SQL Server по мере необходимости динамически получает и освобождает память. Как правило, администратору не нужно указывать, сколько памяти должно быть выделено SQL Server, хотя этот параметр по-прежнему существует и требуется в некоторых средах.

Одна из главных задач проектирования любой программной системы для баз данных — минимизация операций дискового ввода-вывода, так как чтение и запись на диск являются наиболее ресурсоемкими операциями. SQL Server создает в памяти буферный пул для сохранения страниц, считываемых из базы данных. Большой объем кода SQL Server предназначен для минимизации числа физических операций чтения-записи между диском и буферным пулом. SQL Server выполняет балансировку для решения двух задач:

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

В сильно загруженной системе некоторые большие запросы, требующие большого объема памяти для выполнения, не могут получить минимальный объем запрошенной памяти и получить ошибку времени ожидания ресурсов памяти. Для решения этой проблемы следует увеличить значение параметра query wait. При параллельных запросах можно попробовать уменьшить значение параметра max degree of parallelism.

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

Обычная (виртуальная) память

Все выпуски SQL Server поддерживают обычную память на 64-разрядной платформе. Процесс SQL Server может получить доступ к виртуальному адресу до операционной системы в архитектуре x64 (SQL Server Standard Edition поддерживает до 128 ГБ). С архитектурой IA64 ограничение составило 7 ТБ (IA64 не поддерживается в SQL Server 2012 (11.x) и выше). Дополнительные сведения см. в разделе "Ограничения памяти" для Windows .

Адрес памяти расширений Windows (AWE)

С помощью расширений адресного окна (AWE) и привилегии блокировки страниц в памяти (LPIM), необходимых AWE, можно сохранить большую часть памяти процесса SQL Server, заблокированную в физической оперативной памяти в условиях низкой виртуальной памяти. Это происходит как в 32-разрядных, так и 64-разрядных выделениях AWE. Блокировка памяти возникает, так как память AWE не проходит через диспетчер виртуальной памяти в Windows, который управляет разбиением памяти на страницы. API выделения памяти AWE требует привилегии "Блокировка страниц в памяти" (SeLockMemoryPrivilege), см. в заметках AllocateUserPhysicalPages. Поэтому основное преимущество использования API AWE заключается в сохранении большей части памяти в ОЗУ, если в системе возникает давление на память. Сведения о том, как разрешить SQL Server использовать AWE, см. в разделе "Включить страницы блокировки" в памяти.

Если LPIM предоставлено, настоятельно рекомендуется задать максимальное значение памяти сервера (МБ), а не оставить значение по умолчанию 2 147 483 647 мегабайт (МБ). Дополнительные сведения см. в разделе "Конфигурация сервера памяти сервера" — настройка параметров вручную и блокировка страниц в памяти (LPIM).

Если LPIM не включен, SQL Server переключится на использование обычной памяти и в случаях нехватки памяти ОС, в журнале ошибок может быть сообщена ошибка 17890 . Ошибка похожа на следующий пример:

A significant part of SQL Server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

Изменения в управлении памятью, начиная с SQL Server 2012 (11.x)

В более старых версиях SQL Server выделение памяти было выполнено с помощью пяти различных механизмов:

Одностраничные распределитель (SPA), включая только выделения памяти, которые были меньше или равны 8 КБ в процессе SQL Server. Пределы физической памяти, используемой SPA, определяют параметры конфигурации Макс. памяти сервера (МБ) и Мин. памяти сервера (МБ). Буферный пул был одновременно механизмом для SPA и самым крупным потребителем одностраничных выделений.
Многостраничный распределитель (MPA) для выделения памяти объемом больше 8 КБ.
Распределитель CLR, в том числе кучи SQL CLR и глобального выделения памяти во время инициализации CLR.
Выделение памяти для стеков потоков в процессе SQL Server.
Прямое выделение памяти Windows (DWA) для запросов на выделение памяти, отправленных напрямую в Windows. К ним относятся использование кучи Windows и прямые виртуальные выделения, сделанные модулями, загруженными в процесс SQL Server. Примеры таких запросов на выделение включают выделение в библиотеках DLL с расширенными хранимыми процедурами, объекты, созданные с помощью процедур автоматизации (вызовов sp_OA), и выделение со стороны связанных поставщиков сервера.

Начиная с SQL Server 2012 (11.x), выделения на одной странице, выделения на нескольких страницах и выделения среды CLR все объединяются в "Любой размер" Allocator страницы и включаются в ограничения памяти, контролируемые максимальным объемом памяти сервера (МБ) и параметрами конфигурации минимальной памяти сервера (МБ). Это изменение обеспечило более точную возможность изменения размера для всех требований к памяти, которые проходят через диспетчер памяти SQL Server.

Внимание

Внимательно просмотрите текущие конфигурации максимальной памяти сервера (МБ) и минимальной памяти сервера (МБ) после обновления до SQL Server 2012 (11.x) и более поздних версий. Это связано с тем, что начиная с SQL Server 2012 (11.x), такие конфигурации теперь включают и учитывают дополнительные выделения памяти по сравнению с более ранними версиями. Эти изменения применяются как к 32-разрядным, так и 64-разрядным версиям SQL Server 2012 (11.x) и SQL Server 2014 (12.x) и 64-разрядным версиям SQL Server 2016 (13.x) и более поздним версиям.

В следующей таблице указано, управляется ли определенный тип выделения памяти параметрами конфигурации Макс. памяти сервера (МБ) и Мин. памяти сервера (МБ).

Тип выделения памяти	SQL Server 2005 (9.x), SQL Server 2008 (10.0.x) и SQL Server 2008 R2 (10.50.x)	Начиная с SQL Server 2012 (11.x)
Одностраничные выделения	Да	Да, объединяются в выделения страниц "Любой размер"
Многостраничные выделения	No	Да, объединяются в выделения страниц "Любой размер"
Выделения CLR	No	Да
Память стеков потоков	No	No
Прямые выделения из Windows	No	No

Начиная с SQL Server 2012 (11.x), SQL Server может выделить больше памяти, чем значение, указанное в параметре максимальной памяти сервера (МБ ). Это может произойти, если значение общего объема памяти сервера (КБ) уже достигло параметра целевой памяти сервера (КБ), как указано в максимальной памяти сервера (МБ). Если недостаточно непрерывной памяти для удовлетворения спроса на многостраничные запросы памяти (более 8 КБ) из-за фрагментации памяти, SQL Server может выполнить чрезмерное обязательство, а не отклонять запрос памяти.

Как только это выделение будет выполнено, фоновая задача "Монитор ресурсов" начинает сигнализировать всем потребителям памяти освободить выделенную память и пытается привести значение total Server Memory (KB) ниже спецификации целевой памяти сервера (КБ ). Таким образом, использование памяти SQL Server может кратко превышать максимальный параметр памяти сервера (МБ ). В этой ситуации счетчик производительности общего объема памяти сервера (КБ) превысит максимальный объем памяти сервера (МБ) и параметры целевой памяти сервера (КБ ).

Это поведение обычно возникает в следующих операциях.

Запросы больших индексов columnstore
Большой пакетный режим в запросах rowstore
Сборки индекса Columnstore (re), которые используют большие объемы памяти для выполнения операций хэширования и сортировки
Операции резервного копирования, требующие больших буферов памяти
Операции трассировки, которые должны хранить большие входные параметры

Изменения memory_to_reserve начиная с SQL Server 2012 (11.x)

В более старых версиях SQL Server диспетчер памяти SQL Server отложит часть виртуального адресного пространства процесса (VAS) для использования многостраничного распределителя (MPA), clR Allocator, выделения памяти для стеков потоков в процессе SQL Server и прямых выделений Windows (DWA). Эта часть виртуального адресного пространства также называется регионом оставляемой памяти или буферным пулом.

Виртуальное адресное пространство, зарезервированное для этих выделений, определяется параметром конфигурации memory_to_reserve. Значение по умолчанию, используемое SQL Server, равно 256 МБ.

Так как распределитель страницы "любой размер" также обрабатывает выделения, превышающие 8 КБ, значение memory_to_reserve не включает многостраничные выделения. Все остальное в отношении этого параметра конфигурации остается без изменений.

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

Тип выделения памяти	SQL Server 2005 (9.x), SQL Server 2008 (10.0.x) и SQL Server 2008 R2 (10.50.x)	Начиная с SQL Server 2012 (11.x)
Одностраничные выделения	No	Нет, объединяется в выделения страниц "Любой размер"
Многостраничные выделения	Да	Нет, объединяется в выделения страниц "Любой размер"
Выделения CLR	Да	Да
Память стеков потоков	Да	Да
Прямые выделения из Windows	Да	Да

Динамическое управление памятью

Поведение управления памятью по умолчанию ядра СУБД SQL Server — получить столько памяти, сколько требуется, не создавая нехватку памяти в системе. Ядро СУБД SQL Server делает это с помощью API уведомлений о памяти в Microsoft Windows.

При динамическом использовании памяти SQL Server система периодически запрашивает объем свободной памяти. Поддержание достаточного объема свободной памяти позволяет избежать подкачки в операционной системе (ОС). Если объем памяти свободен, SQL Server освобождает память операционной системы. Если объем памяти свободен, SQL Server может выделить больше памяти. SQL Server добавляет память только в том случае, если для рабочей нагрузки требуется больше памяти; Неактивный сервер не увеличивает размер своего виртуального адресного пространства. Если вы заметили, что диспетчер задач и монитор производительности показывают устойчивое уменьшение доступной памяти при использовании динамического управления памятью SQL Server, это поведение по умолчанию и не должно рассматриваться как утечка памяти.

Максимальный объем памяти сервера управляет выделением памяти SQL Server, компиляцией памяти, всеми кэшами (включая буферный пул), предоставлением памяти выполнения запросов, памятью диспетчера блокировки и памятью CLR¹ (по сути, любым клерком памяти, найденным в sys.dm_os_memory_clerks).

¹ память CLR управляется в рамках выделения max_server_memory начиная с SQL Server 2012 (11.x).

Следующий запрос возвращает информацию о текущей выделенной памяти.

SELECT
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count AS sql_page_fault_count,
    memory_utilization_percentage AS sql_memory_utilization_percentage,
    process_physical_memory_low AS sql_process_physical_memory_low,
    process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

Размеры стека

Память для стеков ^{потоков 1}, CLR ², расширенных DLL-файлов, поставщиков OLE DB, на которые ссылаются распределенные запросы, объекты автоматизации, на которые ссылается инструкция Transact-SQL, и любая память, выделенная не библиотекой DLL SQL Server, не контролируется максимальным объемом памяти сервера (МБ).

^{1 См.} статью о настройке параметра конфигурации сервера максимальных рабочих потоков для вычисляемых рабочих потоков по умолчанию для заданного количества аффинизованных ЦП в текущем узле. Размеры стека SQL Server приведены следующим образом:

Архитектура SQL Server	Архитектура ОС	Размер стека
x86 (32-разрядная версия)	x86 (32-разрядная версия)	512 КБ
x86 (32-разрядная версия)	x64 (64-разрядная версия)	768 КБ
x64 (64-разрядная версия)	x64 (64-разрядная версия)	2048 КБ
IA64 (Itanium)	IA64 (Itanium)	4096 КБ

² памяти CLR управляются в max_server_memory выделения, начиная с SQL Server 2012 (11.x).

SQL Server использует API уведомлений о памяти QueryMemoryResourceNotification , чтобы определить, когда диспетчер памяти SQL Server может выделить память и память выпуска.

При запуске SQL Server вычисляет размер виртуального адресного пространства для буферного пула на основе нескольких параметров, таких как объем физической памяти в системе, количество потоков сервера и различные параметры запуска. SQL Server резервирует вычисляемый объем виртуального адресного пространства процесса для буферного пула, но занимает (фиксирует) только необходимый объем физической памяти для текущей нагрузки.

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

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

Параметры настройки min server memory и max server memory

Параметры конфигурации минимальной памяти сервера и максимальной памяти сервера устанавливают верхние и нижние пределы объема памяти, используемого буферным пулом и другими кэшами ядра СУБД. Буферный пул не сразу получает объем памяти, указанной в минимальной памяти сервера. Он начинает расти от объема, необходимого для инициализации. По мере увеличения рабочей нагрузки ядра СУБД SQL Server она продолжает получать память, необходимую для поддержки рабочей нагрузки. Буферный пул не освобождает любую полученную память до тех пор, пока не достигнет объема, указанного в минимальной памяти сервера. Как только это значение будет достигнуто, буферный пул применит стандартный алгоритм выделения и освобождения памяти по мере необходимости. Единственное различие заключается в том, что буферный пул никогда не удаляет выделение памяти ниже уровня, указанного в минимальной памяти сервера, и никогда не получает больше памяти, чем уровень, указанный в максимальной памяти сервера (МБ).<

Заметка

SQL Server в качестве процесса получает больше памяти, чем указано в параметре max server memory (МБ). Как внутренние, так и внешние компоненты могут выделять память за пределами буферного пула, которая потребляет дополнительную память, но память, выделенная для буферного пула, обычно по-прежнему представляет большую часть памяти, потребляемой SQL Server.

Объем памяти, полученной ядром СУБД SQL Server, полностью зависит от рабочей нагрузки, размещенной на экземпляре. Экземпляр SQL Server, который не обрабатывает много запросов, может никогда не достигать минимальной памяти сервера.

Если для минимальной памяти сервера и максимальной памяти сервера задано одинаковое значение, то после того, как память, выделенная ядром СУБД SQL Server, достигает этого значения, ядро СУБД SQL Server перестает динамически освобождать и получать память для буферного пула.

Если экземпляр SQL Server работает на компьютере, на котором часто запускаются и останавливаются другие приложения, выделение и освобождение памяти экземпляром SQL может замедлить запуск других приложений. Кроме того, если SQL Server является одним из нескольких серверных приложений, выполняющихся на компьютере, системным администраторам может понадобиться возможность контроля памяти, выделяемой для SQL Server. В этих случаях можно использовать параметры минимальной памяти сервера и максимальной памяти сервера (МБ) для управления объемом памяти SQL Server. Значения параметров Мин. памяти сервера и Макс. памяти сервера указываются в мегабайтах. Дополнительные сведения, включая рекомендации по настройке этих конфигураций памяти, см. в статье Параметры конфигурации памяти сервера.

Требования к объему памяти для хранения объектов SQL Server

Следующий список содержит обобщенные сведения об объеме памяти, потребляемой разными объектами SQL Server. Эти значения являются оценочными и могут отличаться в зависимости от среды и способов создания объектов.

Блокировка (поддерживается диспетчером блокировки): 64 байта + 32 байта на владельца
Подключение пользователя: приблизительно (3 * network_packet_size + 94 КБ)

Размер сетевого пакета — это размер пакетов табличного потока данных (TDS), которые используются для обмена данными между приложениями и ядром СУБД. По умолчанию размер пакета равен 4 КБ, а его настройка осуществляется с помощью параметра конфигурации network packet size.

Если включено несколько активных результирующих наборов (MARS), подключение пользователя приблизительно (3 + 3 * num_logical_connections) * network_packet_size + 94 КБ.

Влияние параметра min memory per query

Параметр конфигурации min memory per query определяет минимальный объем памяти (в килобайтах), выделяемый для выполнения запроса. Он также называется минимальным временно предоставляемым буфером памяти. Все запросы должны ожидать выполнения до момента освобождения необходимого объема памяти либо истечения времени ожидания, указанного в параметре конфигурации сервера query wait. Тип ожидания, накопленный в этом сценарии RESOURCE_SEMAPHORE.

Внимание

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

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

Рекомендации по использованию этого параметра см. в статье Настройка параметра конфигурации сервера min memory per query.

Управление буферами

Главное назначение базы данных SQL Server — хранение и поиск данных, поэтому интенсивное использование операций дискового ввода-вывода — это основное свойство ядра СУБД. А так как дисковые операции ввода-вывода могут потреблять много ресурсов, а их выполнение может занимать довольно много времени, SQL Server обеспечивает рациональное использование операций ввода-вывода. Управление буфером — это ключевой компонент в достижении этой рациональности. Компонент управления буферами состоит из двух механизмов: диспетчера буферов для доступа к страницам баз данных и их обновления и буферного кэша (также называемого буферным пулом) для сокращения числа операций ввода-вывода файла базы данных.

Принцип работы управления буфером

Буфер — это 8-килобайтовая (КБ) страница в памяти такого же размера, что и страница данных или индекса. Буферный кэш делится на 8-килобайтовые страницы. Диспетчер буферов содержит функции чтения страниц данных или индекса из файлов базы данных на диске в буферный кэш и записывает измененные страницы обратно на диск. Страница остается в буферном кэше, пока диспетчеру буферов требуется область буфера для чтения дополнительных данных. Данные записываются обратно на диск, только если они были изменены. Данные в буферном кэше могут измениться несколько раз, прежде чем будут сохранены обратно на диске. Дополнительные сведения см. в статьях Считывание страниц и Запись страниц.

При запуске SQL Server вычисляет размер виртуального адресного пространства для буферного кэша на основе нескольких параметров, таких как объем физической памяти в системе, настроенное число максимальных потоков сервера и различные параметры запуска. SQL Server резервирует вычисляемый объем виртуального адресного пространства процесса (называемого целевой памятью) для буферного кэша, но занимает (фиксирует) только необходимый объем физической памяти для текущей нагрузки. Можно запросить столбцы committed_target_kb и committed_kb в представлении каталога sys.dm_os_sys_info, чтобы получить число зарезервированных страниц в качестве указателя памяти и число зафиксированных страниц в буферном кэше соответственно.

Интервал между загрузкой SQL Server и получением буферным кэшем целевой памяти называется линейным нарастанием. В течение этого времени читаемые запросы заполняют буфер по мере заполнения. Например, один запрос на чтение страницы с 8 КБ заполняет одну буферную страницу. Это означает, что линейное нарастание зависит от числа и типа запросов клиента. Линейное нарастание ускоряется благодаря преобразованию запросов чтения одной страницы в запросы, одновременно работающие с восемью страницами (что дает один экстент). Это позволяет линейному нарастанию завершить операцию намного быстрее, особенно на машинах с большим объемом памяти. Дополнительные сведения о страницах и экстентах см . в руководстве по архитектуре pages и экстентов.

Так как диспетчер буферов использует большой объем памяти в процессе SQL Server, он взаимодействует с диспетчером памяти, чтобы разрешить другим компонентам использовать свои буферы. Диспетчер буферов взаимодействует прежде всего со следующими компонентами.

диспетчер ресурсов управляет полным использованием памяти и использованием адресного пространства на 32-разрядных платформах:
Диспетчер баз данных и операционная система SQL Server (SQLOS) для низкоуровневых операций ввода-вывода файлов.
диспетчер журнала для упреждающего протоколирования.

Поддерживаемые функции

Диспетчер буферов поддерживает следующие возможности:

Он учитывает неоднородный доступ к памяти (NUMA). Страницы буферного кэша распределены между узлами оборудования NUMA, которые позволяют потоку обращаться к странице буфера, расположенной на локальном узле NUMA, а не во внешней памяти.
Диспетчер буферов поддерживает технологию памяти с горячей заменой, которая позволяет пользователям добавлять физическую память, не перезапуская сервер.
Диспетчер буферов поддерживает большие страницы на 64-разрядных платформах. Размер страницы зависит от версии Windows.

Заметка

До SQL Server 2012 (11.x) включение больших страниц в SQL Server требует флаг трассировки 834.
Диспетчер буферов предоставляет дополнительную диагностику, которая предоставляется с помощью динамических административных представлений. Эти представления можно использовать для мониторинга различных ресурсов операционной системы, относящихся к SQL Server. Например, можно использовать представление sys.dm_os_buffer_descriptors, чтобы отслеживать страницы в буферном кэше.

Операции дискового ввода-вывода

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

Дисковые операции ввода-вывода, выполняемые диспетчером буферов, имеют следующие характеристики.

Все операции ввода-вывода выполняются асинхронно, что позволяет вызывающему потоку продолжать обработку во время выполнения операции ввода-вывода в фоновом режиме.
Все операции ввода-вывода происходят в вызывающих потоках, если не используется параметр affinity I/O. Параметр affinity I/O mask привязывает операцию дискового ввода-вывода SQL Server к определенному подмножеству ЦП. В средах высокоскоростной обработки транзакций в реальном времени (OLTP) SQL Server это расширение может улучшать производительность потоков SQL Server, выдающих вводы-выводы.
Операции ввода-вывода нескольких страниц выполняются с операциями ввода-вывода с разбросом, что позволяет передавать данные из прерывающихся областей памяти. Это означает, что SQL Server может быстро заполнить или записать на диск буферный кэш, предотвращая множество физических запросов операций ввода-вывода.

Длительные запросы операций ввода-вывода

Диспетчер буферов сообщает о любых запросах операций ввода-вывода, которые не были выполнены в течение 15 секунд. Это помогает системному администратору различать ошибки SQL Server и ошибки подсистемы ввода-вывода. Появляется сообщение об ошибке 833, и в журнал ошибок SQL Server записывается следующее:

SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [##] in database [##] (#). The OS file handle is 0x00000. The offset of the latest long I/O is: 0x00000.

Длинный ввод-вывод может быть либо чтением, либо записью; В настоящее время он не указан в сообщении. Сообщение о длительной операции ввода-вывода является предупреждением, а не ошибкой. Они не указывают на проблемы с SQL Server, но с базовой системой ввода-вывода. Сообщения помогают системному администратору быстрее находить причину длительного времени отклика SQL Server и распознавать ошибки, не связанные с работой SQL Server. Таким образом, они не требуют никаких действий, но системный администратор должен исследовать, почему запрос ввода-вывода занимает так много времени, и является ли время оправданным.

Причины длинных запросов ввода-вывода

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

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

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

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

Изолированный длинный интерфейс ввода-вывода, не связанный с предыдущими условиями, может быть вызван проблемой оборудования или драйвера. Журнал системных событий может содержать связанные события, которые помогают выявить ошибку.

Обнаружение нехватки памяти

Нехватка памяти — это состояние, возникающие в результате недостаточного объема памяти. Оно может иметь следующие последствия:

дополнительные операции ввода-вывода (например, очень активный фоновый поток модуля отложенной записи);
более высокий коэффициент перекомпиляции;
более длительное выполнение запросов (если имеются задержки временно предоставляемого буфера памяти);
дополнительные циклы ЦП.

Это состояние может возникать по внешним или внутренним причинам. Возможные внешние причины:

Недостаточный объем доступной физической памяти. Из-за этого система может ограничивать рабочие наборы выполняющихся в настоящее время процессов, что может приводить к общему замедлению работы. SQL Server может уменьшить целевой объект фиксации буферного пула и начать обрезку внутренних кэшей чаще.
Недостаточный общий объем памяти, доступной в системе (включая системный файл подкачки). Из-за этого системе может не удаваться выделить память из-за невозможности выгрузить в файл подкачки выделенную в настоящее время память.

Возможные внутренние причины:

Реагирование на давление внешней памяти, когда ядро СУБД SQL Server устанавливает более низкие ограничения использования памяти.
Настройки памяти были ограничены вручную путем уменьшения значения параметра max server memory.
Изменения в распределении памяти для внутренних компонентов между несколькими кэшами.

Ядро СУБД SQL Server реализует платформу, посвященную обнаружению и обработке давления памяти в рамках управления динамической памятью. Этот механизм включает в себя фоновую задачу Монитор ресурсов. Задача "Монитор ресурсов" отслеживает состояние внешних и внутренних индикаторов памяти. Как только состояние одного из этих индикаторов меняется, он определяет соответствующее уведомление и рассылает его. Уведомления представляют собой внутренние сообщения от каждого из компонентов ядра и хранятся в кольцевых буферах.

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

Кольцевой буфер монитора ресурсов, который отслеживает действия монитора ресурсов, например подачу сигналов о нехватке памяти. Этот кольцевой буфер содержит сведения о состоянии в зависимости от текущего RESOURCE_MEMPHYSICAL_HIGHсостояния , RESOURCE_MEMPHYSICAL_LOWRESOURCE_MEMPHYSICAL_STEADYили RESOURCE_MEMVIRTUAL_LOW.
Кольцевой буфер брокера памяти, который содержит записи уведомлений памяти для каждого пула ресурсов Resource Governor. Когда обнаруживается внутренняя нехватка памяти, для компонентов, выделяющих память, включается уведомление о недостаточном объеме памяти. Оно активирует действия, призванные сбалансировать распределение памяти между кэшами.

Брокеры памяти отслеживают потребление памяти каждым компонентом и на основе собранной информации вычисляют оптимальный объем памяти для каждого из компонентов. Для каждого пула ресурсов Resource Governor имеется набор брокеров. Затем эти сведения рассылаются каждому компоненту, которые уменьшают или увеличивают потребление памяти по мере необходимости.

Дополнительные сведения о брокерах памяти см. в описании представления sys.dm_os_memory_brokers.

Определение ошибки

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

SQL Server повторит любое чтение, которое завершается сбоем с контрольной суммой, разорванной страницей или другой ошибкой ввода-вывода четыре раза. Если считывание закончится успешно в любой из попыток, то в журнал ошибок будет записано сообщение и команда, вызвавшая считывание, продолжится. Если все повторные попытки закончатся ошибкой, команда закончит работу с сообщением об ошибке 824.

Вид используемой защиты страницы является атрибутом базы данных, содержащей страницу. Защита контрольной суммы — это защита по умолчанию для баз данных, созданных в SQL Server 2005 (9.x) и более поздних версий. Механизм защиты страницы указывается во время создания базы данных и может быть изменен с помощью ALTER DATABASE SET. Вы можете определить текущий параметр защиты страниц, запросив page_verify_option столбец в представлении каталога sys.database или IsTornPageDetectionEnabled свойство функции DATABASEPROPERTYEX .

Заметка

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

Защита страницы от разрыва

Защита от разрыва страниц, представленная в SQL Server 2000 (8.x), в основном является способом обнаружения повреждения страниц из-за сбоев питания. Например, неожиданный сбой питания может оставить только часть страницы, записанной на диск. Если используется защита от разрывов страниц, для каждого 512-байтового сектора из 8-килобайтовой (КБ) страницы базы данных в заголовке страницы устанавливается особый двухбитный шаблон подписи при записи страницы на диск.

При чтении страницы с диска биты разрыва, хранимые в заголовке страницы, сравниваются с действительными сведениями о секторах страницы. Шаблон подписи чередуется между двоичным 01 файлом и 10 каждой записью, поэтому всегда можно определить, когда только часть секторов сделала его на диск: если бит находится в неправильном состоянии при последующем чтении страницы, страница была написана неправильно и обнаружена оторванная страница. Обнаружение разорванной страницы использует минимальные ресурсы; Однако он не обнаруживает все ошибки, вызванные сбоями оборудования диска. Сведения о настройке обнаружения разорванной страницы см. в разделе ALTER DATABASE SET Options (Transact-SQL).

Защита контрольной суммы

Защита контрольной суммы, представленная в SQL Server 2005 (9.x), обеспечивает более надежную проверку целостности данных. Контрольная сумма рассчитывается для данных каждой записанной страницы и сохраняется в колонтитуле. Всякий раз, когда страница с сохраненной контрольной суммой читается с диска, компонент Database Engine повторно вычисляет контрольную сумму для данных страницы и вызывает ошибку 824, если новая контрольная сумма отличается от сохраненной. Защита контрольной суммой может перехватить больше ошибок, чем защита от разрыва страницы, потому что она учитывает каждый байт страницы, однако она более ресурсоемкая.

Когда защита контрольной суммой активирована, ошибки, вызванные сбоями питания и поврежденным оборудованием или встроенным ПО, могут быть обнаружены во время чтения страницы с диска диспетчером буферов. Сведения о настройке контрольной суммы см. в разделе ALTER DATABASE SET Options (Transact-SQL).

Внимание

При обновлении пользовательской или системной базы данных до SQL Server 2005 (9.x) или более поздней версии PAGE_VERIFY значение (NONEилиTORN_PAGE_DETECTION) сохраняется. Настоятельно рекомендуется использовать CHECKSUM. TORN_PAGE_DETECTION может использовать меньше ресурсов, но предоставляет минимальное подмножество CHECKSUM защиты.

Общие сведения о доступе к памяти без единого доступа к памяти

SQL Server не является универсальным доступом к памяти (NUMA) и хорошо работает на оборудовании NUMA без специальной настройки. С ростом тактовой частоты и количества процессоров становится труднее сократить время задержки памяти, необходимой для использования дополнительной производительности системы. Для устранения этого недостатка поставщики оборудования применяют большие кэши третьего уровня, но это является всего лишь полумерой. Архитектура NUMA обеспечивает масштабируемое решение для этой проблемы.

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

Динамическое секционирование объектов памяти

Кучи-распределители, называемые объектами памяти в SQL Server, позволяют ядро СУБД выделять память из кучи. Их можно отслеживать с помощью динамического административного представления sys.dm_os_memory_objects.

CMemThread — это потокобезопасный тип объекта памяти, позволяющий выделять память параллельно из нескольких потоков. Для правильного отслеживания объекты CMemThread пользуются конструкциями синхронизации (мьютекс), чтобы обеспечить обновление критически важных элементов информации одновременно только в одном потоке.

Заметка

Тип объекта CMemThread используется во всей базе кода ядра СУБД для многих разных выделений и может быть секционирован глобально, по узлу или ЦП.

Однако использование мьютексов может привести к состязанию, если из одного объекта памяти выделяется много потоков в режиме высокой конкуренции. Поэтому SQL Server имеет концепцию секционированных объектов памяти (PMO), и каждая секция представлена одним объектом CMemThread. Секционирование объекта памяти статически определяется и не может быть изменено после создания. Так как шаблоны выделения памяти могут сильно различаться в зависимости от таких факторов, как использование оборудования и памяти, невозможно заранее подготовить идеальный шаблон секционирования.

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

Заметка

Перед SQL Server 2016 (13.x) флаг трассировки 8048 можно использовать для принудительного создания PMO на основе ЦП. Начиная с SQL Server 2014 (12.x) с пакетом обновления 2 (SP2) и SQL Server 2016 (13.x), это поведение является динамическим и контролируемым подсистемой.

Начиная с SQL Server 2014 (12.x) с пакетом обновления 2 (SP2) и SQL Server 2016 (13.x), ядро СУБД может динамически обнаруживать споры в определенном объекте CMemThread и продвигать объект на один узел или реализацию на основе ЦП. После повышения pmO остается повышенным до перезапуска процесса SQL Server. Состязание CMemThread может быть обнаружено наличием высоких ожиданий CMEMTHREAD в динамическом административном представлении sys.dm_os_wait_stats и путем наблюденияза столбцами contention_factorsys.dm_os_memory_objects DMV , partition_typeи . exclusive_allocations_countwaiting_tasks_count

Руководство по архитектуре управления памятью

Диспетчер виртуальной памяти Windows

Архитектура памяти SQL Server

Обычная (виртуальная) память

Адрес памяти расширений Windows (AWE)

Изменения в управлении памятью, начиная с SQL Server 2012 (11.x)

Изменения memory_to_reserve начиная с SQL Server 2012 (11.x)

Динамическое управление памятью

Размеры стека

Параметры настройки min server memory и max server memory

Требования к объему памяти для хранения объектов SQL Server

Влияние параметра min memory per query

Рекомендации, касающиеся временно предоставляемого буфера памяти

Управление буферами

Принцип работы управления буфером

Поддерживаемые функции

Операции дискового ввода-вывода

Длительные запросы операций ввода-вывода

Причины длинных запросов ввода-вывода

Обнаружение нехватки памяти

Определение ошибки

Защита страницы от разрыва

Защита контрольной суммы

Общие сведения о доступе к памяти без единого доступа к памяти

Динамическое секционирование объектов памяти

Далее

Обратная связь

Обратная связь

Дополнительные ресурсы