Минимизация блокирования в SQL Server 2008 - Наблюдение за системой
ОГЛАВЛЕНИЕ
Наблюдение за системой
Воздействие блокировок на общую производительность системы может отслеживаться для блокировок путем опроса данных состояния через определенные промежутки времени (например, каждый чес) и записи статистики по удерживаемым блокировкам. Собираемая ключевая информация:
- затрагиваемый объект, грануляция и тип блокировки;
- длительность блокировки и блокирования;
- выполняемая команда SQL (имя хранимой процедуры, инструкция SQL);
- информация о цепочке блокировок, где применимо;
- использование системной доступной емкости блокировок.
Можно выполнить сценарий, наподобие показанного на рис. 3, для сбора этой информации, записывая ее в таблицу с соответствующей временной меткой. Для дальнейшего анализа идентификатора ResourceId of блокируемых данных можно выполнить сценарий, наподобие показанного на рис. 4.
Рис 3 Capturing locking stats
SELECT er.wait_time AS WaitMSQtyРис 4 Learning more about blocked data
, er.session_id AS CallingSpId
, LEFT(nt_user_name, 30) AS CallingUserName
, LEFT(ces.program_name, 40) AS CallingProgramName
, er.blocking_session_id AS BlockingSpId
, DB_NAME(er.database_id) AS DbName
, CAST(csql.text AS varchar(255)) AS CallingSQL
, clck.CallingResourceId
, clck.CallingResourceType
, clck.CallingRequestMode
, CAST(bsql.text AS varchar(255)) AS BlockingSQL
, blck.BlockingResourceType
, blck.BlockingRequestMode
FROM master.sys.dm_exec_requests er WITH (NOLOCK)
JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
ON er.session_id = ces.session_id
CROSS APPLY fn_get_sql (er.sql_handle) csql
JOIN (
-- Retrieve lock information for calling process, return only one record to
-- report information at the session level
SELECT cl.request_session_id AS CallingSpId
, MIN(cl.resource_associated_entity_id) AS CallingResourceId
, MIN(LEFT(cl.resource_type, 30)) AS CallingResourceType
, MIN(LEFT(cl.request_mode, 30)) AS CallingRequestMode
-- (i.e. schema, update, etc.)
FROM master.sys.dm_tran_locks cl WITH (nolock)
WHERE cl.request_status = 'WAIT' -- Status of the lock request = waiting
GROUP BY cl.request_session_id
) AS clck
ON er.session_id = clck.CallingSpid
JOIN (
-- Retrieve lock information for blocking process
-- Only one record will be returned (one possibility, for instance,
-- is for multiple row locks to occur)
SELECT bl.request_session_id AS BlockingSpId
, bl.resource_associated_entity_id AS BlockingResourceId
, MIN(LEFT(bl.resource_type, 30)) AS BlockingResourceType
, MIN(LEFT(bl.request_mode, 30)) AS BlockingRequestMode
FROM master.sys.dm_tran_locks bl WITH (nolock)
GROUP BY bl.request_session_id
, bl.resource_associated_entity_id
) AS blck
ON er.blocking_session_id = blck.BlockingSpId
AND clck.CallingResourceId = blck.BlockingResourceId
JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
ON er.blocking_session_id = ber.session_id
CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE ces.is_user_process = 1
AND er.wait_time > 0
DECLARE @SQL nvarchar(max)
, @CallingResourceType varchar(30)
, @Objectname sysname
, @DBName sysname
, @resource_associated_entity_id int
-- TODO: Set the variables for the object you wish to look up
SET @SQL = N'
USE ' + @DbName + N'
DECLARE @ObjectId int
SELECT @ObjectId = CASE
WHEN @CallingResourceType = ''OBJECT''
THEN @resource_associated_entity_id
WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
THEN (SELECT object_id
FROM sys.partitions
WHERE hobt_id = @resource_associated_entity_id)
WHEN @CallingResourceType = ''ALLOCATION_UNIT''
THEN (SELECT CASE
WHEN type IN (1, 3)
THEN (SELECT object_id
FROM sys.partitions
WHERE hobt_id = allocation_unit_id)
WHEN type = 2
THEN (SELECT object_id
FROM sys.partitions
WHERE partition_id = allocation_unit_id)
ELSE NULL
END
FROM sys.allocation_units
WHERE allocation_unit_id = @resource_associated_entity_id)
ELSE NULL
END
SELECT @ObjectName = OBJECT_NAME(@ObjectId)'
EXEC dbo.sp_executeSQL
@SQL
, N'@CallingResourceType varchar(30)
, @resource_associated_entity_id int
, @ObjectName sysname OUTPUT'
, @resource_associated_entity_id = @resource_associated_entity_id
, @CallingResourceType = @CallingResourceType
, @ObjectName = @ObjectName OUTPUT
Также можно отслеживать укрупнения в системе с помощью приложения SQL Profiler (событие Lock:Escalation), динамического административного представления dm_db_index_operational_stats (index_lock_promotion_count) или регулярного опроса информации о блокировках системы. Необходимые сведения, собираемые при отслеживании укрупнений, включают сведения о том, гарантирует ли обработка укрупнение; если укрупнение не гарантируются, соответствующие хранимые процедуры могут указать исходную причину проблем производительности. Таблица с данными большого объема или частым одновременным использованием должна быть основным объектом оценки.
После сбора данных о блокировках, блокированиях и укрупнениях они могут быть проанализированы для определения общего времени блокировки и блокирования (количество возникновений, умноженное на длительность) для каждого объекта. Обычно это может вызвать последовательный цикл настройки производительности, в котором выполняется внесение, отслеживание, анализ и исправление изменений. Иногда требуется только простое изменение, такое как добавление индекса для значительного увеличения производительности и изменения области в системе с наиболее сильным негативным влиянием на производительность.
Дополнительные сведения об уменьшении блокировки в SQL Server см. на боковой панели «Дополнительные материалы». При удалении особого внимания обеспечению небольшого размера транзакций на этапах проектирования, создания кода и стабилизации можно минимизировать множество проблем блокировки. Подходящее оборудование также может значительно уменьшить вероятность нежелательных укрупнений. В любом случае, текущая оценка блокирования в системе может быстро определить источники проблем производительности.
Автор: Чери Уоррен
Иcточник: TechNet Magazine
Опубликована - 17.04.2008