Статья подготовлена для студентов курса «MS SQL Server разработчик» в образовательном проекте OTUS.
Dynamic Management Views — специализированные представления и довольно часто функции, которые существуют в MS SQL Server и значительно облегчают администрирование и мониторинг SQL Server. Зачем они разработчику:
— если нужно автоматизировать административные задачи;
— если мы автоматизируем сбор нестандартных метрик для мониторинга.
Исторически сложилось, что сначала это были, в основном, представления, а уже потом добавились табличные функции, поэтому так и осталось название DMV.
Итак, давайте знакомиться:
1.Sys.dm_exec_cached_plans возвращает закэшированные планы запросов. Очень полезное представление, чтобы посмотреть, например, варианты планов, которые строятся на различные выборки. Также позволяет проверить, сколько места в памяти занимают планы и насколько они одинаковы для разных запросов.
2.Sys.dm_exec_query_stats показывает суммарную статистику выполнения по планам запросов. Именно используя это DMV, вы можете собрать статистику самых ресурсоёмких запросов на сервере и начать оптимизацию именно с них.
3.Sys.dm_tran_locks хранит информацию о блокировках, которые в данный момент наложены на объекты в БД, покажет тип блокировки и заблокированный объект, а также уровень изоляции транзакции. При этом, если вы хотите собрать историю блокировок за какой-либо период, нужно будет вызывать периодически DMV и сохранять результаты, так как при снятии блокировки данные об этой блокировке перестанут отображаться.
4.Sys.dm_db_missing_index_. Набор DMV, который показывает, каких индексов не хватает в БД по мнению SQL Server. Осторожно, не стоит создавать все индексы из списка! Во-первых, SQL Server никак не анализирует уже существующие и, если на таблице есть индекс из 2-х полей [id, name], а сервер считает, что нужен индекс по [id, name, type], то он может только предложить создать новый, а не модифицировать существующий. Во-вторых, в предложения часто попадает длинный список INCLUDE полей, что далеко не всегда является хорошей практикой, так как предложенный индекс дублирует всю таблицу.
5.Sys.dm_db_index_physical_stats покажет, сколько места занимают ваши индексы, а также уровень их фрагментации. Возможно, пора запустить процедуры обслуживания на некоторых индексах? Высокая фрагментация может быть причиной, по которой оптимизатор предпочтёт не использовать индекс, хотя по набору полей он может идеально подходить под запрос.
6.Sys.dm_io_pending_io_requests — список запросов на данный момент, которые висят в состоянии ожидания системы ввода-вывода. Можно обратить внимание на тот же уровень фрагментации, а также на объём возвращаемых данных.
7.Sys.dm_os_performance_counters отображает счётчики производительности SQL Server. Выборка покажет сведения о том, насколько эффективно работает кэш, как часто приходится вычитывать данные с диска и многое-многое другое.
Обзор DMV получился весьма кратким, но я надеюсь, что вы познакомитесь с заинтересовавшими вас представлениями, и они помогут глубже понять причины проблем с производительностью, а также вычислить неидеально написанные запросы.
Если вы работали с базами данных, писали или читали простые запросы, знаете типы данных и основные операторы на базовом уровне – то наш курс для вас!