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

Недавно у меня был клиент-консультант, который был обеспокоен низким использованием памяти их экземпляра SQL Server. Несмотря на наличие большой базы данных объемом 500 ГБ, SQL Server использовал всего несколько сотен мегабайт памяти в соответствии с диспетчером задач в Windows. Это вызвало вопросы о том, почему SQL Server не использует максимально возможное количество памяти.

При дальнейшем исследовании мы обнаружили, что клиент предоставил привилегию “Заблокированные страницы в памяти” учетной записи службы, на которой работает SQL Server. Эта привилегия предотвращает операционную систему Windows от вытеснения памяти, выделенной SQL Server. Хотя эта привилегия может быть полезна в определенных ситуациях, она также может привести к проблемам с производительностью, если ее не управлять правильно.

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

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

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

Начиная с SQL Server 2005 SP2, Microsoft ввело сообщение об ошибке в журнале SQL Server, чтобы указать, когда происходит сокращение рабочего набора. Это сообщение служит предупреждением о том, что производительность SQL Server может быть снижена из-за вытеснения памяти. Включение привилегии “Заблокированные страницы в памяти” может предотвратить такие проблемы, не позволяя операционной системе Windows вытеснять память, выделенную для Буферного пула.

Следует отметить, что “Заблокированные страницы в памяти” используются специально для выделений Буферного пула в SQL Server. Поэтому важно ограничить память, выделенную для Буферного пула, с помощью настройки Max Memory вашего экземпляра SQL Server при использовании этой привилегии.

Теперь вы можете задаться вопросом, почему диспетчер задач сообщает неверные размеры рабочего набора, когда SQL Server использует привилегию “Заблокированные страницы в памяти”. Причина заключается в основных функциях Win32API, используемых SQL Server для выделения памяти.

По умолчанию SQL Server использует функцию VirtualAlloc для выделения Буферного пула. Однако, когда учетная запись службы SQL Server имеет привилегию “Заблокированные страницы в памяти”, SQL Server внутренне переключается на использование функции AllocateUserPhysicalPages Win32API. Эту функцию можно вызывать только при наличии процесса привилегии SeLockMemoryPrivilege, которая является дружественным именем для привилегии “Заблокированные страницы в памяти”.

Функция AllocateUserPhysicalPages является частью API Address Windowing Extensions (AWE), и память, выделенная с помощью этой функции, не может быть вытеснена операционной системой Windows. Это связано с тем, что области памяти AWE предназначены для невытесняемой памяти. Поэтому диспетчер задач не отображает эти выделения памяти, что приводит к расхождению в отчете о размере рабочего набора.

Чтобы точно определить использование памяти SQL Server, вы можете использовать различные методы. Внутри SQL Server вы можете использовать DMV sys.dm_os_process_memory и столбец physical_memory_in_use_kb для получения физической памяти, используемой в данный момент SQL Server, включая выделения памяти AWE.

Для более подробного разбивки использования памяти вы можете использовать DMV sys.dm_os_memory_clerks. Каждый памятный клерк представляет собой конкретный компонент в SQL Server, а столбец awe_allocated_kb отображает выделения памяти AWE, сделанные с помощью функции AllocateUserPhysicalPages.

Кроме того, вы можете отслеживать размер Буферного пула с помощью счетчика производительности “SQLServer:Memory Manager/Total Server Memory (KB)” вне SQL Server.

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

Спасибо за чтение!

– Ваше имя