Используем SQL Server Dynamic Management Views and Functions (DMV and DMF)

Microsoft SQL 2005/2008 предоставляет доступ к динамическим административным представлениям и функциям (Dynamic Management Views and Functions, DMV and DMF). Эти данные мы можем использовать для диагностики проблем и оптимизации производительности баз данных.
Вот неполный перечень доступной информации:

• Причины задержек выполнения запросов.
• Работа с индексами (отсутствующие, неиспользуемые, требующие больше всех операций ввода/вывода, часто используемые).
• Запросы с высокими издержками на ввод-вывод, с высоким использованием процессора.
• Запросы, выполняющиеся чаще всего.
• Запросы, страдающие от блокировок.

Полное описание всех функций можно найти в MSDN — Динамические административные представления и функции (Transact-SQL). Для тех, кто еще не сталкивался с запросами к DMV – рекомендую ознакомиться со статьей из журнала MSDN за январь 2008-ого года: "Открытие скрытых данных для оптимизации производительности приложений". Она довольно обширна, содержит общую информацию и большое количество готовых часто используемых запросов к DMV.
Далее, я хочу рассказать о том, как можно сохранять и анализировать данные запросов к DMV.

Термины и определения
DMV, DMF – Динамические административные представления и функции (Dynamic Management Views and Functions). Возвращают данные о состоянии сервера, которые могут использоваться для контроля исправности экземпляра сервера, диагностики проблем и настройки производительности.
Хранилище – база данных для накопления информации, полученной запросами к DMV и DMF для последующего анализа.

Автоматический сбор и анализ данных DMV и DMF

Опишу, на мой взгляд, самые крупные недостатки работы с DMV через простые SQL запросы:

1. Данные DMV содержатся в памяти и начинают накапливаться заново после каждого перезапуска сервера. Кроме этого, они обнуляются для некоторых объектов при различных изменениях в структуре таблиц или индексов.
2. Большая информационная система дает неравномерную нагрузку на базу данных. Часть функционала может использоваться редко и поэтому некоторые объекты базы данные могут быть не учтены в DMV или учтены неверно. Например, это могут быть ежедневная выгрузка большого количества данных или ежемесячный отчет. Кроме этого, часто бывает необходимость сравнить показатели до и после изменений. Это сложно сделать, не имея на руках исторических данных из DMV и DMF.
3. Не всегда есть возможность работать напрямую с базой данных, т.е. когда можно выполнить произвольные запросы к DMV.

Для решения этих проблем можно использовать следующие инструменты.

DMVStats

A SQL Server 2005 Dynamic Management View Performance Data Warehouse
Адрес проекта на CodePlex: http://sqldmvstats.codeplex.com

Это приложение накапливает сведения из DMV и DMF в своей базе данных и предоставляет набор готовых отчетов SQL Reporting Services для анализа. Для сбора данных используется задание (jobs) SQL Server Agent.
Список готовых отчетов:

Analyze Block Info Executing Requests and Waiting Tasks Index Definitions Index Operational Stats Top IO Queries

Top Worker Queries Virtual File Stats Wait Stats Drill-through Guidance Alerts

Установка и конфигурация
Инструкция по установке, конфигурации и использованию: DMVStats.doc. Если кратко, то процесс установки автоматизирован двумя скриптами. Скрипт InstallDMVStatsDBProcs.cmd создает БД хранилища, необходимые sp процедуры и устанавливает задания SQL Server Agent. Скрипт DeployReports.cmd необходим для добавления отчетов на MS SQL Reporting Services.
Управление и конфигурация приложения происходит с помощью отчетов на SQL Reporting Services.


Для изменения времени задания, для его включения или отключения есть готовые скрипты в каталоге Util архива приложения.

Примечание (не отражено в документации).
Если после установки отчет «Rules» пустой (правила отбора счетчиков производительности), то вам необходимо в скрипте DatabaseScripts/ConfigDMVstatsDB.sql заменить имена для вставки в таблицу DMVconfig.include_perf_counters. Например, вместо 'SQLServer:Locks ' необходимо указать 'MSSQL$SQL2005:Locks ', где SQL2005 – имя вашего SQL instance. Полное правильное имя вы можете подсмотреть в представлении sys.dm_os_performance_counters. Там же, по аналогии, можно добавить в таблицу DMVconfig.include_perf_counters дополнительно нужные вам счетчики производительности из представления sys.dm_os_performance_counters. После этого, повторно выполните исправленный SQL скрипт.

Общие впечатления
Проект распространяется с документацией, содержит удобные скрипты для установки и конфигурации. Использование MS SQL Reporting Services позволяет анализировать данные удаленно через веб-сайт и использовать встроенные возможности автоматического построения и рассылок отчетов по расписанию. Минусом считаю то, что не все отчеты удобны, некоторые очень долго формируются. Используется довольно запутанная архитектура с объединением множества снимков данных в так называемые наборы (Baselines), при этом фильтры на эти наборы в отчетах не устанавливаются.

Пример отчета «Analyze Block Info».



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

Miracle SQLSTAT2005

SQL SERVER 2005 performance data warehouse
Адрес проекта на CodePlex: http://sqlstat2005.codeplex.com

Проект SQLSTAT2005 также имеет свою базу данных, которая наполняется данными DMV и DMF по расписанию задания SQL Server Agent. Для анализа данных используются отчеты для SQL Management Studio.

Возможности текущей реализации:

• Сбор и сохранение данных DMV и DMF в своей базе данных, настройка расписания и количества запрашиваемых данных.
• Отчеты по выполнению отдельных запросов и пакетов запросов (информация о запросе, его текст, план, информация о вводе-выводе).
• Отчеты об использовании ресурсов процессора, памяти и дисков.
• Отчеты поддерживают сверление (drill-down), т.е. из пакета запросов можно посмотреть все запросы, а у конкретных запросов уже посмотреть информацию о времени выполнения, используемом плане, операциях ввода-вывода.
• Поддерживаются все версии SQL Server 2005, включая Express.

Установка и конфигурация
Инструкция по установке, конфигурации и использованию: Miracle SQLSTAT2005.docx
Если кратко, то просто восстанавливаем базу данных из архива SQLSTAT2005_v1.bak, создаем ручками задание SQL Server Agent и добавляем в SQL Server Management Studio стартовый отчет Reports/SQLSTAT2005_StartHERE.rdl из архива.

Добавить Custom Report вы сможете в версии SQL 2005 не ниже SP2.

Процесс добавления отчетов.


Пример одного из отчетов «SQLSTAT2005 Performance (Statement)». Он показывает 30 самых долгих запросов.


Общие впечатления
Структура хранилища почти один в один повторяет таблицы DMV, добавлен лишь префикс для таблиц и несколько служебных колонок (идентификатор снимка данных и количество захватываемых записей). Это удобно при написании дополнительных запросов к этой базе данных. Данные из DMF, такие как тексты и планы запросов также сохраняются в соответствующих таблицах. Простой принцип работы с отчетами. При запуске стартового отчета необходимо выбрать начальный и конечный снимок данных из списка. После этого будут доступны все отчеты SQLSTAT2005 для выбранного периода времени. Отчеты устанавливаются локально для Management Studio, на удаленном сервере необходимо только создать базу данных хранилища и сконфигурировать задание сбора сведений для SQL Server Agent.

Дополнительная информация

Проекты DMVStats и SQLSTAT2005 облегчают сбор и анализ сведений, представляемый SQL Server через DMV и DMF. Если их сравнивать, то с одной стороны DMVStats – легче ставится, настраивается и содержит больше готовых отчетов, но не развивается в настоящее время. С другой стороны, SQLSTATS2005 – сделан проще и находится в развитии. Если разработка и поддержка SQLSTATS2005 будет продолжена, в будущем это будет более удобный инструмент для работы.

Кроме этих инструментов, вы сами можете использовать подход, применяемый в них. Это создание своих таблиц и задание, которое по расписанию собирает данные из DMV и DMF для хранения в ваших таблицах для последующего анализа. Для создания SQL запросов вы можете воспользоваться диаграммами, которые показывают отношения системных таблицы и административных представлений: SQL Server 2005 System Views Map и SQL Server 2008 System Views Map.

Хочу так же упомянуть еще одно решение: SQL Server 2005 Performance Dashboard Reports. Это расширение для SQL Server Management Studio, позволяющее строить отчеты на основе служебных данных SQL сервера. Отчеты работают напрямую с системными таблицами, представлениями и функциям.
Список отчетов из SQL Server 2005 Performance Dashboard Reports:

Performance Dashboard; Blocking; Buffer IO; Buffer Latch; Databases Overview; Expensive Queries; General Waits; Historical IO; Historical Waits; Latch Waits;

Missing Indexes; Missing Indexes — XML Showplan; Page Details; Plan Guide; Query Plan; Recent CPU Consumption; Requests Overview; Session Details; Sessions Overview; Traces;

Пример стартового отчета «Performance Dashboard» из набора SQL Server 2005 Performance Dashboard Reports.

Подробную информацию о «Performance Dashboard Reports» можно получить по ссылкам:
«Making the Most Out of the SQL Server 2005 Performance Dashboard» (Eng) – часть 1, часть 2, часть 3.

В этой статье я рассказал о существующих методах и инструментах для работы со служебными данными, которые предоставляет MS SQL Server 2005/2008.

1. Исходный код SQL запросов к статье «Открытие скрытых данных для оптимизации производительности приложений».
2. DMVStats — A SQL Server 2005 Dynamic Management View Performance Data Warehouse.
3. Miracle SQLSTAT2005 — SQL SERVER 2005 performance data warehouse.
4. SQL Server 2005 Performance Dashboard Reports