Блог инженеров технической поддержки SQL Server. Microsoft. Россия

Блог инженеров технической поддержки SQL Server. Microsoft. Россия

Основы анализа производительности SQL Server. Перегрузка процессоров.

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

Мы будем следовать алгоритму приведенному в предыдущей статье (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)