Итак, первая часть нашей серии статей посвящена основам анализа проблем связанных с процессором.

Мы будем следовать алгоритму приведенному в предыдущей статье (http://blogs.technet.com/b/sqlruteam/archive/2014/01/08/sql.aspx).

Посмотрим на загрузку процессоров системы. Для этого используем счетчик Processor:%Processor Time. Максимальные показатели этого счетчика определены на уровне 80...85%%.  Перегружены могут быть как все процессоры, так и только часть процессоров системы. В данной статье мы рассмотрим пример когда перегружены все процессоры, как более простой и общий случай. В дельнейшем мы рассмотрим также перегрузку нескольких процессоров.

Как видим из рисунка ниже в момент времени 1.40...1.52 перегружены все процессоры системы. Говоря строго счетчик Processor:%Processor Time = Processor:%User Time + Processor:%Privilege Time, т.е. это сумма времени работы процессора в режиме пользователя и в режиме ядра, но, поскольку это пока только основы, мы упростим это и будем считать, что процессор все свое время работает в режиме пользователя, т.е. все время обслуживает пользовательские процессы.

 ,

 Как видно из рисунка приведенного ниже, среднее (суммарное) время загрузки процессоров в интересующий нас период времени составляет более 95%, что очень много.

 Также видно, что перегружены все процессоры.

Далее нам предстоит решить задачу отыскания процесса, который "съел" все вычислительные мощности системы. Для этого посмотрим на счетчик Process(...): %Processor Time для каждого процесса. Данный счетчик отображает сумму времени, которое thread-ы, принадлежащие процессу, находятся на процессорах системы. Поэтому не удивляйтесь если увидите значение более 100%. В данном случае мы видим, что для процесса SQLSERVR среднее значение счетчика за указанный период времени около 1576. Если разделить этот показатель на 16 (количество процессоров в системе), то получим 98.5%. Таким образом мы нашли процесс "съевший" все ресурсы процессорной системы, это SQLSERVR.EXE.

Примечание: Исключить из анализа необходимо лишь процесс Idle, который показывает время нахождения Idle thread-ов на процессорах (реально это время простоя процессоров).

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

Как известно из практики эксплуатации SQL Server, процессорная система в наибольшей степени нагружается операциями:

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

Примечание:  Мы предполагаем. что 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.93 т.е. почти 1, что в 10 раз более установленной нормы. Соотношение SQL Compilations/sec к Batch Requests/sec должно быть менее 10%, т.е. SQL Compilations/sec в данном случае должен быть не более 280.

 Соотношение между SQL ReCompilations/sec и SQL Compilations/sec составляет 0.16%, что во много раз менее установленной нормы. Соотношение SQL ReCompilations/sec к SQL Compilations/sec  должно быть менее 10%, т.е. SQL ReCompilations/sec в данном случае должен быть не более 260, а он всего лишь 0.37.

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

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

Норма на соотношение Workfiles Created/sec к Batch Requests/sec  соствляет не более 20%.

Как мы можем видеть в данном случае такой проблемы нет.

 Как видно из приведенных выше рассуждений основной проблемой приводящей к перегрузке процессора является большое количество компиляций планов выполнения. В данном случае количество компиляций в 10 раз превосходит норму. Такая ситуация обычно складывается если приложение активно использует динамический TSQL код. Убедиться в том, что этот именно наш случай можно с помощью счетчиков Performance Monitor.

Для этого необходимо просмотреть состав процедурного кэша. Сделать это можно используя объект Plan Cache и счетчик Cache Pages. Данный счетчик измеряет количество 8-ми килобайтных страниц  выделенных под хранение различных типов планов выполнения. Типы планов отображаются через Instance, и могут быть:

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

Проанализировав показания этих счетчиков мы видим, что 80...90%% объема процедурного кэша составляет SQL Plans, что и приводит к перегрузке процессоров.

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

В любом случае, мы произвели Root Cost Analysis и корневая причина нами определена.

 Наша следующая статья будет посвящена основам анализа проблем с памятью SQL Server.

Александр Каленик, Senior Premier Field Engineer (PFE), MSFT (Russia)