...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
SELECT r.session_id,
r.status,
r.start_time,
r.command,
r.total_elapsed_time,
r.cpu_time,
r.reads,
r.writes,
s.login_name,
s.host_name,
s.program_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
WHERE r.status <> 'background'
ORDER BY r.total_elapsed_time DESC; |
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
-- Включите ведение статистики для запросов EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'user connection statistics', 1; RECONFIGURE; |
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
SELECT TOP 100
qs.query_hash,
SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
(CASE statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS query_text,
qs.execution_count,
qs.total_elapsed_time,
qs.total_elapsed_time / qs.execution_count AS mean_time,
qs.max_elapsed_time,
qs.total_rows
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY total_elapsed_time DESC; |
- Сбор самых долгих (по среднему времени выполнения) запросов:
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
SELECT TOP 100
qs.query_hash,
SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
(CASE statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS query_text,
qs.execution_count,
qs.total_elapsed_time,
qs.total_elapsed_time / qs.execution_count AS mean_time,
qs.max_elapsed_time,
qs.total_rows
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY mean_time DESC; |
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
SELECT TOP 100
qs.query_hash,
SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
(CASE statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS query_text,
qs.execution_count,
qs.total_elapsed_time,
qs.total_elapsed_time / qs.execution_count AS mean_time,
qs.max_elapsed_time,
qs.total_rows
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY max_elapsed_time DESC; |
...