Мониторинг производительности

База данных для мониторинга производительности Microsoft SQL Server:

Для развития решения присылайте (minyurov@mail.ru) собственные методики по анализу и оптимизации производительности и способы применения данного решения на практике.


perf-mon-cycle Оптимизация работы сервера, базы данных и выполнения запросов — это сложная и трудоемкая работа, которая должна выполняться постоянно.

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

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

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

Очевидным показателем производительности является время выполнения запроса. Но это, на самом деле, условный критерий, поскольку на него влияют множество факторов, которые сложно контролировать. Простым и объективным показателем является то, какая работа выполняется системой при выполнении запроса: прежде всего объем обработанных данных (ввод-вывод). И основной задачей оптимизации является именно минимизация нагрузки на систему и ее балансировка — равномерная нагрузка на аппаратные ресурсы.

Часто проблемы с производительностью имеют эффект «домино» — важно найти первопричину и устранить именно ее, а не симптомы.

sql-perf-dilemma Для бизнес-приложений важна производительность СУБД прежде всего для обработки транзакций. Если мы хотим на этой же базе данных выполнять сложный анализ данных, например, делать отчеты, то у нас возникает конфликт оптимизации: при повышении производительности обработки транзакций может ухудшаться анализ данных и наоборот.

Microsoft SQL Server имеет множество средств диагностики, которые могут генерировать большие объемы информации. С помощью динамических административных объектов (DMV/DMF) можно достаточно просто получить картинку в целом, либо очень детальную, касающуюся конкретного запроса или объекта базы данных.

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

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

perf-mon-reg При мониторинге производительности важно собирать комплексную информацию о рабочей нагрузке и конфигурации, чтобы лучше понимать тенденции и факторы, влияющие на работу нашей системы. Анализируя эту информацию мы оцениваем производительность и выявляем проблемы.

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

На основе профессиональных знаний сообщества, доступных на форумах, блогах и в профессиональной литературе, я разрабатываю решение для исследования конфигурации и нагрузки на Microsoft SQL Server (Database Engine). Это решение реализовано в виде базы данных, содержащей в себе таблицы, запросы и хранимые процедуры для сбора и хранения диагностической информации (см. следующий рисунок), а также средства для обнаружения и решения стандартных проблем.

mssql-perf-areas

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

perf-mon-logic Разрабатывается специализированное приложение для визуализации диагностических данных.

На основе формализованных знаний обеспечивается их интеллектуальный анализ и объяснение полученной информации.

Это решение имеет открытую архитектуру и позволяет добавлять новые методы по диагностике и настройке работы СУБД.

Дорожная картаbaseline-road-map

Вебинар Архитектура и производительность Microsoft SQL Server

Библиография

  1. SQL Server Performance Survival Guide
  2. Expert performance indexing for SQL Server 2012. Jason Strate, Ted Krueger.
  3. Inside the SQL Server Query Optimizer. Benjamin Nevarez.
  4. Microsoft SQL Server 2012 Performance Tuning Cookbook. Ritesh Shah, Bihag Thaker.
  5. Optimizing Fill-factors for SQL Server. Ken Lassesen.
  6. Performance Tuning with SQL Server Dynamic Management Views. Louis Davidson, Tim Ford.
  7. Plan Caching and Recompilation in SQL Server 2012. Greg Low.
  8. Pro SQL Server internals. Dmitri Korotkevitch
  9. Pro SQL Server Wait Statistics. Enrico van de Laar.
  10. SQL Server DMVs in Action. Better queries with dynamic management views. Ian W. Stirk.
  11. SQL Server Statistics. Holger Schmeling.
  12. SQL Server Query Performance Tuning. Grant Fritchey.
  13. SQL Server Concurrency. Locking, Blocking and Row Versioning. Kalen Delaney.