Параметры конфигурации памяти сервера

Область применения:SQL Server

Использование памяти для ядра СУБД SQL Server ограничивается парой параметров конфигурации и min server memory (MB)max server memory (MB). С течением времени и при обычных обстоятельствах SQL Server попытается выполнить попытку утверждения памяти до предела, заданного .max server memory (MB)

Примечание.

Индексы Columnstore: обзор и In-Memory обзоре OLTP и сценариях использования имеют собственные клерки памяти, что упрощает мониторинг использования буферного пула. Дополнительные сведения см. в статье sys.dm_os_memory_clerks.

В более ранних версиях SQL Server использование памяти практически не было ограничено, указывая для SQL Server, что вся системная память доступна для использования. Рекомендуется настроить верхний предел использования памяти SQL Server во всех версиях SQL Server.max server memory (MB)

• Начиная с SQL Server 2019 (15.x), программа установки SQL на серверах Windows предоставляет рекомендацию для max server memory (MB) автономного экземпляра SQL Server на основе процента доступной системной памяти во время установки.
• В любое время можно перенастроить границы памяти (в мегабайтах) для процесса SQL Server, используемого экземпляром SQL Server с помощью min server memory (MB) параметров конфигурации и max server memory (MB) настройки.

Примечание.

Это руководство относится к экземпляру SQL Server в Windows. Сведения о конфигурации памяти в Linux см. в разделах Рекомендации по повышению производительности и конфигурации для SQL Server в Linux и Параметр memory.memorylimitmb.

Рекомендации

Параметры по умолчанию и минимальное допустимое значение для этих параметров

Вариант	По умолчанию.	Допустимый минимум	Рекомендуемая конфигурация
min server memory (MB)	0	0	0
max server memory (MB)	2 147 483 647 мегабайт (МБ)	128 МБ	75 % доступной системной памяти, не потребляемой другими процессами, включая другие экземпляры. Более подробные рекомендации см. в разделе о максимальном объеме памяти сервера.

В этих границах SQL Server может динамически изменять требования к памяти в зависимости от доступных системных ресурсов. Дополнительные сведения см. в разделе Управление динамической памятью.

• Слишком max server memory (MB) большое значение может привести к тому, что один экземпляр SQL Server будет конкурировать за память с другими экземплярами SQL Server, размещенными на одном узле.
• Однако установка max server memory (MB) слишком низкой является потерянной возможностью производительности и может привести к нехватке памяти и проблемам с производительностью в экземпляре SQL Server.
• Установка max server memory (MB) минимального значения может даже предотвратить запуск SQL Server. Если после изменения этого параметра не удается запустить SQL Server, запустите его с помощью -f параметра запуска и сбросите max server memory (MB) его до предыдущего значения. Дополнительные сведения см. в разделе ядро СУБД Параметры запуска службы.
• Не рекомендуется задавать max server memory (MB) и min server memory (MB) быть одинаковым значением или почти одинаковыми значениями.

Примечание.

Параметр max server memory (максимальный объем памяти сервера) ограничивает только размер буферного пула SQL Server. Параметр максимальной памяти сервера не ограничивает оставшуюся неограничленную область памяти, которую SQL Server оставляет для выделения других компонентов, таких как расширенные хранимые процедуры, COM-объекты, не общие библиотеки DLL и EXEs.

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

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

Минимальный объем памяти сервера

Используйте min server memory (MB) для обеспечения минимального объема памяти, доступного диспетчеру памяти SQL Server.

• SQL Server не сразу выделяет объем памяти, указанной при min server memory (MB) запуске. Однако после того, как использование памяти достигло этого значения из-за загрузки клиента, SQL Server не может освободить память, если не будет сокращено min server memory (MB) значение. Например, если несколько экземпляров SQL Server устанавливаются одновременно на одном сервере, рекомендуется задать min server memory (MB) параметр для резервирования памяти для экземпляра.

• min server memory (MB) Задание значения важно в виртуализированной среде, чтобы гарантировать, что давление на память от базового узла не пытается освободить память из буферного пула на гостевой виртуальной машине ( виртуальной машине) за пределами допустимой производительности. В идеале экземпляры SQL Server на виртуальной машине не должны конкурировать с процессами упреждающего распределения памяти на виртуальном узле.

• SQL Server не гарантирует выделение объема памяти, указанного в min server memory (MB). Если нагрузка на сервер никогда не требует выделения объема памяти, указанного в min server memory (MB), SQL Server будет использовать меньше памяти.

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

Используйте max server memory (MB) для обеспечения того, чтобы операционная система и другие приложения не испытывали негативного давления на память из SQL Server.

• Прежде чем задать max server memory (MB) конфигурацию, отслеживайте общее потребление памяти сервера, на котором размещен экземпляр SQL Server во время обычной работы, чтобы определить доступность и требования к памяти. Для начальной конфигурации или когда с течением времени не было возможности собирать использование памяти процесса SQL Server, используйте следующий обобщенный подход к настройке max server memory (MB) для одного экземпляра:
  • Из общей памяти ОС вычитает эквивалент потенциального выделения памяти потока SQL Server за пределами max server memory (MB) управления, что является размером стека¹, умноженным на вычисляемый максимальный рабочий поток ².
  • Затем вычитайте 25% для других выделений памяти вне max server memory (MB) контроля, таких как буферы резервного копирования, библиотеки DLL расширенных хранимых процедур, объекты, созданные с помощью процедур автоматизации (sp_OA вызовов) и выделения из связанных поставщиков серверов. Это универсальное приближение, и ваш пробег может отличаться.
  • Что остается, должно быть параметром max server memory (MB) для установки одного экземпляра.

¹ Сведения о размерах стеков потока для различных архитектур см. в разделе Руководство по архитектуре управления памятью.

² Дополнительные сведения о вычисляемых рабочих потоках по умолчанию для заданного количества сопоставленных ЦП в текущем узле см. в разделе "Конфигурация сервера: максимальное количество рабочих потоков".

Настройка параметров вручную

Параметры min server memory (MB) сервера и max server memory (MB) могут быть заданы для диапазона значений памяти. Этот метод полезен для системных администраторов или администраторов баз данных, когда требуется настроить экземпляр SQL Server так, чтобы его параметры не противоречили требованиям к памяти других приложений или других экземпляров SQL Server, запущенных на этом узле.

Использование Transact-SQL

Параметры min server memory (MB) и max server memory (MB) параметры — это дополнительные параметры. При использовании системной хранимой процедуры sp_configure для изменения этих настроек изменить их можно, только если параметр show advanced options установлен в значение 1. Эти параметры вступают в силу сразу же без перезагрузки сервера. Дополнительные сведения: sp_configure.

В следующем примере для параметра задано max server memory (MB) значение 12 288 МБ или 12 ГБ. Хотя sp_configure указывает имя параметра как max server memory (MB), можно опустить (MB).

EXECUTE sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXECUTE sp_configure 'max server memory', 12288;
GO

RECONFIGURE;
GO

Следующий запрос возвращает сведения о настроенных значениях и значении, которое сейчас используется. Этот запрос возвращает результаты независимо от того, включен ли sp_configure параметр "Показать расширенные параметры".

SELECT [name],
       [value],
       [value_in_use]
FROM sys.configurations
WHERE [name] = 'max server memory (MB)'
      OR [name] = 'min server memory (MB)';

Использование SQL Server Management Studio

Используйте min server memory (MB) и max server memory (MB) перенастроите объем памяти (в мегабайтах), управляемый диспетчером памяти SQL Server для экземпляра SQL Server.

1. В обозревателе объектов щелкните правой кнопкой мыши сервер и выберите пункт Свойства.

2. Выберите страницу Память окна Свойства сервера. Отображаются текущие значения минимального объема памяти сервера и максимального объема памяти сервера.

3. В разделе Параметры памяти сервера введите требуемые значения для параметров минимального объема памяти сервера и максимального объема памяти сервера. Рекомендации см. в разделе min server memory (MB) и max server memory (MB) в этой статье.

На следующем снимке экрана показаны все три шага:

Блокировка страниц в памяти (LPIM)

Приложения на основе Windows могут использовать API Address Windowing Extensions (AWE) ОС Windows, чтобы выделять физическую память и сопоставлять ее с адресным пространством процесса. Эта политика LPIM Windows определяет, какие учетные записи могут получать доступ к API для сохранения данных в физической памяти, чтобы система не отправляла страницы данных в виртуальную память на диске. Память, выделенная с помощью AWE, блокируется до тех пор, пока приложение не освободит ее или не завершит работу. Использование API AWE для управления памятью в 64-разрядной версии SQL Server также часто называют заблокированными страницами. Блокировка страниц в памяти может реагировать на сервер при выполнении разбиения памяти на диск. Для параметра Блокировка страниц в памяти указывается значение enabled в экземплярах выпуска SQL Server Standard и выше, если учетной записи с привилегией на выполнение sqlservr.exe предоставлено право пользователя Windows Блокировка страниц в памяти (LPIM).

Чтобы отключить параметр Блокировка страниц в памяти для SQL Server, удалите право пользователя Блокировка страниц в памяти у учетной записи с привилегиями для запуска sqlservr.exe (стартовой учетной записи SQL Server).

Использование LPIM не влияет на динамическое управление памятью в SQL Server, что позволяет расширить или сузить ее по запросу других клерков памяти. При использовании страниц блокировки в памяти справа настоятельно рекомендуется задать верхний предел max server memory (MB). См. дополнительные сведения о параметре max server memory (MB).

LPIM следует использовать при наличии признаков того, что sqlservr процесс выстраивается. В этом случае ошибка 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: ##%.

Использование LPIM с неправильно настроенным max server memory (MB) параметром, который не учитывает других потребителей памяти в системе, может привести к нестабильности в зависимости от объема памяти, требуемого другими процессами, или требований к памяти SQL Server за пределами max server memory (MB)области. См. дополнительные сведения о max server memory. Если права на блокировку страниц в памяти (LPIM) предоставляются (в 32-разрядных или 64-разрядных системах), настоятельно рекомендуется задать max server memory (MB) определенное значение, а не оставить значение по умолчанию 2 147 483 647 мегабайт (МБ).

Примечание.

Начиная с SQL Server 2012 (11.x), для Standard Edition флаг трассировки 845 больше не нужен для использования заблокированных страниц памяти.

Включение параметра Блокировка страниц в памяти

Если вы изучили предыдущие сведения и хотите включить параметр Блокировка страниц в памяти, предоставив это разрешение учетной записи службы для экземпляра SQL Server, см. раздел Включение параметра "Блокировка страниц в памяти" (Windows).

Чтобы определить учетную запись службы для экземпляра SQL Server, обратитесь к диспетчеру конфигурации SQL Server или запросите service_account из sys.dm_server_services. Дополнительные сведения см. в sys.dm_server_services.

Просмотр статуса Блокировка страниц в памяти

Чтобы определить, предоставлено ли учетной записи службы разрешение Блокировка страниц в памяти для экземпляра SQL Server, используйте следующий запрос. Этот запрос поддерживается в SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) и более поздних версий.

SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;

Следующие значения sql_memory_model_desc указывают состояние LPIM:

• CONVENTIONAL. Разрешение "Блокировка страниц в памяти" не предоставлено.
• LOCK_PAGES. Разрешение "Блокировка страниц в памяти" предоставлено.
• LARGE_PAGES. Привилегия блокировки страниц в памяти предоставляется в режиме Enterprise при включенном флаге трассировки 834. Это расширенная конфигурация, которая не рекомендуется для большинства сред. Дополнительные сведения и важные предостережения см. в флаге трассировки 834.

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

• Выходные данные следующего запроса Transact-SQL указывают ненулевое значение для locked_page_allocations_kb:

  SELECT osn.node_id,
         osn.memory_node_id,
         osn.node_state_desc,
         omn.locked_page_allocations_kb
  FROM sys.dm_os_memory_nodes AS omn
       INNER JOIN sys.dm_os_nodes AS osn
           ON (omn.memory_node_id = osn.memory_node_id)
  WHERE osn.node_state_desc <> 'ONLINE DAC';
  

• Текущий журнал ошибок SQL Server сообщает об этом сообщении Using locked pages in the memory manager во время запуска сервера.

• В разделе диспетчера памяти выходных данных DBCC MEMORYSTATUS отображается ненулевое значение элемента AWE Allocated .

Несколько экземпляров SQL Server

При запуске нескольких экземпляров ядро СУБД существуют различные подходы, которые можно использовать для управления памятью:

• Используйте max server memory (MB) в каждом экземпляре для управления использованием памяти, как описано ранее. Установите максимальные значения для каждого экземпляра, учитывая, что их сумма не должна превышать общий объем физической памяти, установленной на компьютере. Рекомендуется выделять каждому экземпляру объем памяти, пропорциональный его ожидаемой рабочей нагрузке или размеру базы данных. Данный подход имеет то преимущество, что свободная память доступна новым процессам или экземплярам сразу же после их запуска. Недостаток в том, что, когда выполняются не все экземпляры, ни один из выполняющихся экземпляров не сможет использовать память, оставшуюся свободной.

• Используйте min server memory (MB) в каждом экземпляре для управления использованием памяти, как описано ранее. Установите минимальные значения для каждого экземпляра так, чтобы их сумма была на 1–2 ГБ меньше общего объема физической памяти, установленной на компьютере. Опять же, можно установить эти минимумы пропорционально ожидаемой нагрузке этого экземпляра. Данный подход имеет то преимущество, что выполняющиеся экземпляры могут использовать оставшуюся свободную память в случае, когда выполняются не все экземпляры. Этот подход также полезен при наличии другого интенсивного в памяти процесса на компьютере, так как это позволит SQL Server по крайней мере получить разумный объем памяти. Недостаток заключается в том, что при запуске нового экземпляра (или любого другого процесса) может потребоваться некоторое время для выпуска памяти запущенных экземпляров, особенно если они должны записывать измененные страницы обратно в базы данных для этого.

• Используйте оба max server memory (MB)min server memory (MB) экземпляра и в каждом экземпляре для управления потреблением памяти, наблюдением и настройкой максимального использования каждого экземпляра и минимальной защиты памяти в пределах широкого диапазона потенциальных уровней использования памяти.

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

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

Примеры

А. Задание для параметра max server memory значения 4 ГБ

В следующем примере для параметра задано max server memory (MB) значение 4096 МБ или 4 ГБ. Хотя sp_configure указывает имя параметра как max server memory (MB), можно опустить (MB).

EXECUTE sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXECUTE sp_configure 'max server memory', 4096;
GO

RECONFIGURE;
GO

В результате вы получите инструкцию, аналогичную Configuration option 'max server memory (MB)' changed from 2147483647 to 4096. Run the RECONFIGURE statement to install. Новое ограничение памяти вступает в силу сразу после выполнения RECONFIGURE. Дополнительные сведения: sp_configure.

В. Определение текущего распределения памяти

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

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;

В. Просмотр значения max server memory (MB)

Следующий запрос возвращает сведения о настроенном сейчас значении и используемом значении. Этот запрос возвращает результаты независимо от того, включен ли sp_configure параметр "Показать расширенные параметры".

SELECT [value], [value_in_use]
FROM sys.configurations
WHERE [name] = 'max server memory (MB)';

Связанный контент

• Руководство по архитектуре управления памятью
• Наблюдение и настройка производительности
• RECONFIGURE (Transact-SQL)
• Параметры конфигурации сервера
• sp_configure (Transact-SQL)
• параметры запуска службы ядро СУБД
• Предельный объем памяти для выпусков Windows и Windows Server