SQL-Ex blog

Введение в dm_os_performance_counters

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



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



Столбец instance_name может содержать пустую строку, но не NULL, поэтому проверка по имени базы данных или другим метаданным весьма простая.

Cntr_value содержит текущее значение счетчика производительности.

Cntr_type является системным значением, которое описывает тип счетчика. Это может оказаться полезным, но при знакомстве с каждым счетчиком и тем, что он делает, этот тип столбца не имеет большого значения. Исчерпывающие подробности по типам и их значениям можно найти здесь:

WMI Performance Counter Types – Win32 apps | Microsoft Docs

Чтобы подытожить общие типы счетчиков, которые рассматриваются в этой статье и обычно используются при обнаружении проблем или планировании мощности, перечислим их:



Мой локальный тестовый сервер SQL Server 2019 с пятью базами данных возвращает 2000 строк из этого представления. Поскольку некоторые счетчики возвращают данные уровня базы данных, число строк, возвращаемых представлением будет увеличиваться с ростом числа баз данных.

Что можно измерить?

Короткий ответ - "много чего". SQL Server поддерживает и делает доступным широкий набор метрик. Некоторые являются общими для всех установок SQL Server, в то время как другие связаны с определенными функциями если они установлены/включены).

Некоторые наиболее общие объекты, которые полезны администраторам и разработчикам, включают: (Заметим, что заголовок содержит имя_объекта, а подробности - имя_счетчика)

SQLServer: менеджер буферов (Buffer Manager)

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

Page Life Expectancy (PLE - ожидаемая продолжительность жизни страницы:): Показатель того, насколько долго страницы остаются в буферном кэше, прежде чем они удаляются и заменяются более новыми страницами. При интенсивном использовании памяти старые страницы удаляются из кэша более агрессивно, освобождая место новым.

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

Это исключительно полезная метрика, которая приобретает ценность, если наблюдается в течение длительного периода времени, как для предупреждения/мониторинга, так и для планирования мощности. Число, выдаваемое представлением, показывает значение на момент времени, когда делалась выборка:



Поскольку мой локальный сервер не обрабатывал сейчас больших данных (и у меня есть много памяти), число довольно большое. В этом случае он не испытывал проблем с памятью с момента последнего перезапуска, и, следовательно, PLE составляет более 23 дней.

Page Reads /sec, Page Writes /sec, Page Lookups /sec, Lazy Writes /sec: Меры физических и логических чтений, а также записи в/из буферного кэша. Это дает полное представление о вводе/выводе в SQL Server и количество I/O, которое выполняет приложение на сервере баз данных. Page Reads и Page Writes показывают физические чтения в/из кэша. Page Lookups измеряет число раз, когда данные были запрошены из кэша. Lazy Writes измеряет количество записей в хранилище вне контрольной точки из-за нехватки памяти.

Поскольку физический ввод/вывод является дорогим, рост числа физических чтений может говорить о нехватке памяти.

Так как размер страницы известен - 8Кб, с помощью арифметических вычислений можно определить число IO на данном SQL Server. Например, если сервер показывает в среднем 1 миллион чтений страниц в секунду и 10К записей страниц в секунду, то можно сделать вывод, что буферный кэш обслуживает чтение 7,629Гб в секунду и запись 78,125Мб в секунду (путем деления на 8192 для получения байтов и приведения их к выбранным единицам хранения).

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

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

Target Pages: Это целевое количество страниц, которое SQL Server хочет сохранить в буферном кеше. Добавление памяти увеличивает это число, в то время как сокращение памяти уменьшает его. Если количество данных, необходимое для регулярного обслуживания запросов существенно превышает это число, то будет проявляться нехватка памяти, что будет выражаться в физических чтениях и сокращении ожидаемой продолжительности жизни страницы. Как и для других данных, связанных со страницами, умножение на 8Кб дает необработанный объем данных. Например, на моем локальном сервере показатель текущего значения счетчика имеет следующее значение:



Это равно приблизительно 21,49 данных (2816743 * 8 / 1024²).

SQLServer:Общая статистика (General Statistics)

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

Logins /sec and Logouts /sec: Они отслеживают накопительное число входов и выходов во времени. Взятие разницы между двумя выборками дает число входов и выходов за этот период времени. Ниже представлены две выборки на моем локальном сервере, которые отстоят друг от друга на 10 минут:



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

Скорость создания временных таблиц (Temp Tables Creation Rate): Этот счетчик предоставляет накопительные итоги по созданию временных таблиц и табличных переменных на SQL Server. Это может быть удобной мерой для выявления процессов на сервере, которые создают чрезмерное количество временных объектов, и когда их создается наибольшее количество.

Отметим, что этот счетчик измеряет только создание таблицы, но не повторное использование. Если T-SQL создает временный объект и продолжает его использовать во многих операциях, отмечаться будет только его создание. Следующий код выбирает этот счетчик до/после создания временной таблицы:

SELECT

	*

FROM sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:General Statistics'

AND counter_name = 'Temp Tables Creation Rate';

CREATE TABLE #test2 (id INT);

SELECT

	*

FROM sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:General Statistics'

AND counter_name = 'Temp Tables Creation Rate';

DROP TABLE #test2;

SELECT

	*

FROM sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:General Statistics'

AND counter_name = 'Temp Tables Creation Rate';

Результаты демонстрируют увеличение значения на единицу после создания временной таблицы:



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

SQLServer: Статистика SQL (SQL Statistics)

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

Все представленные ниже метрики накопительные. Следовательно, потребуется снять несколько точек во времени, чтобы сравнить и определить изменение во времени.

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

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

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

SQL Compilations/sec и SQL Re-Compilations/sec: Эти счетчики измеряют активность оптимизатора запросов. Когда запрос выполняется впервые, оптимизатор запросов генерирует план выполнения, который кэшируется для повторного использования. В общем показатель компиляции будет высоким, если:

• SQL Server или приложение стартует первый раз.


• Когда работая нагрузка резко меняется.


• Когда нехватка памяти выбрасывает планы из кэша.


• Когда нерегламентированные (ad-hoc) запросы перегружают кеш планов выполнения.

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

Перекомпиляция имеет место, когда существующий план выполнения в кэше отбрасывается во время исполнения запроса и заменяется новым планом. Это может произойти по многим причинам, как то:

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


• Использован хинт RECOMPILE.


• Изменился DDL схемы, которая используется в плане выполнения.


• Изменились опции в операторе SET, которые влияют на планы выполнения.

Полный список причин можно получить с помощью этого запроса:

SELECT

	*

FROM sys.dm_xe_map_values

WHERE dm_xe_map_values.name = 'statement_recompile_cause';

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

Как компиляции, так и перекомпиляции могут происходить по разным причинам, которые здесь не обсуждались, начиная со строк подключения к базе данных или настроек сервера, а также плохо написанного кода T-SQL. Глубокое обсуждение этой темы выходит за рамки данной статьи, но её стоит изучить, если проблемы с производительностью лежат в этой области.

Счетчики базы данных (Database Counters): Имеется множество метрик, которые собираются на уровне базы данных, который может обеспечить более детальное понимание производительности сервера. Имеется подробная информация о размере и использовании файлов данных и журнала, числа транзакций в секунду, рост/сжатие журнала, число записывающих транзакций в секунду и многое другое. Столбец instance_name содержит имя базы данных:



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

Еще!

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

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

Производительность dm_os_performance_counters

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

На моем локальном сервере с 6 пользовательскими базами это число около 2000.

На другом сервере с 92 пользовательскими базами данных число строк возрастает до 7700.

На другом сервере с 665 пользовательскими базами данных число строк возрастает до 19781.

На еще одном сервере с 4141 базами данных количество строк возрастает до 278000.

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

Поэтому следует тестировать производительность до использования этого кода в рабочем окружении. Убедитесь, что он выполняется быстро, а если нет, рассмотрите возможность сокращения частоты выполнений, чтобы он не выполнялся просто в режиме нон-стоп, весь день собирая данные счетчиков. Не говоря уже о том, что сбор метрик сервера и баз данных никоим образом не должен мешать работе сервера, который мониторится.

Данные монитора производительности SQL Server

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

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

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