Aracılığıyla paylaş


Hangi sorguların kilit tutan belirlemek

Veritabanı yöneticileri genellikle veritabanı performansını engelleyen kilitleri kaynağı tanımlamak gerekir.

Örneğin, sunucunuz üzerinde bir performans sorunu engelleyerek neden olabilir şüpheli. Ne zaman sen sorgu sys.dm_exec_requests, kilidi bekledi kaynağı olduğunu belirten bekleme türü ile askıya alınmış modunda birkaç oturum bulmak.

Sen sorgu sys.dm_tran_locksve sonuçları orada birçok kilitleri bekleyen, ancak bu kilitleri verilmiş oturumları gösterilmesini etkin istekler var mı sys.dm_exec_requests.

Bu örnek, kilitleme, sorgu planı ne sorgu aldı belirleme yöntemi gösterir ve Transact-SQLyığın kilidi götürüldü zaman. Bu örnek ayrıca eşleşme hedef Genişletilmiş olayları oturumu içinde nasıl kullanıldığını gösterir.

Bu görevi yerine getirmeye içerir sorgu Düzenleyici kullanarak SQL Server Management Studioaşağıdaki yordamı gerçekleştirmek için.

[!NOT]

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

Hangi sorguların kilit tutan belirlemek için

  1. Sorgu Düzenleyicisi'nde aşağıdaki deyimleri çıkış.

    -- 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. Sunucu üzerindeki iş yükünü yürütme sonrasında, yine kilit tutan sorguları bulmak için sorgu Düzenleyicisi'nde aşağıdaki deyimleri çıkış.

    --
    -- 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ı belirledikten sonra herhangi bir geçici tablolar ve olay oturumu açılır.

    DROP TABLE #unmatched_locks
    DROP EVENT SESSION FindBlockers ON SERVER
    

Ayrıca bkz.

Başvuru

OLAY SESSION (Transact-sql) oluştur

alter olay SESSION (Transact-sql)

drop olay SESSION (Transact-sql)

sys.dm_xe_session_targets (Transact-sql)

sys.dm_xe_sessions (Transact-sql)