DMV для начинающих

Пересказ статьи Monica Rathbun. DMV’s for the Beginner

Я замечаю, что некоторые администраторы баз данных и разработчики никогда не обращают внимания на динамические административные представления (DMV), которые доступны в SQL Server, начиная с версии 2005. Эти представления предоставляют полезные средства для определения того, что происходит с конкретным экземпляром SQL Server. Итак, в этой статье я собираюсь перечислить несколько моих любимых DMV как справку для тех, кто может быть незнаком с ними.

Настройка производительности

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

sys.dm_exec_sessions – возвращает информацию о текущей сессии, такую как идентификатор сессии, информацию о логине — имя, хост, сведения о клиенте. Оно также возвращает некоторую информацию о производительности, например, использование памяти, времени процессора, число чтений/записи и множество других полезных сведений.

SELECT login_name ,COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;

sys.dm_exec_connections — дает информацию о сессиях текущих подключений к SQL Server. Она содержит время начала подключения, последнего чтения, последней записи, сетевой адрес клиента, последний sql_handle (уникальный токен, который идентифицирует SQL-пакет) и другую информацию, относящуюся к сетевому подключению.

SELECT c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme, s.host_name, s.program_name, 
s.client_interface_name, s.login_name, s.nt_domain, s.nt_user_name, s.original_login_name, c.connect_time,
s.login_time
FROM sys.dm_exec_connections AS c 
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id 
WHERE c.session_id = @@SPID -- @@SPID возвращает SPID вашей текущей сессии

sys.dm_exec_requests — это представление я часто использую при поиске блокировок, и когда мне недоступна sp_whosactive. Оно замечательно при анализе блокировок, времени ожидания и наблюдении за выполняемыми в текущее время запросами на вашем SQL Server. Оно предоставляет типы ожиданий, процент выполнения операций backup\restore\DBCC Checkdb, информацию о базе данных и сессии; конкретизирует тип выполняемых операторов — SELECT, INSERT, UPDATE, DELETE и т.д., а также дает информацию о плане выполнения с чтением/записью и использования ресурсов.

USE master
GO
SELECT * FROM sys.dm_exec_requests
WHERE session_id = 54;
GO

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

USE master
GO
SELECT * FROM sys.dm_os_wait_stats
GO

sys.dm_os_performance_counters — всегда удобно взглянуть на ОС через SQL Server. С его помощью вы можете получить счетчики монитора производительности, захваченные SQL Server. Это не заменяет монитор производительности для детального анализа производительности, однако включает такие вещи, как количество буферов, блокировки, события роста файлов, счетчики постолбцового хранения, процент попаданий в кэш,…, т.е. множество полезного в одном флаконе.

USE master
GO
SELECT * FROM sys.dm_os_performance_counters
GO

Настройка запроса

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

sys.dm_exec_query_stats — самое важное. Оно возвращает суммарную статистику о производительности для планов запроса, которые в настоящее время находятся кэше планов. Представление дает статистику, привязанную к отдельному плану, в частности, чтения, запись, общее число строк, время, необходимое для выполнения, хэш плана, информацию о выделении памяти, потоках, статистику поколоночного хранения, сливы и степень параллелизма (DOP, которая была добавлена в SQL Server 2016).

SELECT TOP 5 query_stats.query_hash AS "Query Hash",
 SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
 MIN(query_stats.statement_text) AS "Statement Text" 
 FROM (SELECT QS.*, 
 SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
 ((CASE statement_end_offset
  WHEN -1 THEN DATALENGTH(ST.text)
 ELSE QS.statement_end_offset END
 - QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS 
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST) AS query_stats
GROUP BY query_stats.query_hash 
ORDER BY 2 DESC;

sys.dm_exec_query_plan — это представление дает вам фактический план выполнения в формате XML.

USE master
GO
SELECT *FROM sys.dm_exec_query_plan (your plan handle goes here)
GO

sys.dm_exec_cached_plans — это планы, которые доступны в кэше для повторного использования. Вы можете использовать представление для получения хэндла плана, необходимого для того, чтобы получить фактический план из sys.dm_exec_query_plan. Здесь вы также можете увидеть использование счетчика — сколько раз этот план применялся, находясь в кэше.

USE master; 
GO 
SELECT *FROM sys.dm_exec_cached_plans AS cpCROSS APPLY sys.dm_exec_query_plan(cp.plan_handle); 
GO

sys.dm_exec_sql_text — возвращает фактический текст запроса в плане выполнения и относится к динамическим административным функциям, а не к представлениям.

-- запрос sql_handle
 
SELECT sql_handle FROM sys.dm_exec_requests WHERE session_id = 59  
-- замените это значение вашим фактическим spid
 
-- используйте sql_handle как параметр в sys.dm_exec_sql_text
 
SELECT * 
FROM sys.dm_exec_sql_text (здесь должен быть ваш хэндл)

Индексы

sys.dm_db_index_usage_stats — когда вы захотите узнать, используется ли на самом деле ваш индекс. Это представление скажет вам обо всех сканированиях, поисках и записях, сделанных с вашим индексом в последний раз, когда он использовался.

USE master
GO
SELECT * FROM sys.dm_db_index_usage_stats

sys.dm_db_missing_index_details — даст вам список всех отсутствующих индексов на вашем SQL Server. Это DMV не следует рассматривать как список индексов для добавления, т.к. это создаст много потенциально перекрывающихся индексов. Однако данное представление дает хорошую стартовую точку для необходимой индексации сервера.

USE master
GO
SELECT * FROM sys.dm_db_missing_index_details

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

Добавить комментарий