Individuare le query che mantengono attivi i blocchi
Si applica a: 
SQL Server, Database SQL di Azure
Gli amministratori di database hanno spesso la necessità di individuare l'origine dei blocchi che hanno effetti negativi sulle prestazioni del database.
Si supponga ad esempio di ritenere che un blocco sia la causa di un problema di prestazioni nel server. Quando si esegue una query su sys.dm_exec_requests, vengono individuate diverse sessioni in modalità di sospensione con un tipo di attesa che indica che la risorsa in attesa è rappresentata da un blocco.
Nei risultati dell'esecuzione di una query su sys.dm_tran_locks viene indicato che sono presenti numerosi blocchi in sospeso, ma che per le sessioni a cui sono stati concessi i blocchi non è disponibile alcuna richiesta attiva mostrata in sys.dm_exec_requests.
In questo esempio viene illustrato un metodo che consente di individuare la query che ha attivato il blocco, il piano della query e lo stack Transact-SQL nel momento in cui è stato attivato il blocco. Nell'esempio viene inoltre illustrato l'utilizzo della destinazione di abbinamento in una sessione di Eventi estesi.
Il completamento di tale attività comporta l'utilizzo dell'editor di query in SQL SQL Server Management Studio per effettuare la procedura descritta di seguito.
Nota
In questo esempio viene utilizzato il database AdventureWorks.
Per individuare le query che mantengono attivi i blocchi
Nell'editor di query eseguire le istruzioni indicate di seguito.
-- 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 = STARTDopo l'esecuzione di un carico di lavoro nel server, nell'editor di query eseguire le istruzioni indicate di seguito per individuare le query che mantengono ancora attivi i blocchi.
-- -- 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_modeDopo avere identificato i problemi, eliminare qualsiasi tabella temporanea e la sessione dell'evento.
DROP TABLE #unmatched_locks DROP EVENT SESSION FindBlockers ON SERVER
Nota
Gli esempi di codice Transact-SQL precedenti vengono eseguiti in SQL Server locale, ma potrebbero non essere eseguiti in database SQL di Azure. Anche le parti della memoria centrale dell'esempio che coinvolgono direttamente eventi, come ADD EVENT sqlserver.lock_acquired funzionano anche su database SQL di Azure. Tuttavia, gli elementi preliminari, ad esempio sys.server_event_sessions, devono essere modificati nelle relative controparti del database SQL di Azure, ad esempio sys.database_event_sessions per l'esecuzione dell'esempio.
Per altre informazioni su queste differenze minime tra SQL Server locale e il database SQL di Azure, vedere gli articoli seguenti:
Vedi anche
CREARE LA SESSIONE DI EVENTI (Transact-SQL)
ALTER EVENT SESSION (Transact-SQL)
DROP EVENT SESSION (Transact-SQL)
sys.dm_xe_session_targets (Transact-SQL)
sys.dm_xe_sessions (Transact-SQL)