Aracılığıyla paylaş

  • Makale

Nasıl Yapılır: Hangi sorgular, kilit tutan belirler.

Veritabanı yöneticilerinin çoğunlukla veritabanı performansını hindering kilitleri kaynağı tanımlamak gerekir.

Örneğin, sunucunuz üzerinde bir performans sorunu engelleyerek kaynaklanabilir şüpheleniyorsanız.The sys.dm_exec_requests sorguladığınızda, birkaç oturumların askıya alınmış bir modda kilit bekledi kaynak olduğunu gösteren bir bekleme türüyle bulduğunuz.

Sys.dm_tran_locks ve birçok kilitlerin ödenmemiş olduğundan sonuçlar program'ı sorgulayabilir, ancak sys.dm_exec_requests gösteren herhangi bir etkin istekleri kilitlerin verilmiş olan oturumlara sahip değilsiniz.

Bu örnek, Sorgu planını kilit, hangi sorgu sürdü belirleme yöntem gösterir ve Transact-SQL kilit alındığı sırada yığılır. Bu örnek, ayrıca bir olaylar genişletilmiş oturumda eşleşme hedef tarafından nasıl kullanıldığını gösterir.

Bu görevi yerine getirmeye Query Düzenleyicisi'nde kullanarak içerir SQL Server Management Studio Aşağıdaki yordamı gerçekleştirmek için .

Not

Bu örnek, AdventureWorks veritabanındaki kullanır.

Hangi sorgular kilit tutan olmadığını belirlemek için

  1. Query Düzenleyicisi'nde, aşağıdaki ifadeyi girin.

    -- Perform cleanup. 
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='FindBlockers')
    DROP EVENT SESSION FindBlockers ON SERVER
GO
-- Use dynamic SQL to create the event session and allow creating a -- predicate on the AdventureWorks database id.
--
DECLARE @dbid int

SELECT @dbid = db_id('AdventureWorks')

IF @dbid IS NULL
BEGIN
    RAISERROR('AdventureWorks is not installed. Install AdventureWorks before proceeding', 17, 1)
    RETURN
END

DECLARE @sql nvarchar(1024)
SET @sql = '
CREATE EVENT SESSION FindBlockers ON SERVER
ADD EVENT sqlserver.lock_acquired 
    (action 
        ( sqlserver.sql_text, sqlserver.database_id, sqlserver.tsql_stack,
         sqlserver.plan_handle, sqlserver.session_id)
    WHERE ( database_id=' + cast(@dbid as nvarchar) + ' AND resource_0!=0) 
    ),
ADD EVENT sqlserver.lock_released 
    (WHERE ( database_id=' + cast(@dbid as nvarchar) + ' AND resource_0!=0 ))
ADD TARGET package0.pair_matching 
    ( SET begin_event=''sqlserver.lock_acquired'', 
            begin_matching_columns=''database_id, resource_0, resource_1, resource_2, transaction_id, mode'', 
            end_event=''sqlserver.lock_released'', 
            end_matching_columns=''database_id, resource_0, resource_1, resource_2, transaction_id, mode'',
    respond_to_memory_pressure=1)
WITH (max_dispatch_latency = 1 seconds)'

EXEC (@sql)
-- 
-- Create the metadata for the event session
-- Start the event session
--
ALTER EVENT SESSION FindBlockers ON SERVER
STATE = START

  2. Bir iş yükü sunucu yürütme sonrasında, aşağıdaki ifadeleri sorgularda hala kilitli tutan bulmak için Query Düzenleyicisi'nde sorun.

    --
-- The pair matching targets report current unpaired events using 
-- the sys.dm_xe_session_targets dynamic management view (DMV)
-- in XML format.
-- The following query retrieves the data from the DMV and stores
-- key data in a temporary table to speed subsequent access and
-- retrieval.
--
SELECT 
objlocks.value('(action/value)[5]', 'int')
        AS session_id,
    objlocks.value('(data/value)[5]', 'int') 
        AS database_id,
    objlocks.value('(data/text)[1]', 'nvarchar(50)' ) 
        AS resource_type,
    objlocks.value('(data/value)[9]', 'bigint') 
        AS resource_0,
    objlocks.value('(data/value)[10]', 'bigint') 
        AS resource_1,
    objlocks.value('(data/value)[11]', 'bigint') 
        AS resource_2,
    objlocks.value('(data/text)[2]', 'nvarchar(50)') 
        AS mode,
    objlocks.value('(action/value)[1]', 'varchar(MAX)') 
        AS sql_text,
    CAST(objlocks.value('(action/value)[4]', 'varchar(MAX)') AS xml) 
        AS plan_handle,    
    CAST(objlocks.value('(action/value)[3]', 'varchar(MAX)') AS xml) 
        AS tsql_stack
INTO #unmatched_locks
FROM (
    SELECT CAST(xest.target_data as xml) 
        lockinfo
    FROM sys.dm_xe_session_targets xest
    JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
    WHERE xest.target_name = 'pair_matching' AND xes.name = 'FindBlockers'
) heldlocks
CROSS APPLY lockinfo.nodes('//event[@name="lock_acquired"]') AS T(objlocks)
--
-- Join the data acquired from the pairing target with other 
-- DMVs to return provide additional information about blockers
--
SELECT ul.*
    FROM #unmatched_locks ul
    INNER JOIN sys.dm_tran_locks tl ON ul.database_id = tl.resource_database_id AND ul.resource_type = tl.resource_type
    WHERE resource_0 IS NOT NULL
    AND session_id IN 
        (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0)
    AND tl.request_status='wait'
    AND REPLACE(ul.mode, 'LCK_M_', '' ) = tl.request_mode

  3. Sorunları tanımlayan sonra herhangi bir geçici tablo ve olay oturum açılır.

    DROP TABLE #unmatched_locks
DROP EVENT SESSION FindBlockers ON SERVER