Автор: Paul Randal, Advanced SQL Server performance tuning
Всё это прекрасно — иметь навороченные сторонние инструменты мониторинга и диагностики производительности, но иногда вам приходится углубляться в происходящее внутри SQL Server дальше, чем могут зайти эти инструменты. Или вам приходится обращаться в службу поддержки клиентов или Premier Support, чтобы они могли углубиться.
Обычно вы или они будете использовать четыре динамических административных представления (DMV), которые дают всё более продвинутую информацию о происходящем для диагностики производительности:
sys.dm_os_wait_statssys.dm_os_waiting_taskssys.dm_os_latch_statssys.dm_os_spinlock_stats(этот вообще не документирован и упоминается лишь в нескольких местах в Интернете)
В этой статье я немного объясню эти DMV, уделив основное внимание защёлкам (кратким блокировкам) и спин-блокировкам.
sys.dm_os_wait_stats
Статистика ожиданий — это хлеб насущный настройки производительности. SQL Server отслеживает, какие ресурсы потокам приходится ожидать и как долго им приходится ждать. Анализируя, какие ресурсы (и комбинации ресурсов) ожидаются чаще всего, вы можете получить представление о том, с чего начать дальнейшее исследование. Например, если большинство ожиданий — это PAGEIOLATCH_SH, и в вашем базовом показателе статистики ожиданий этого не было, вы можете посмотреть на производительность подсистемы ввода-вывода с помощью DMV sys.dm_io_virtual_file_stats (о котором я писал в этой статье).
Как собирать статистику ожиданий можно почитать тут: Tips for DBA: sys.dm_os_wait_stats и правильный способ сбора статистики ожиданий
sys.dm_os_waiting_tasks
DMV sys.dm_os_waiting_tasks показывает, что в данный момент ожидается всем, что выполняется в системе.
Я создал сценарий с 200 клиентами, создающими и удаляющими небольшие временные таблицы, чтобы создать конкуренцию за защёлки tempdb. Используя DMV, я могу увидеть, что ожидается (я удалил столбцы, описывающие блокировки, из вывода, чтобы он поместился на экране):
SELECT * FROM sys.dm_os_waiting_tasks;
GO
waiting_task_address session_id exec_context_id wait_duration_ms wait_type resource_address resource_description
-------------------- ---------- --------------- -------------------- ------------------ ------------------ --------------------
0x000000000044E508 2 0 4091305 XE_DISPATCHER_WAIT NULL NULL
0x000000000044E988 9 0 4121252 FSAGENT NULL NULL
0x000000000044EBC8 20 0 4121251 BROKER_TRANSMITTER NULL NULL
0x000000000044F4C8 63 0 53 PAGELATCH_EX 0x0000000088FFEED8 2:1:1139
0x000000000044EE08 64 0 8 PAGELATCH_UP 0x0000000080FE8AD8 2:1:1
0x000000000044F288 87 0 0 PAGELATCH_UP 0x0000000080FE8AD8 2:1:1
0x000000000044F048 91 0 53 PAGELATCH_EX 0x0000000088FFEED8 2:1:1139
0x000000000044F948 92 0 61 PAGELATCH_EX 0x0000000088FFEED8 2:1:1139
0x000000000044F708 101 0 10 PAGELATCH_EX 0x0000000080FEEC58 2:1:120
0x000000000044FDC8 103 0 37 PAGELATCH_UP 0x0000000080FE8AD8 2:1:1
0x000000008744E088 118 0 11 PAGELATCH_EX 0x0000000080FEEC58 2:1:120
0x000000008744E2C8 121 0 66 PAGELATCH_UP 0x0000000080FE8AD8 2:1:1
0x000000008744E508 122 0 33 PAGELATCH_EX 0x0000000080FEEC58 2:1:120
0x000000008744E748 155 0 32 PAGELATCH_EX 0x0000000080FEEC58 2:1:120
0x000000008744E988 158 0 27 PAGELATCH_EX 0x0000000080FEEC58 2:1:120
0x000000008744EBC8 163 0 34 PAGELATCH_EX 0x0000000080FEEC58 2:1:120
0x000000008744EE08 168 0 66 PAGELATCH_UP 0x0000000080FE8AD8 2:1:1
0x000000008744F048 179 0 26 PAGELATCH_UP 0x0000000080FE8AD8 2:1:1
.Как вы можете видеть, классическая конкуренция за защёлки tempdb проявляется — ID страницы (2:1:1) — первая страница PFS в tempdb. Мой коллега Джо Сак (Joe Sack) создал скрипт, который извлекает данные из ряда других DMV, чтобы сделать вывод sys.dm_os_waiting_tasks более полезным, и я модифицировал его в следующий (обратите внимание, что 'text' в одной строке не имеет разделителей, потому что это нарушает работу плагина форматирования кода):
SELECT
[owt].[session_id],
[owt].[exec_context_id],
[ot].[scheduler_id],
[owt].[wait_duration_ms],
[owt].[wait_type],
[owt].[blocking_session_id],
[owt].[resource_description],
CASE [owt].[wait_type]
WHEN N'CXPACKET' THEN
RIGHT ([owt].[resource_description],
CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)
ELSE NULL
END AS [Node ID],
--[es].[program_name],
[est].text,
[er].[database_id],
[eqp].[query_plan],
[er].[cpu_time]
FROM sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_os_tasks [ot] ON
[owt].[waiting_task_address] = [ot].[task_address]
INNER JOIN sys.dm_exec_sessions [es] ON
[owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests [er] ON
[es].[session_id] = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE
[es].[is_user_process] = 1
ORDER BY
[owt].[session_id],
[owt].[exec_context_id];
GO
В выводе слишком много информации, чтобы полезно показать её в этом посте, но я вижу фактические выполняемые инструкции T-SQL (в данном случае много DROP TABLE и SELECT * INTO глобальных временных таблиц) и XML-планы запросов. Нажав на один из них в SSMS, я получаю фактический план — очень круто. Это означает, что я могу из DMV sys.dm_os_wait_stats узнать, какие ожидания ресурсов преобладают, затем с помощью DMV sys.dm_os_waiting_tasks увидеть, какие запросы ожидают эти ресурсы, — и затем углубиться, чтобы понять почему.
sys.dm_os_latch_stats
Защёлка (latch) — это легковесный механизм синхронизации, который защищает доступ к чтению и изменению структур в памяти. Например, 8-килобайтные буферы страниц в буферном пуле (класс защёлки = BUFFER) или структура данных, представляющая файлы данных и журналов базы данных (класс защёлки = FGCB_ADD_REMOVE). Защёлка удерживается только на время операции, в отличие от блокировки, которая может удерживаться до фиксации транзакции. Один из примеров блокировок и защёлок: представьте таблицу, где запрос обновления вызвал эскалацию блокировок, так что на таблице удерживается блокировка X (исключительная) на уровне таблицы. По мере продолжения обновления записей в таблице больше блокировок не приобретается, но любые страницы данных и индексов, обновляемые в памяти, должны быть захвачены в режиме EX (исключительном) перед выполнением обновления. Защёлка выступает в роли механизма синхронизации, предотвращающего одновременное обновление страницы двумя потоками или чтение страницы потоком в то время, как другой поток находится в процессе её обновления. Другой пример — если вы выполняете запрос SELECT с NOLOCK — хотя запрос не будет захватывать блокировки SH (разделяемые) на любом уровне таблицы, потоки должны захватывать защёлки SH на страницах перед их чтением — для синхронизации с возможными параллельными обновлениями.
Если потоку требуется защёлка, он переводится из состояния RUNNING в SUSPENDED и помещается в список ожидающих для уведомления о том, что защёлка была захвачена в запрошенном режиме.
Ожидания защёлок соответствуют ожиданиям LATCH_XX в выводе DMV sys.dm_os_wait_stats, поэтому изучение того, какие защёлки вызывают больше всего ожиданий, может показать узкое место в системе.
Вы можете сбросить статистику ожиданий защёлок так же, как и обычную статистику ожиданий, используя:
DBCC SQLPERF('sys.dm_os_latch_stats', CLEAR);
GO
Пример вывода из DMV:
SELECT * FROM sys.dm_os_latch_stats
WHERE [waiting_requests_count] > 0
ORDER BY [wait_time_ms] DESC;
GOlatch_class waiting_requests_count wait_time_ms max_wait_time_ms
--------------------------------- ---------------------- -------------------- --------------------
BUFFER 113181121 466697044 1233
ACCESS_METHODS_HOBT_COUNT 66676 331193 577
ACCESS_METHODS_HOBT_VIRTUAL_ROOT 15018 68865 125
LOG_MANAGER 130 5610 234
FGCB_ADD_REMOVE 299 5073 32
TRACE_CONTROLLER 1 0 0
VERSIONING_TRANSACTION_LIST 1 0 0
ACCESS_METHODS_HOBT_FACTORY 64 0 0Вы также можете агрегировать их, используя код ниже:
WITH [Latches] AS
(SELECT
[latch_class],
[wait_time_ms] / 1000.0 AS [WaitS],
[waiting_requests_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_latch_stats
WHERE [latch_class] NOT IN (
N'BUFFER')
--AND [wait_time_ms] > 0
)
SELECT
MAX ([W1].[latch_class]) AS [LatchClass],
CAST (MAX ([W1].[WaitS]) AS DECIMAL(14, 2)) AS [Wait_S],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL(14, 2)) AS [Percentage],
CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (14, 4)) AS [AvgWait_S]
FROM [Latches] AS [W1]
INNER JOIN [Latches] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- порог процента
GO
Вот пример после очистки статистики защёлок и выполнения теста на конкуренцию в tempdb в течение 30 секунд:
LatchClass Wait_S WaitCount Percentage AvgWait_S
--------------------------------- ------- ---------- ----------- ----------
ACCESS_METHODS_HOBT_COUNT 7.92 1471 75.41 0.0054
ACCESS_METHODS_HOBT_VIRTUAL_ROOT 1.38 393 13.15 0.0035
LOG_MANAGER 1.20 12 11.44 0.1001Большинство классов защёлок не документированы, но я буду проливать свет на них по мере того, как буду больше писать о статистике защёлок.
sys.dm_os_spinlock_stats
Спин-блокировка (spinlock) — это ещё один легковесный механизм синхронизации, используемый для управления доступом к определённым структурам данных в ядре, — используется, когда время удержания спин-блокировки очень короткое. Они отличаются от защёлок тем, что поток, ожидающий защёлку, уступает планировщик и переходит в список ожидающих, тогда как поток, ожидающий захвата спин-блокировки, будет тратить некоторое время процессора, «крутясь» (spinning), чтобы увидеть, сможет ли он захватить блокировку, прежде чем сдастся, «отступит» (уступит планировщик) и попробует снова. Это может позволить другому потоку выполниться, который удерживает спин-блокировку, и в конечном итоге освободить её, позволяя системе продолжить работу (да, поток может уступить планировщик и перейти в список ожидающих, удерживая спин-блокировку!), потому что другой поток затем сможет захватить спин-блокировку.
Совершенно нормально, что коллизии и «кручения» спин-блокировок происходят в загруженной системе, но иногда узкое место может возникнуть в системах с большим количеством процессоров, где коллизии более вероятны, — это может исчерпать ресурсы процессора, пока многие потоки «крутятся», пытаясь захватить спин-блокировку.
Выполнение DMV показывает вам список всех спин-блокировок в системе (все они не документированы, но я буду работать над этим в будущем) — вот частичный вывод:
SELECT * FROM sys.dm_os_spinlock_stats
ORDER BY [spins] DESC;
GO
name collisions spins spins_per_collision sleep_time backoffs
------------------ -------------------- -------------------- ------------------- -------------------- -----------
LOCK_HASH 3629624 4402099957 1212.825 561 817819
SOS_CACHESTORE 11992297 3352117666 279.5226 6093 71948
OPT_IDX_MISS_KEY 63329610 2036811058 32.16207 15830 180845
SOS_TLIST 9769744 574740437 58.82861 320 3619
SOS_SCHEDULER 2137875 107392996 50.23352 557 7753
MUTEX 676406 83493780 123.4374 340 3300
LOGCACHE_ACCESS 2210697 83204315 37.63714 0 252366
SOS_RW 264489 70122059 265.1228 14 799
XDESMGR 346005 61031449 176.3889 216 3788
SOS_SUSPEND_QUEUE 3397384 53752545 15.82174 120 2384
OPT_IDX_STATS 129814 19800952 152.5332 27 356
BACKUP_CTX 29730 16784471 564.5635 192 1645
LOCK_RESOURCE_ID 17558 4363116 248.4973 20 375
SOS_TASK 206597 1898063 9.187273 16 171
XVB_LIST 266112 882691 3.316991 1 63
.В 2005 году вам нужно было бы использовать DBCC SQLPERF ('spinlockstats') и использовать INSERT/EXEC, чтобы поместить результаты в таблицу. Эрик Хамфри (Eric Humphrey) (блог | twitter) собрал код:
IF OBJECT_ID (N'tempdb..#TempSpinlockStats1') IS NOT NULL
DROP TABLE [#TempSpinlockStats1];
GO
CREATE TABLE [#TempSpinlockStats1] (
[name] NVARCHAR(30) NOT NULL,
[collisions] BIGINT NOT NULL,
[spins] BIGINT NOT NULL,
[spins_per_collision] FLOAT NOT NULL,
[sleep_time] BIGINT NOT NULL,
[backoffs] BIGINT NOT NULL
);
INSERT INTO [#TempSpinlockStats1] EXEC ('DBCC SQLPERF(''spinlockstats'')');
GO
Спин-блокировка LOCK_HASH, например, используется менеджером блокировок для просмотра одной из хеш-корзин, содержащих хеши ресурсов блокировок, чтобы определить, может ли быть предоставлена блокировка.
sleep_time — это агрегация времени, затраченного на сон между циклами «кручения», когда происходит отступление (backoff).
Я собрал код, который позволяет увидеть, какая активность спин-блокировок происходит между двумя моментами времени. Код захватывает вывод из DMV в две временные таблицы с любым желаемым интервалом между ними (чтобы позволить вам выполнить команду), а затем показывает разницу между двумя наборами данных. Я покажу пример выполнения DBCC CHECKDB.
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] = N'##TempSpinlockStats1')
DROP TABLE [##TempSpinlockStats1];
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] = N'##TempSpinlockStats2')
DROP TABLE [##TempSpinlockStats2];
GO
-- Базовый уровень
SELECT * INTO [##TempSpinlockStats1]
FROM sys.dm_os_spinlock_stats
WHERE [collisions] > 0
ORDER BY [name];
GO
-- Теперь делаем что-то
DBCC CHECKDB (N'SalesDB') WITH NO_INFOMSGS;
GO
-- Захват обновлённой статистики
SELECT * INTO [##TempSpinlockStats2]
FROM sys.dm_os_spinlock_stats
WHERE [collisions] > 0
ORDER BY [name];
GO
-- Разница между ними
SELECT
'***' AS [New],
[ts2].[name] AS [Spinlock],
[ts2].[collisions] AS [DiffCollisions],
[ts2].[spins] AS [DiffSpins],
[ts2].[spins_per_collision] AS [SpinsPerCollision],
[ts2].[sleep_time] AS [DiffSleepTime],
[ts2].[backoffs] AS [DiffBackoffs]
FROM [##TempSpinlockStats2] [ts2]
LEFT OUTER JOIN [##TempSpinlockStats1] [ts1]
ON [ts2].[name] = [ts1].[name]
WHERE [ts1].[name] IS NULL
UNION
SELECT
'' AS [New],
[ts2].[name] AS [Spinlock],
[ts2].[collisions] - [ts1].[collisions] AS [DiffCollisions],
[ts2].[spins] - [ts1].[spins] AS [DiffSpins],
CASE ([ts2].[spins] - [ts1].[spins]) WHEN 0 THEN 0
ELSE ([ts2].[spins] - [ts1].[spins]) /
([ts2].[collisions] - [ts1].[collisions]) END
AS [SpinsPerCollision],
[ts2].[sleep_time] - [ts1].[sleep_time] AS [DiffSleepTime],
[ts2].[backoffs] - [ts1].[backoffs] AS [DiffBackoffs]
FROM [##TempSpinlockStats2] [ts2]
LEFT OUTER JOIN [##TempSpinlockStats1] [ts1]
ON [ts2].[name] = [ts1].[name]
WHERE [ts1].[name] IS NOT NULL
AND [ts2].[collisions] - [ts1].[collisions] > 0
ORDER BY [New] DESC, [Spinlock] ASC;
GO
И вывод выглядит следующим образом:
New Spinlock DiffCollisions DiffSpins SpinsPerCollision DiffSleepTime DiffBackoffs
---- ------------------ -------------------- -------------------- ----------------- -------------------- ------------
*** DBCC_CHECK 4 24 6 0 0
*** DIAG_MANAGER 1 0 0 0 0
*** FCB_REPLICA_SYNC 10 16147 1614.7 0 0
*** LSID 9 0 0 0 0
*** QUERYEXEC 5 0 0 0 0
*** X_PACKET_LIST 2 0 0 0 0
*** X_PORT 8 227 28.375 0 0
*** XACT_WORKSPACE 11 0 0 0 0
*** XID_ARRAY 7 0 0 0 0
BUF_FREE_LIST 2 0 0 0 0
HOBT_HASH 2 1 0 0 0
LOCK_HASH 3 1818 606 0 0
SOS_RW 2 500 250 0 0
SOS_SCHEDULER 5 6 1 0 0
SOS_SUSPEND_QUEUE 11 39 3 0 0
SOS_TASK 1 0 0 0 0
SOS_TLIST 1 0 0 0 0Здесь вы можете увидеть, какие спин-блокировки были захвачены для выполнения команд DBCC CHECKDB — отмеченные *** не появлялись в «начальном» наборе статистики спин-блокировок. Подробнее обо всём этом в будущих статьях.
Вы также можете исследовать спин-блокировки с помощью расширенных событий — опять же, об этом позже.
Резюме
Возможно глубокое погружение в происходящее внутри SQL Server с использованием этих четырёх DMV. В частности, спин-блокировки — что означает каждая из них, что она контролирует и что означает конкуренция за каждую из них (плюс что вы можете с этим сделать) — требуют большого знания того, что происходит внутри ядра, и я планирую распространять часть этих знаний в будущем — существует огромный объём информации, который необходимо опубликовать о защёлках и спин-блокировках.
Комментариев нет:
Отправить комментарий