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

Конкретный пример анализа с использованием приведенной информации будет рассмотрен в следующей статье.

Согласно материалам MS Technet, основными операциями, при которых может происходить потеря производительности, являются:

•     чтение страниц файла данных для загрузки их в Буферный пул;
•     запись данных в файл журнала транзакций при завершении транзакций (Commit);
•     Запись страниц в файл данных для фиксации произведенных модификаций

Остановимся немного подробнее на каждом пункте.

    чтение страниц файла данных для загрузки их в Буферный пул;

Счетчик Buffer Cache Hit Ratio среди объектов SQL Server: Buffer Manager показывает, насколько полно SQL Server может разместить данные в буфере кэша. Чем выше это значение, тем лучше, т.к. для эффективного обращения SQL сервера к страницам данных, они должны находиться в буфере кэша, и операции физического ввода-вывода (I/O) должны отсутствовать. Если Вы наблюдаете устойчивое снижение среднего значения этого счётчика, рассмотрите возможность добавление ОЗУ. - http://www.sql.ru/articles/mssql/02111903performancecounters.shtml

Даже при высоком показателе счетчика Buffer Cache Hit Ratio может оказаться, что необходимая для выполнения операции строка находится на диске, и потребуются две операции ввода — физическая и логическа. Время выполнения первы составляет до десятков миллистекунд, и второй — от десятков нано- до десятков микросекунд.

Исходя из разницы времени, необходимого на выполнение этих оперций следует, что наличие даже одной такой строки уже приводит к некоторому падению проивзодительности(конечно, в реальности одна строка не приведет к значительному замедлению работы, плюс существуют специальные алгоритмы для предотвращения подобных ситуаций; в данной заметке ситуация намеренно упрощена для наглядности).

В своих материалах Microsoft приводит пороговое значение на латентность операции чтения с дисков для SQL Server в 15мс(счетчик Physical Disk: Avg. Disk sec/Read ). В случае, если показатели данного счетчика превышают 15 мс в течении длительного времени, это может указывать на недостаточную производительность дисковой подсистемы.

    запись данных в файл журнала транзакций при завершении транзакций (Commit);


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


Операция же Commit является сигналом для СУБД, что данная модификация выполнена успешно и доведена до конца. Таким образом, скорость выполнения транзакций зависит от задержек появления записи Commit в журнале транзакций.


Запись в журнал происходит через 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.

Для латентности записи на диск(счетчик Physical Disk: Avg. Disk sec/Write ) Microsoft приводит пороговое значение 15 мс, однако по материалам Technet реальное значение этой величины составляет 3…4 мс для SAN и 8…10 мс для локальных дисков.

Превышение указанных значений указывают на проблему с производительностью дисковой подсистемы.


    Запись страниц в файл данных для фиксации произведенных модификаций


Согласно материалам Microsoft, эти операции производятся Redo thread-ами в асинхронном режиме и обычно не приводят к проблемам производительности, однако возможны следующие ситуации, когда происходит снижение производительности:
 

•     Перестройка индексов. Redo thread-ы не успевают достаточно быстро записать модифицированные данные в файлы данных после перестройки индексов , что приводит к недоступности объектов с индексами
•     Зеркальная база AlwaysOn. Redo thread на зеркальной базе не успевает записать данные в файлы данных, что приводит к неработоспособности зеркала.
•     Восстановление БД из резервной копии. Redo thread при восстановлении базы из резервной копии не успевает быстро записать данные в файлы данных

Проблема указанная в первом пункте может быть решена с помощью Online Index Rebuilding(https://www.mssqltips.com/sqlservertip/2361/rebuilding-sql-server-indexes-using-the-online-option/).

В случае использования AlwaysOn можно оценить разницу между данными, первичной и вторичной реплик, для чего необходимо разделить показания счетчика Database Replica: Recovery Queue на показания Database Replica: Redone Bytes/sec.

Конкретного порогового значения для этого показателя в материалах Microsoft найти не удалось, поэтому остается только оценивать его динамику в конкретной системе.


Материалы:

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/