SQL, как ограничить память sql

Обновлено: 21.11.2024

По умолчанию максимальный объем памяти SQL Server составляет 2 147 483 647 — чертовски больше, чем у вас есть на самом деле. Trivia time — это максимальное число для 32-битного целого числа со знаком. SQL Server будет использовать все больше и больше памяти, пока в системе не останется ничего.

Если в операционной системе нет доступной памяти, она начнет использовать файл подкачки вместо ОЗУ. Использование файла подкачки вместо памяти приведет к снижению производительности системы — операции, которые должны быть быстрыми и выполняться в памяти, будут постоянно читать и записывать на диск. Вы можете узнать больше о нехватке памяти в этой записи блога Microsoft.

В нашем Руководстве системного администратора по памяти Microsoft SQL Server мы объясняем, почему SQL Server действительно требуется как можно больше памяти и для чего он ее использует. Однако это не означает, что мы должны оставить максимальный объем памяти SQL Server неограниченным. Если мы будем голодать ОС по памяти, то сама Windows может не получить нужной ей памяти.

Мы сравниваем параметр «максимальная память сервера» в sys.configurations с фактическим объемом памяти, которым располагает сервер (как показано в sys.dm_os_sys_memory).

Мы также проверяем sys.dm_os_nodes, чтобы узнать, сообщают ли какие-либо узлы NUMA о том, что памяти опасно мало и что они больше не могут создавать потоки.

Только не делайте хуже

У многих людей возникает соблазн установить для параметра Min Memory такое же значение, как и для параметра Max Memory, чтобы гарантировать, что в SQL Server всегда будет достаточно памяти.

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

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

Прежде чем исправлять — разберитесь с единицами измерения

На заметку: максимальный объем памяти в SQL Server задается в мегабайтах, а не в гигабайтах. Поэтому, если вы намерены указать SQL Server использовать 20 ГБ в качестве максимального параметра памяти, вы должны установить его равным 20480 (эквивалент в мегабайтах). Неправильное понимание этого может привести к тому, что SQL Server не выделит достаточно памяти для правильного запуска.

Хорошие новости: стандартная версия может использовать больше памяти, чем вы думаете

Начиная с SQL Server 2012 ограничение памяти в 64 ГБ для Standard Edition применяется только к кэшу данных, а другие части SQL Server могут использовать память выше этого значения. В SQL Server 2014 предел кэша данных Standard Edition достигает 128 ГБ. Подробнее об этом читайте здесь.

Чтобы решить проблему

1) Решите, какой максимальный объем памяти сервера (МБ) установить. Наше простое «начальное» эмпирическое правило заключается в том, чтобы оставить свободными 4 ГБ или 10 % от общего объема памяти, в зависимости от того, что БОЛЬШЕ в вашем экземпляре для начала, и отрегулируйте это по мере необходимости.

2) Выполните изменение. У нас есть подробные инструкции по выполнению вашего изменения ниже. Это не требует перезапуска вашего экземпляра SQL Server, но вызовет повторную компиляцию планов выполнения.

Как установить максимальный объем памяти сервера (МБ) в SQL Server

Вариант 1. Используйте ГРАФИЧЕСКИЙ ИНТЕРФЕЙС

Щелкните правой кнопкой мыши!

Как только вы доберетесь до свойств…

Это 12 ГБ. Используйте этот номер, только если вы также используете ноутбук.

Помните, что 1 ГБ – это 1 024 МБ, поэтому посчитайте соответственно!

Вариант 2. Установите максимальный объем памяти сервера с помощью T-SQL

Если вы знакомы с процедурой sp_configure и настроили ее для доступа к дополнительным параметрам, вы можете установить ее непосредственно в T-SQL. Просто замените 12345 ниже на количество мегабайт, которое вы хотите использовать:

Многие люди, работающие с SQL Server, часто задают вопрос: "Почему SQL Server использует почти всю память на сервере, даже если в базах данных практически нет активности?" Это распространенный и логичный вопрос, если вы не знакомы с управлением памятью SQL Server и не изучали этот вопрос. Этот совет предназначен для объяснения конфигураций памяти SQL Server по умолчанию и того, как настроить SQL Server для использования фиксированного объема ОЗУ.

Решение

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

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

Если ваш сервер является выделенным сервером базы данных, проблем с выделением памяти не возникает, однако иногда на сервере базы данных выполняется много приложений. Кроме того, некоторые приложения используют память, доступную во время их запуска, и могут быть не в состоянии запросить дополнительную память у Windows, если это необходимо. В этом случае, чтобы другие приложения могли работать правильно, мы можем ограничить доступ SQL Server к памяти, установив параметр «max server memory». По умолчанию установлено значение 2 147 483 647 МБ, что позволяет SQL Server использовать примерно всю память сервера. Мы можем изменить этот параметр на более низкое значение в соответствии с нашими требованиями. Это можно сделать с помощью SQL Server Management Studio или сценария T-SQL.

Установка «максимальной памяти сервера» с помощью SQL Server Management Studio

На очень высоком уровне давайте проследим за использованием памяти на нашем компьютере, где работает SQL Server, затем установим «максимальный объем памяти сервера» в SQL Server Management Studio (SSMS) и просмотрим изменения в использовании памяти. В нашем примере тестовый сервер имеет 1 ГБ памяти (RAM) и SQL Server 2014 установлен, но не в рабочем состоянии. Также на нашем сервере нет других ресурсов, потребляющих память. Запустив диспетчер задач, мы можем отслеживать использование памяти на вкладке «Производительность», чтобы получить представление об использовании памяти перед запуском SQL Server:

Теперь мы снова запустим SQL Server и будем отслеживать использование памяти:

Мы видим, что объем используемой памяти увеличился:

А самым большим потребителем памяти является SQL Server:

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

На нашем сервере это 117 МБ:

Теперь давайте выполним некоторые действия в нашем экземпляре. У нас есть база данных TestDB на нашем экземпляре и в ней TableA, которая содержит 300 000 строк. Выберем все строки из этой таблицы:

По завершении запроса мы снова проверим использование памяти в SQL Server:

В данном случае мы видим, что SQL Server использует 432 МБ, однако на нашем экземпляре нет активных запросов:

И 93% памяти нашего сервера используется (в основном SQL Server):

Установите «максимальный объем памяти сервера» в SQL Server Management Studio

Теперь мы установим параметр "max server memory", чтобы ограничить использование памяти SQL Server. Мы можем сделать это, щелкнув правой кнопкой мыши наш экземпляр и выбрав «Свойства»:

После этого выберите страницу "Память":

Выше мы видим, что «Максимальная память сервера» установлена на 2147483647 МБ. Это значение по умолчанию. Мы изменим его на 200 МБ и нажмем «ОК»:

Выполнив этот запрос еще раз, мы увидим, что память, используемая SQL Server, уменьшилась до 244 МБ:

Возникает вопрос: почему 244 МБ, если мы установили «максимальную память сервера» для SQL Server в 200 МБ? Причина в том, что наш запрос возвращает используемую в данный момент память, однако максимальная память сервера управляет памятью, используемой буферным пулом, скомпилированной памятью, всем кешем и т. д., но не контролирует память для поставщиков связанных серверов, отличных от SQL Server, память, выделенная DLL-библиотека, отличная от SQL Server, память для стеков потоков, кучи памяти и т. д., поэтому результат нашего запроса может быть немного больше, чем значение «max server memory».

Установите «максимальную память сервера» в SQL Server с помощью T-SQL

Мы также можем установить "максимальную память сервера" с помощью скрипта T-SQL:

Этот скрипт устанавливает для "максимальной памяти сервера" значение по умолчанию - 2147483647 МБ:

Мы можем проверить в SSMS, что "максимальная память сервера" изменилась:

Заключение

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

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

Опция	По умолчанию	Минимально допустимый
минимальная память сервера	0	0
максимальная память сервера	2 147 483 647 мегабайт (МБ)< /td>	128 МБ

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

Установка слишком большого значения максимального значения памяти сервера может привести к тому, что один экземпляр SQL Server будет конкурировать за память с другими экземплярами SQL Server, размещенными на том же хосте. Однако слишком маленькое значение этого параметра может привести к существенной нагрузке на память и проблемам с производительностью. Установка максимального значения памяти сервера на минимальное значение может даже помешать запуску SQL Server. Если вы не можете запустить SQL Server после изменения этого параметра, запустите его с помощью параметра запуска -f и сбросьте максимальное значение памяти сервера до его предыдущего значения. Дополнительные сведения см. в разделе Параметры запуска службы Database Engine.

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

Установить параметры вручную

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

Параметры минимальной памяти сервера и максимальной памяти сервера являются дополнительными параметрами. Если вы используете системную хранимую процедуру sp_configure для изменения этих параметров, вы можете изменить их, только если для параметра show advanced options установлено значение 1. Эти параметры вступают в силу немедленно, без перезапуска сервера.

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

Не гарантируется, что SQL Server выделит объем памяти, указанный в минимальной памяти сервера. Если нагрузка на сервер никогда не требует выделения объема памяти, указанного в минимальной памяти сервера, SQL Server будет работать с меньшим объемом памяти.

Используйте max_server_memory, чтобы гарантировать, что ОС не будет испытывать вредного нехватки памяти. Чтобы установить максимальную конфигурацию памяти сервера, отслеживайте общее потребление процесса SQL Server, чтобы определить требования к памяти. Для первоначальной настройки или когда не было возможности собирать сведения об использовании памяти процессом SQL Server с течением времени, используйте следующий обобщенный подход к настройке max_server_memory для одного экземпляра:

Из общего объема памяти ОС вычтите эквивалент потенциального распределения памяти потоков SQL Server вне максимального контроля памяти сервера, который состоит из размера стека 1 * расчетного максимального количества рабочих потоков 2 .
Затем вычтите 25 % для других выделений памяти, выходящих за пределы максимального контроля памяти сервера, например для резервных буферов, расширенных библиотек DLL хранимых процедур, объектов, созданных с помощью процедур автоматизации (вызовы sp_OA), и выделений от поставщиков связанных серверов. Это общее приближение, пробег может варьироваться.
То, что осталось, должно быть параметром max_server_memory для настройки одного экземпляра.

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

2 См. страницу документации о том, как настроить параметр конфигурации сервера max worker threads, для получения информации о расчетных рабочих потоках по умолчанию для заданного количества аффинитизированных ЦП на текущем хосте.

Использовать SQL Server Management Studio

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

Процедура настройки фиксированного объема памяти (не рекомендуется)

Чтобы установить фиксированный объем памяти:

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

Нажмите узел Память.

В разделе "Параметры памяти сервера" введите требуемый объем минимальной памяти сервера и максимальной памяти сервера.

Используйте настройки по умолчанию, чтобы позволить SQL Server динамически изменять требования к памяти в зависимости от доступных системных ресурсов. Рекомендуется установить максимальную память сервера, как указано выше.

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

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

Эта политика Windows определяет, какие учетные записи могут получить доступ к API для хранения данных в физической памяти, не позволяя системе подкачивать данные в виртуальную память на диске. Блокировка страниц в памяти может привести к тому, что сервер не сможет реагировать, когда происходит подкачка памяти на диск. Параметр «Блокировать страницы в памяти» включен в экземплярах SQL Server Standard Edition и выше, если учетной записи с правами на запуск sqlservr.exe предоставлено право пользователя Windows Блокировать страницы в памяти (LPIM). .

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

Использование LPIM не влияет на управление динамической памятью SQL Server, позволяя увеличивать или уменьшать ее по запросу других операторов памяти. При использовании права пользователя Блокировать страницы в памяти рекомендуется установить верхний предел максимальной памяти сервера, как описано выше.

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

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

Чтобы включить блокировку страниц в памяти

Чтобы включить блокировку страниц в памяти:

В меню "Пуск" нажмите "Выполнить". В поле "Открыть" введите gpedit.msc.

Откроется диалоговое окно групповой политики.

В консоли групповой политики разверните Конфигурация компьютера, а затем разверните Параметры Windows.

Разверните Параметры безопасности, а затем разверните Локальные политики.

Выберите папку «Назначение прав пользователя».

Политики будут отображаться в области сведений.

На панели дважды щелкните Блокировать страницы в памяти.

В диалоговом окне «Параметры локальной политики безопасности» добавьте учетную запись с правами запуска sqlservr.exe (учетная запись запуска SQL Server).

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

При запуске нескольких экземпляров компонента Database Engine существует три подхода к управлению памятью:

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

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

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

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

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

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

Примеры

Пример A. Установите для параметра максимального объема памяти сервера значение 4 ГБ.

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

Это выведет оператор, похожий на:

Параметр конфигурации "максимальная память сервера (МБ)" изменен с 2147483647 на 4096. Для установки выполните оператор RECONFIGURE.

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

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

Пример C. Определение значения параметра "максимальная память сервера (МБ)"

Следующий запрос возвращает информацию о текущем сконфигурированном значении и значении, используемом SQL Server. Этот запрос вернет результаты независимо от того, имеет ли значение «показать дополнительные параметры» значение true.

В этой статье рассказывается о настройке минимального и максимального объема памяти для экземпляров базы данных SQL Server.

Введение

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

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

Не допускать, чтобы пул буферов стал настолько большим, что всей системе не хватит памяти
Сведите к минимуму физический ввод-вывод для файлов базы данных, максимально увеличив размер буферного пула

По умолчанию SQL Server динамически получает память в зависимости от ОЗУ сервера; однако это гарантирует, что не возникнет нехватки памяти для оставшихся процессов.

Ядро базы данных SQL Server имеет фоновый процесс, называемый монитором ресурсов. Его задача — следить за показателями внутренней и внешней памяти.

Эти уведомления хранятся в кольцевых буферах. Группа ресурсов монитора ресурсов использует сигналы RESOURCE_MEMPHYSICAL_HIGH, RESOURCE_MEMPHYSICAL_LOW, RESOURCE_MEMPHYSICAL_STEADY или RESOURCE_MEMVIRTUAL_LOW.

RESOURCE_MEM_STEADY: проблем с памятью не наблюдалось
RESOURCE_MEMPHYSICAL_HIGH: SQL Server может получить память, так как ОС имеет достаточно свободной доступной памяти
RESOURCE_MEMPHYSICAL_LOW: SQL Server должен вернуть память, так как в ОС не хватает памяти
RESOURCE_MEMVIRTUAL_LOW: диапазон виртуальных адресов для процесса SQL Server исчерпан

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

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

Минимальная память: значение по умолчанию – 0 МБ.
Максимальная память: значение по умолчанию 2 147 483 647 МБ.

Минимальный объем памяти сервера для экземпляра базы данных SQL Server

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

Примечание.
1. Это нормально, когда использование памяти SQL Server ниже настроенного минимального объема памяти сервера

Максимальный объем памяти сервера для экземпляров базы данных SQL Server

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

Читайте также: