Как неправильное значение Max Server Memory может приводить к проблемам с SQL-сервером?

Если Вы сталкивались с таким симптомом, что после перезапуска SQL-сервер работает нормально, а потом постепенно все медленнее, пока его снова не приходится перезапускать - это может быть признаком неправильно установленного параметра Max Server Memory (максимально доступная серверу память).

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

В этом случае и возникает проблема, поскольку SQL-сервер изначально создавался с таким «прицелом», что это самое важное приложение в системе, и когда операционная система требует больше памяти себе, он не освобождает ее так быстро, как «обычная программа», и делает это маленькими порциями, пока ОС не прекратит присылать запросы на освобождение памяти.

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

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

По материалам с сайта idera.com, Glenn Berry’s SQL Performance, TechNet.microsoft.com