В первой части рассматривался краткий анализ производительности MS SQL с точки зрения использования ресурсов CPU. Во второй части будет рассмотрен вопрос оценки утилизации оперативной памят и расчета оптимального значения параметра max server memory.
Согласно https://blogs.technet.microsoft.com, в целом можно выделить следующие основные проблемы, связанные с потреблением оперативной памяти:
-
Потребление MS SQL Server практически всего доступного объема оперативной памяти системы, что вызвало недостаток памяти для ОС и прочих приложений(в т.ч. системных)
-
Нехватка памяти для MS SQL Server в случае установленного ограничения на доступный объем памяти
-
Стороннее приложение, работающее на той же системе, где развернут MS SQL Server потребляет слишком большой объем памяти, что приводит к недостатку свободной памяти для MS SQL Server (ситуация, в чем-то противоположная первому пункту)
-
Избыточное потребление памяти каким-либо из компонентов MS SQL Server.
Остановимся подробнее на первом пункте.
Для того чтобы установить, что ОС и системным приложениям действительно не хватает памяти, и причина именно в MS SQL Server, необходимо оценить показания счетчика Memory: Available Bytes(этот счетчик показывает, сколько памяти доступно ОС для распределения приложения и внутреннего использования).
Согласно материалам technet.microsoft.com(), в свободном распределении у ОС должно оставаться не менее 5% от общего объема оперативной памяти, установленной в системе. По достижению порога Memory: Available Bytes в 100…50 MB Windows включает trimming рабочих наборов процессов, включая системные драйверы, что приводит к резкому снижению производительности всех компонентов ОС(https://technet.microsoft.com/en-us/library/cc958278.aspx)
В данной заметке не будет рассматриваться вариант, когда происходит утечка памяти в приложении, например утечка невыгружаемого пула. Остается вариант, когда установлено завышенное значение параметра MS SQL Server max server memory, а также случай установки опции “Lock Pages in Memory” для УЗ, от которой запускается MS SQL.
-
Некластеризованный SQL Server, либо кластеризованный SQL Server в режиме Актив/Пассив .
Допустим, в системе где развернут MS SQL имеется 10GB оперативной памяти и используется 6 ядер CPU.
-
Вычисляем остаток для ОС(согласно Microsoft - 5%). В данном примере 10*0,05 = 500MB минимум.
-
Учитываем потребление памяти ядромSQL Server, SQL heap, CLR. Ориентировочно можно принять значение в 500Mb-1Gb(по данным /blogs.technet.microsoft.com/sqlruteam/).
-
Пямять под кэши рабочих потоков (Worker thread). В том же источнике находим формулу MemVol=(512+(NumCpu-4)*16)*2 MB, где MemVol - искомое значение, NumCpu - число используемых логических ядер. Т.е.MemVol= (512+ (6-4)*16)*2 = 1088Mb, или округленно 1Gb.
Значение “max server memory” вычисляется как: 10GB(Общий объем памяти сервера) – 0.5Gb(остаток для распределния ОС) – 1Gb(потребляемая ядром MS SQL память) – 1Gb(Пямять под кэши рабочих потоков )= 7.5GB. Для версии SQL 2012 и далее “max server memory” включает в себя SQL heap и частично CLR, поэтому в расчете можно брать меньшее значение в 500MB.
В случае использования опции Lock Pages In Memory нежелательно превышать рассчетный объем max server memory или же использовать значение по умолчанию(т.е. неограниченно).
-
Кластеризованный SQL Server(два узла) работающий в режиме Актив/Актив.
При расчете необходимо учитывать, что пункты 2, 3 и результирующее значение max server memory будут больше в два раза. При использовании Lock Pages In Memory необходимо не только установить максимальный объем используемой памяти, но и минимальный,чтобы в случае использования одного узла обоими SQL Server не возникло ситуации, когда ОС станет не хватать памяти.
Вторая часть этой заметки, "Краткий анализ производительности MS SQL Server, часть 2.2", будет посвящена оценке использования памяти реальным экземпляром MS SQL Server и расчетом оптимальных значений, исходя из показаний счетчиков для иллюстрации приведенной методики.
Используемые материалы:
https://blogs.technet.microsoft.com
http://msdn.microsoft.com/en-us/library/windows/desktop/aa366525(v=vs.85).aspx
http://blogs.msdn.com/b/tims/archive/2010/10/28/pdc10-mysteries-of-windows-memory-management-revealed-part-one.aspx
http://blogs.technet.com/b/askperf/archive/2007/02/23/memory-management-101.aspx http://msdn.microsoft.com/en-us/library/windows/hardware/gg463344.aspx
