SQL-Ex blog

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

Эти динамические административные представления то, что я в первую очередь использую, когда наблюдаю проблемы с производительностью или мне нужно узнать, что происходит прямо сейчас на моем экземпляре 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 cp

CROSS 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.