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

Основными компонентами, влияющими на производительность SQL Server, являются:

• процессоры;
• память;
• диски;
• сеть.

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

В случае, если загрузка компонентов не превышает критических значений, проблема с наибольшей вероятностью лежит в неоптимальности внутренней структуры SQL Server, и потребуется анализ с помощью SQL Profiler Trace, DMV, DBCC, а также наличие специальных знаний.

Загрузка процессора

Используется счетчик Processor:%Processor Time. Критическими показателями для данного счетчика можно считать загрузку 80-85%, причем перегружены могут быть как все процессоры системы, так и часть из них.

Счетчик Processor:%Processor Time учитывает User Time и Privilege Time, т.е. т.е. это сумма времени работы процессора в режиме пользователя и в режиме ядра, однако в данном случае для упрощения считается, что процессор работает только в пользовательском режиме, время на обслуживание ядра не учитывается.

Предположим, мы видим такую картину(искомая область выделена синим):

Далее следует найти процесс, загружающий систему. Для этого смотрим на счетчик Process(…): %Processor Time для каждого процесса(кроме процесса Idle, отражающего время простоя процессоров).

Видимо, что среднее значение счетчика составляет примерно 1577, и, поделив на общее количество процессоров(в данном примере -16, иллюстрация взята из сети Internet для конкретного примера), получаем загрузку процессора данным процессом 1577/16=98,6%, что превышает пороговое значение в 80-85%.

Таким образом, в данном примере sqlsrv.exe оказался причиной выской загрузки системы.

В дальнейшем необходимо будет найти причину, по которой sqlsrv.exe потребляет настолько значительное процессорное время. В случае, если причина найдена не будет, это означает что система просто потребляет значительные ресурсы CPU, и подошла к пределу своей производительности.

Процессорная подсистема в наибольшей степени нагружается операциями:

• Компиляции и рекомпиляции планов выполнения;
• Операциями сортировки;
• Операциями хеширования.

Согласно материалам technet и другим статьям, опубликованным в сети Internet, описывающих работу SQL Server, основными соотношениями, позволяющими определить прчину перегрузки процессора, являются:

• Соотношение между SQL Compilations/sec и Batch Requests/sec
• Соотношение между SQL ReCompilations/sec и SQL Compilations/sec
• Соотношение между Workfiles Created/sec и Batch Requests/sec

Нормой для данных соотношений можно считать:

• SQL Compilations/sec/Batch Requests/sec = 0,1 (10%)
• SQL ReCompilations/sec/SQL Compilations/sec = 0,1 (10%)
• Workfiles Created/sec/Batch Requests/sec = 0,2 (20%)

Остановившись на последнем пункте, стоит пояснить, что Workfile – это часть страниц файла данных выделенных для внутренних нужд SQL Server. Отличие Workfile от Worktable состоит в том, что Worktable содержит страницы файла связанные структурами метаданных (IAM) и зарегистрированными в системных таблицах, а Workfile это просто страницы файла данных не объединенные воедино метаданными (IAM). SQL Server активно использует Workfiles для выполнения операций хеширования и хранения промежуточных результатов хеширования (например Hash Backet-ов). (Цитируется по материалам microsoft technet)

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

В случае, когда основной причиной загрузки процессора является первый пункт(Компиляции и рекомпиляции планов выполнения), это может указывать на использование приложением динамического TSQL кода. Для подтверждения этого предположения необходимо просмотреть состав процедурного кэша. Сделать это можно используя объект Plan Cache и счетчик Cache Pages(счетчики  Performance Monitor).

Cache Pages измеряет количество 8-ми килобайтных страниц  выделенных под хранение различных типов планов выполнения. Типы планов(отображаемые через Instance) могут быть:

• Bound Trees (Результаты алгебраизации View, алгоритмы выборки данных из View)
• Extended Storage procedures (Планы выполнения расширенных хранимых процедур)
• Object Plans (Планы выполнения хранимых процедур, триггеров и некоторых видов функций)
• SQL Plans (Планы выполнения динамического TSQL кода, сюда же попадают автопараметризованные запросы) 
• Temporary Tables & Tables Variables (Кэшированные метаданные по временным таблицам и табличным переменным).

Анализируя показания этих счетчиков, в случае если SQL Plans составляет значительную часть объема процедурного кэша, предположение подтверждается. Дальнейшие действия потребуют анализа кода и его оптимизации.

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

Используемые материалы:

Microsoft Technet

Жуков М.А., "Проблема с производительностью старта портала в IBM WebSphere"