Sql server windows nt 64 жрет память

Обновлено: 04.07.2024

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

Например, представьте, что ваш сервер SQL работает оптимально, только с 8 ГБ памяти, а сервер показывает

95% от общего объема используемой оперативной памяти. Вы можете удвоить ОЗУ на машине, удвоить настройку Max Memory экземпляра SQL, а затем наблюдать, как сервер медленно поднимается до 95%. Это не обязательно проблема. SQL просто кэширует столько временных данных, сколько может с тем, что ему дано.

Ниже приведены наши краткие сведения о том, есть ли на самом деле проблема с памятью или SQL Server просто выполняет то, что предполагается сделать:

  1. Проверьте параметр Max Memory в свойствах экземпляра и сравните его с общей памятью сервера. SQL нужно давать как можно больше, но каждая среда отличается. Есть также много факторов, которые необходимо учитывать (количество экземпляров, приложений, нагрузка, состояние кластера и т. д.). По крайней мере, убедитесь, что для операционной системы осталось несколько ГБ. Кроме того, убедитесь, что все остальное, что для этого нужно, на этой машине.
  2. Если максимальная память установлена на 2147483647, измените ее прямо сейчас. Это значение по умолчанию, с которым устанавливается SQL, и говорит о необходимости использовать столько, сколько ему нужно. Это может вызвать проблемы с производительностью для ОС и других приложений на сервере и замедлить все, если это когда-либо будет узким местом.


  1. Запустите встроенный отчет о потреблении памяти из свойств экземпляра. Полезные детали для немедленного поиска - это высокое значение PLE и низкое значение ожидающих предоставления памяти. Page Life Expectancy - это количество секунд, в течение которых страница будет оставаться в пуле буферов, прежде чем ее можно будет «повторно использовать» на сервере. Общая рекомендация - иметь 300 секунд или больше, но эта рекомендация экспоненциально увеличивается, когда на сервере больше оперативной памяти. Memory Grants Pending - это число процессов, ожидающих предоставления памяти рабочей области. Ноль - это лучшее значение, поскольку оно означает, что все, что работает, может сделать это с достаточным объемом памяти, который ему необходим.



  1. Запустите приведенный ниже запрос, чтобы проверить текущие счетчики памяти. Третий набор результатов покажет временную метку, когда произошло изменение памяти. Следите за любыми «низкими» предупреждениями о памяти и с этого момента определяйте, следует ли дополнительно исследовать нагрузку на память, если SQL использует соответствующее количество.

SELECT @@SERVERNAME AS [Server Name]

,total_physical_memory_kb / 1024 AS [Total Physical Memory (MB)]

,available_physical_memory_kb / 1024 AS [Available Physical Memory (MB)]

,total_page_file_kb / 1024 AS [Total Page File Memory (MB)]

,available_page_file_kb / 1024 AS [Available Page File Memory (MB)]

,system_memory_state_desc AS [Available Physical Memory]

,CURRENT_TIMESTAMP AS [Current Date Time]

SELECT physical_memory_in_use_kb / 1024 AS [Physical Memory In Use (MB)]

,locked_page_allocations_kb / 1024 AS [Locked Page In Memory Allocations (MB)]

,memory_utilization_percentage AS [Memory Utilization Percentage]

,available_commit_limit_kb / 1024 AS [Available Commit Limit (MB)]

,CASE WHEN process_physical_memory_low = 0 THEN 'No Memory Pressure Detected' ELSE 'Memory Low' END AS 'Process Physical Memory'

,CASE WHEN process_virtual_memory_low = 0 THEN 'No Memory Pressure Detected' ELSE 'Memory Low' END AS 'Process Virtual Memory'

SQL Server. Основы анализа производительности памяти

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

  • SQL Server “съел” всю память (или очень много), что начало “угнетать” ОС и приложения работающие на ней.
  • Вы установили ограничения на память потребляемую SQL Server и ему стало не хватать выделенной памяти.
  • Какое-либо приложение потребило слишком много памяти и SQL Server-у стало не хватать выделенной памяти
  • “Перекос” в потреблении памяти каким-либо компонентом SQL Server, что стало причиной “угнетения” других компонентов SQL Server.

Первые три причины называются внешним “нажимом” на память и методика их анализа почти одинакова. Третья же причина называется “внутренним” нажимом на память и методики ее анализа совершенно другие.

Итак начнем рассмотрение первой причины – “SQL Server “съел” всю (или очень много) память, что начало угнетать ОС и приложения работающие на ней.”

В качестве ограничения накладываемого на рассмотрение – в блоге мы будем рассматривать только 64-битовую версию SQL Server и ОС, поскольку 32-битовых остается все меньше и меньше.

Для начала надо установить факт, что ОС действительно не хватает памяти и эту память потребил SQL Server. Выполнить это можно посмотрев счетчик Memory: Available Bytes. Он показывает сколько памяти осталось у ОС для распределения приложениям и внутреннего использования. Возникает справедливый вопрос:”Каким образом приложение может потребить много памяти и угнетать ОС?”. Причин тому может быть несколько:

  • Утечка памяти в приложении или драйвере, особенно утечка невыгружаемого пула.
  • Установка завышенного значения параметра “max server memory” в свойствах SQL Server и права “Lock Pages in Memory” для учетной записи от которой работает SQL Server.

У нас (Microsoft) есть принятая норма на остаток памяти который должен оставаться доступным для распределения ОС. Эта норма составляет 5% от объема установленного RAM, т.е. в свободном распределении у ОС должно остаться не менее 5% от общего объема RAM установленного на сервере. И как бы это число не казалось большим (особенно при больших объемах установленной памяти), лучше этого правила придерживаться.
Что будет предпринимать ОС, когда приложения потребят слишком много оперативной памяти? При достижении порога Memory: Available Bytes в 100…50 MB Windows включит агрессивный сброс (trimming) рабочих наборов процессов, включая системные драйверы, что тут же приведет к резкому снижению производительности всех компонентов ОС. В данной статье мы не будем рассматривать эти вопросы, возможно мы рассмотрим их в будущем.

Каким образом рассчитать правильное значение “max server memory”?

Здесь есть два случая:

  • Некластеризованный SQL Server, либо кластеризованный SQL Server в режиме Актив/Пассив .
  • Кластеризованный SQL Server работающий в режиме Актив/Актив.

Расчет памяти для некластеризованного, либо кластеризованного SQL Server работающего в режиме Актив/Пассив

  1. Остаток для ОС – 5%. В нашем случае это около 25 GB (500*5%).
  2. Память под ядро SQL Server (различные *.exe, *.dll, *ocx и пр. модули), SQL heap, CLR. Обычно это до 500 MB, хотя за счет CLR это может быть и больше.
  3. Память под кэши “Worker thread”, рассчитываемая по формуле (512+(NumCpu-4)*16)*2 MB. В нашем случае это (512+(64-4)*16)* 2MB = 2944 MB (около 2.7 GB).
  4. Итого под “max server memory” остается: 500 – 25 – 0.5 – 2.7 = 471.2 GB. Т.е. размер Буферного пула (при таком значении “max server memory”) может вырасти до 471 GB.
  5. Для версии SQL 2012 и далее “max server memory” включает в себя SQL heap и частично CLR.

Особенно актуален это расчет, если вы используете “Lock Pages In Memory” В этом случае завысив это число или оставив его по умолчанию (что обозначает – любой объем) вы можете поставить ОС в довольно неприятное положения, которое приведет к агрессивному триммированию рабочих наборов и, как следствие, резкому замедлению работы системы.

При расчете необходимо учитывать, что пункты 2, 3 и 4 должны быть удвоены, и при использовании права учетной записи SQL Server “Lock Pages In Memory”, вам необходимо подобрать не только “max server memory”, но и “min server memory”, что бы в случае переката обоих SQL Server на один узел вы не забрали всю память у ОС.

В данном случае на сервере установлено 500 GB оперативной памяти и 5% должно быть около 25 GB. Каким бы большим не казалось это число, но чем больше на сервере процессоров и памяти, тем (как правило) более ресурсоемкие задачи он выполняет и для их решения ему требуются большие ресурсы.

Как видно из рисунка (в данном случае), остаток памяти на сервере составляет около 7 GB, что не соответствует нашим рекомендациям.

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

Давайте сначала выясним сколько памяти потребил SQL Server. Для этого надо знать, использует или нет SQL Server право учетной записи SQL Server “Lock Page In Memory”, Выяснить это можно из свойств учетной записи, а можно косвенно, через счетчики Performance Monitor. Дело в том, что если право учетной записи SQL Server “Lock Page In Memory” не установлено, то вся (или почти вся) используемая память будет частью рабочего набора процесса sqlservr.exe. Если же это право установлено, то при этом (скрыто) используется механизм AWE (Address Windows Extension) и основная память под Буферный пул будет размещена за пределами процесса sqlservr.exe.

Как видно из рисунка ниже, размер рабочего набора процесса составляет всего около 4 GB, что значительно меньше общего объема потребленной памяти.

Посмотрим, сколько всего памяти использует SQL Server. Он использует 500 857 024 (около 480 GB) для распределения на Буферный пул, Процедурный кэш, кэш Worker Thread и для некоторых не значительных потребителей. А отсюда можно сделать вывод, что в данном случае SQL Server использует “Lock Pages in Memory”.

Далее приступим к поиску ответа на вопрос:”Можно ли отобрать часть памяти у SQL Server, не нанося ему вреда?”

Во первых, давайте проверим какое количество запросов обслуживается из Буферного пула (без выполнения физических чтений). Как мы видим из рисунка ниже по тексту около 100% (точнее 99,972%) запросов выполняются из буферного пула (при пороговом значении данного счетчика не менее 92%), что дает нам надежду на наличие избытка памяти у SQL Server.

Следующим счетчиком, который рекомендуется посмотреть является SQL Server: Page Life Expectancy. Он контролирует время жизни страниц в Буферном пуле. Пороговое значение 300 секунд. В данном случае мы видим среднее значение около 221000, что почти в 700 раз больше порогового. Это укрепляет нас в мысли, что ресурсы есть.

Окончательный ответ нам поможет дать счетчик SQL Server: Lazy Writes/sec, отображающий как часто срабатывает процесс Lazy Writer. Мы знаем, что это процесс активируется тогда, когда у SQL Server заняты около 75% выделенных буферов. Его задача выполнить фиксацию данных и очистить буферы. Для систем имеющих значительный запас памяти этот счетчик должен быть близок к нулю. Как мы видим это так.

Из всего вышесказанного можно сделать вывод: SQL Server имеет достаточный объем памяти и может “поделиться” ей с ОС. Отбирая память у SQL Server (уменьшая “max server memory”) необходимо контролировать выше описанные счетчики и определить тот порог, ниже которого уменьшать объем памяти нельзя.

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