Tentukan Kueri Mana yang Menahan Kunci

Berlaku untuk: SQL ServerAzure SQL Database

Administrator database sering kali perlu mengidentifikasi sumber kunci yang menghambat performa database.

Misalnya, Anda menduga bahwa masalah performa di server Anda dapat disebabkan oleh pemblokiran. Saat Anda mengkueri sys.dm_exec_requests, Anda menemukan beberapa sesi dalam mode ditangguhkan dengan jenis tunggu yang menunjukkan bahwa kunci adalah sumber daya yang sedang ditunggu.

Anda meminta sys.dm_tran_locks dan hasilnya menunjukkan bahwa ada banyak kunci yang luar biasa, tetapi sesi yang diberikan kunci tidak memiliki permintaan aktif yang ditampilkan di sys.dm_exec_requests.

Contoh ini menunjukkan metode untuk menentukan kueri apa yang mengambil kunci, rencana kueri, dan tumpukan Transact-SQL pada saat kunci diambil. Contoh ini juga menggambarkan bagaimana target pemasangan digunakan dalam sesi Extended Events.

Menyelesaikan tugas ini melibatkan penggunaan Editor Kueri di SQL Server Management Studio untuk melakukan prosedur berikut.

Catatan

Contoh ini menggunakan database AdventureWorks.

Untuk menentukan kueri mana yang menahan kunci

1. Dalam Editor Kueri, terbitkan pernyataan berikut.

   -- 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. Setelah eksekusi beban kerja di server, terbitkan pernyataan berikut di Editor Kueri untuk menemukan kueri yang masih memegang kunci.

   --  
   -- 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[@name="session_id"]/value)[1]', 'int')  
           AS session_id,  
       objlocks.value('(data[@name="database_id"]/value)[1]', 'int')   
           AS database_id,  
       objlocks.value('(data[@name="resource_type"]/text)[1]', 'nvarchar(50)' )   
           AS resource_type,  
       objlocks.value('(data[@name="resource_0"]/value)[1]', 'bigint')   
           AS resource_0,  
       objlocks.value('(data[@name="resource_1"]/value)[1]', 'bigint')   
           AS resource_1,  
       objlocks.value('(data[@name="resource_2"]/value)[1]', 'bigint')   
           AS resource_2,  
       objlocks.value('(data[@name="mode"]/text)[1]', 'nvarchar(50)')   
           AS mode,  
       objlocks.value('(action[@name="sql_text"]/value)[1]', 'varchar(MAX)')   
           AS sql_text,  
       CAST(objlocks.value('(action[@name="plan_handle"]/value)[1]', 'varchar(MAX)') AS xml)   
           AS plan_handle,      
       CAST(objlocks.value('(action[@name="tsql_stack"]/value)[1]', '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. Setelah mengidentifikasi masalah, hilangkan tabel sementara dan sesi peristiwa.

   DROP TABLE #unmatched_locks  
   DROP EVENT SESSION FindBlockers ON SERVER  
   

Catatan

Contoh kode Transact-SQL sebelumnya berjalan di SQL Server lokal, tetapi mungkin tidak cukup berjalan di Azure SQL Database. Bagian inti dari contoh yang secara langsung melibatkan Peristiwa, seperti ADD EVENT sqlserver.lock_acquired melakukan pekerjaan di Azure SQL Database juga. Tetapi item awal, seperti sys.server_event_sessions harus diedit ke mitra Azure SQL Database mereka seperti sys.database_event_sessions contoh untuk dijalankan. Untuk informasi selengkapnya tentang perbedaan kecil antara SQL Server lokal versus Azure SQL Database, lihat artikel berikut ini:

• Extended events di Azure SQL Database
• Objek sistem yang mendukung peristiwa yang diperluas

Lihat Juga

CREATE EVENT SESSION (T-SQL)
MENGUBAH SESI PERISTIWA (Transact-SQL)
HILANGKAN SESI PERISTIWA (T-SQL)
sys.dm_xe_session_targets (Transact-SQL)
sys.dm_xe_sessions (T-SQL)