Использование монитора производительности для определения узких мест аппаратных средств, на которых запущен SQL Server

Список контрольных вопросов аудита производительности

Введите ваши результаты в таблицу, приведенную выше.

Использование монитора производительности (Performance Monutor) для идентификации узких места аппаратных средств SQL Server

Лучше всего начать аудит производительности SQL Server с монитора производительности (System Monitor). Мониторинг нескольких основных счетчиков за период 24 часов позволит вам получить довольно хорошее представление о любых главных аппаратных проблемах, которые сказываются на производительности SQL Server.

В идеале Вы должны использовать монитор производительности для создания файла регистрации (журнала) показаний ключевых счетчиков сроком на 24 часа. Вам предстоит выбрать "типичный" 24-часовой период для создания файла регистрации. Например, выберите типичный рабочий день, не конец недели или праздник.

Как только Вы зафиксировали данные монитора производительности за 24 часа в файле регистрации, отобразите рекомендованные счетчики в режиме Graph монитора производительности, и затем запишите среднее, минимальное и максимальное значения в вышеприведенную таблицу. Как только Вы сделали это, сравните ваши результаты с результатами приведенного ниже анализа. Это сравнение даст вам возможность определить любые потенциальные узкие места аппаратных средств, которые влияют на ваш SQL Server.

Как интерпретировать ключевые счетчики монитора производительности

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

Память: Страницы/секунды

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

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

Если ваш сервер показывает в среднем больше чем 20 страниц в секунду, одной из наиболее вероятных причин этого является нехватка необходимой оперативной памяти. Вообще говоря, чем больше оперативной памяти имеется, тем меньше должно выполняться операций по обмену страницами.

В большинстве случаев, на физическом сервере, специализированном под SQL Server, с адекватным количеством оперативной памяти, среднее значение обмена страницами будет меньше чем 20. Адекватное количество оперативной памяти для SQL Server можно определить по следующему критерию: сервер должен иметь коэффициент удачного обращения в кэш буфера (Buffer Hit Cache Ratio) 99 % и выше. Данный счетчик описан ниже в этой статье. Если Вы имеете SQL Server, у которого этот коэффициент имеет значение 99 % или выше в течение 24 часов, но Вы получаете среднее значение обмена страницами более 20 в течение того же самого периода времени, это может указывать на то, что у Вас выполняются и другие приложения на физическом сервере помимо SQL Server. Если дело обстоит именно так, Вы должны в идеальном случае удалить эти приложения, позволив SQL Server быть единственным главным приложением на физическом сервере.

Если ваш Сервер SQL не выполняет никакие другие приложения, и обмен страницами превышает 20 в среднем в течение 24 часов, это может означать, что Вы изменили параметры настройки памяти SQL Server. SQL Server должен быть конфигурирован так, чтобы была установлена опция "Dynamically configure SQL Server memory" (Динамически конфигурировать память SQL Server), а установка "Maximum Memory" должна находиться в наибольшем значении. Для оптимальной работы, SQL Server нужно позволить взять столько оперативной памяти, сколько ему требуется для собственных нужд, не испытывая необходимости конкурировать за оперативную память с другими приложениями.

Память: Доступное пространство

Другой способ выяснить, имеет ли ваш SQL Server достаточно физической оперативной памяти, состоит в том, чтобы проверить счетчик Memory Object: Available Bytes. Его значение должно быть более 5 МБ. В противном случае, ваш Сервер SQL нуждается в большем количестве физической оперативной памяти. На сервере, специализированном под SQL Server, последний пытается удерживать от 4-10MB свободной физической памяти. Оставшаяся физическая оперативная память используется операционной системой и SQL Server. Когда объем доступной памяти близко к 5 МБ или ниже, наиболее вероятно, что SQL Server испытывает перегрузку из-за нехватки памяти. Если это имеет место, Вы должны увеличить количество физической оперативной памяти в сервере, уменьшить нагрузку на сервер или изменить параметры настройки конфигурации памяти вашего SQL Server соответственно.

Физический диск: Время работы диска %

Этот счетчик показывает, насколько занят физический дисковый массив (не логический раздел или отдельный диск в массиве). Он обеспечивает хорошую относительную меру того, насколько заняты ваши дисковые массивы.

Как эмпирическое правило, счетчик времени диска должен показывать менее 55 %. Если показания счетчика превышают 55 % в течение непрерывных периодов (свыше 10 минут в течение ваших 24 часов мониторинга), то ваш SQL Server может испытывать проблемы с операциями ввода/вывода. Если Вы наблюдаете это поведение лишь изредка в течение ваших 24 часов мониторинга, я бы не волновался слишком сильно, но если бы это случалось часто (скажем, несколько раз час), то я начал бы искать способы увеличить производительность операций ввода/вывода на сервере или уменьшить загрузку сервера. Некоторые способы увеличивать дисковый ввод/вывод состоят в добавлении новых дисков в массив (если это возможно), замены дисков на более быстрые, добавлении кэш-памяти на плате контроллера (если это возможно), использования различных версий RAID или установки более быстрого контроллера.

Перед использованием этого счетчика под NT 4.0, нужно вручную включить его, введя в Command Prompt следующее: "diskperf-y". После этого нужно будет перезагрузить ваш сервер. Таким образом, требуется сразу включать дисковые счетчики под Windows NT 4.0. Если Вы работаете под Windows 2000, этот счетчик включен по умолчанию.

Физический диск: Средняя длина очереди диска

Помимо наблюдения за значением счетчика "Физический Диск: Время работы диска", желательно также отслеживать значения счетчика средней длины очереди диска (Avg. Disk Queue Length). Если это значение превышает значение 2 для непрерывных периодов (свыше 10 минут в течение вашего 24 часового мониторинга) для каждого дисковода в массиве, то этот массив может оказаться узким местом производительности системы. Подобно счетчику времени работы диска, если это происходит изредка в течение 24 часов периода мониторинга, я не сильно бы волновался, но если это происходит часто, тогда я бы начал искать способы увеличить производительность системы ввода/вывода сервера, как это описано выше.

Вам придется вычислить этот показатель, поскольку Performance Monitor не знает, сколько физических дисков находится в вашем массиве. Например, если у вас имеется массив из 6 физических дисков, и средняя длина очереди равна 10 для этого массива, тогда фактическое среднее значение дисковой очереди для каждого диска составляет 1.66 (10/6=1.66), что хорошо укладывается в рекомендованный показатель 2 на один физический диск.

Перед использованием этого счетчика под NT 4.0, не забудьте вручную включить его, набрав на приглашение к вводу команд NT (Command Prompt) следующее: "diskperf-y" с последующей перезагрузка вашего сервера. Поэтому требуется включать дисковые счетчики сразу после установки Windows NT 4.0. Если Вы используете Windows 2000, то этот счетчик будет включен по умолчанию.

Используйте оба описанных выше счетчика, чтобы точно выяснить, испытывает ли ваш сервер проблемы с системой ввода/вывода. Например, если Вы видите много периодов времени, в течение которых время работы диска более 55 %, и когда среднее значение длины очереди диска составляет более 2 на один физический диск, Вы можете быть уверенными, что сервер имеет проблемы с системой ввода - вывода.

Процессор: Процессорное время %

Счетчик Processor Object: % Processor Time имеется для каждого центрального процессора и оценивает использование каждого отдельного центрального процессора. Аналогичный счетчик имеется также для всей совокупности центральных процессоров (общее количество). Это ключевой счетчик для слежения за использованием центрального процессора. Если общее время загрузки процессоров по этому счетчику превышает 80 % в течение непрерывных периодов (свыше 10 минут в течение 24 часового периода мониторинга), то Вы можете считать центральный процессор узким местом системы. Если эти периоды сильной загрузки происходят изредка, и Вы полагаете, что можете смириться с этим, то все в порядке. Но если они возникают часто, Вам следует рассмотреть такие варианты снижения загрузки сервера, как приобретение более быстрых центральных процессоров, установку большего количества центральных процессоров, или приобретение центральных процессоров, которые имеют больший встроенный кэш второго уровня (L2).

Система: Длина очереди процессора

Наряду со счетчиком процессорного времени, Вам следует также контролировать счетчик длины очереди процессора (Processor Queue Length). Если этот показатель превышает значение 2 на один центральный процессор в течение непрерывных периодов (свыше 10 минут в течение вашего 24 часового периода мониторинга), то вероятно это является узким звеном системы. Например, если на Вашем сервере имеется 4 центральных процессора, длина очереди процессора не должна превышать в общей сложности значение 8.

Если длина очереди процессора регулярно превышает рекомендованный максимум, но использование центрального процессора не настолько высоко (что является типичным случаем), то рассмотрите вариант уменьшения значения конфигурационного параметра SQL Server "max worker threads" (максимального числа нитей). Возможной причиной высокого значения длины очереди процессора является наличие избыточного числа рабочих нитей, дожидающихся своей очереди. Уменьшая их число, что Вы и делаете с помощью этого параметра, вынуждает задействовать пулинг нитей (если это еще не имеет место), или повысить его роль.

Используйте оба описанных счетчика совместно, чтобы точно определить, есть ли проблемы с центральным процессором. Если оба индикатора превышают рекомендованные значения в течение одних и тех же непрерывных периодов времени, Вы можете быть уверены, что центральный процессор является слабым местом системы.

Буфер SQL Server: Коэффициент удачного обращения в кэш буфера

Этот счетчик (SQL Server Buffer: Buffer Cache Hit Ratio) показывает, как часто SQL Server обращается к буферу, а не к жесткому диску, чтобы получить данные. В приложениях OLTP, этот коэффициент должен превышать 90 %, а в идеале быть выше 99 %. Если ваш коэффициент удачного обращения в буферный кэш ниже 90 %, Вам следует пойти и купить больше оперативной памяти уже сегодня. Если этот коэффициент лежит в диапазоне между 90 % и 99 %, то Вы должны серьезно рассмотреть вариант покупки дополнительной оперативной памяти, так как чем ближе Вы приближаетесь к 99 %, тем быстрее ваш SQL Server будет работать. В некоторых случаях, если ваша база данных является очень большой, Вам не удастся приблизиться к 99 %, даже если Вы поставите максимально допустимое количество оперативной памяти на вашем сервере. Тогда все, что Вы можете сделать, - это добавить память по максимуму и смириться с существующим положением вещей.

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

SQL Server: Пользовательские подключения

Поскольку число пользователей Сервер SQL, влияет на его производительность, рекомендуется следить за счетчиком пользовательских подключений (SQL Server General Statistics Object: User Connections counter). Он показывает число пользовательских подключений, а не число пользователей, которые подключены к SQL Server в данный момент времени.

Если показания этого счетчика превышают 255, то Вам следует увеличить значение конфигурационного параметра "Maximum Worker Threads" (максимальное число рабочих нитей), значение по умолчанию которого равно 255. Если число подключений превышает имеющееся число рабочих нитей, то SQL Server начнет совместно использовать рабочие нити, что может отрицательно сказаться на производительности. Установка этого параметра должна быть выше, чем максимальное число подключений, которое может быть достигнуто на вашем сервере.

Что дальше

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

Brad M. McGehee (оригинал: Using Performance Monitor to Identify SQL Server Hardware Bottlenecks)
Перевод: Моисеенко С.И.
Оригинал перевода