В предыдущих заметках я кратко описал основные методы диагностики производительности сервера MS SQL.
В заключительной части будет приведен пример практического применения описанной методики на реальном сервере. Данная часть будет носить исключительно краткий и иллюстративный характер, так как рассмотренный экземпляр сервера MS SQL не имеет реальных проблем с производительностью в текущем режиме. Параметр max server memory также настроен в соответствии с описанной методикой.
Проверим основные описанные параметры в том порядке, как они упоминались в заметках:
Загрузка процессора провессом MS SQL:
По этому пункту проблем не наблюдается.
Утилизация памяти:
процитирую фрагмент из второй заметки, посвященной использованию памяти -
Согласно материалам technet.microsoft.com(), в свободном распределении у ОС должно оставаться не менее 5% от общего объема оперативной памяти, установленной в системе. По достижению порога Memory: Available Bytes в 100…50 MB Windows включает trimming рабочих наборов процессов, включая системные драйверы, что приводит к резкому снижению производительности всех компонентов ОС(https://technet.microsoft.com/en-us/library/cc958278.aspx)
В нашем случае параметр Memory: Available Bytes имеет значение в 1.2 Gb, что превышает пороговое значение с запасом более чем в два раза.
Проблем с данным параметром также не наблюдается.
Оценим задержку записи операции в журнал.
Процитирую соответствующий пункт:
Запись в журнал происходит через Log Buffer, размер которого определяется самим MS SQL server.
Существую два счетчика, позволяющие оценить задержки операции записи в журнал - SQL Server:Databases: Log Flush Wait Time и SQL Server:Databases: Log Flush Waits/sec.
Для получения среднего времени ожидания при записи данных в журнал транзакций, необходимо разделить показания первого счетчика на показания второго, т. е.
Tср= Log Flush Wait Time/Log Flush Waits/sec = 7/0,786 = 8,9ms
Оценим производительность дисковой подсистемы.
Для латентности записи на диск(счетчик Physical Disk: Avg. Disk sec/Write ) Microsoft приводит пороговое значение 15 мс, однако по материалам Technet реальное значение этой величины составляет 3…4 мс для SAN и 8…10 мс для локальных дисков.
Проверяем показания счетчика Physical Disk: Avg. Disk sec/Write :
Максимальное значнеие счетчика составило 12ms.
Microsoft приводит пороговое значение этой величины в 15 мс, однако по материалам Technet реальное значение этой величины составляет 3…4 мс для SAN и 8…10 мс для локальных дисков.
В данном случае наблюдается единичное и незначительное превышенеи параметра по материалам Technet , так что можно заключить что проблем не наблюдается и по данному пункту.
Как можно заключить из вышесказанного, конкретный экземпляр MS SQL не имеет проблем с производительностью в текущем режиме работы. В случае же возрастания нагрузки узким местом может стать дисковая подсистема и, возможно, загрузка процессора.
Используемые материалы:
https://www.mssqltips.com/sqlservertip/2361/rebuilding-sql-server-indexes-using-the-online-option/
http://www.sql.ru/articles/mssql/02111903performancecounters.shtml
https://msdn.microsoft.com/ru-ru/library/ff929171.aspx
https://msdn.microsoft.com/ru-ru/library/ff878253.aspx
https://blogs.technet.microsoft.com/sqlruteam/
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
