Come trovare ciò che sta bloccando i miei tavoli

Ho una tabella SQL che all’improvviso non può restituire dati a meno che non includa “con (nolock)” alla fine, che indica un qualche tipo di blocco lasciato sul mio tavolo. Ho sperimentato un po ‘con dm_tran_locks per identificare che ci sono in effetti un certo numero di blocchi sul tavolo, ma come faccio a identificare cosa li sta bloccando (cioè l’elemento request dei dm_tran_locks)?

EDIT: So di sp_lock per pre SQL 2005, ma ora che sp è deprecato, AFAIK il modo giusto per farlo è con dm_tran_locks. Sto usando SQL Server 2008 R2.

Dai un’occhiata alle seguenti stored procedure di sistema, che puoi eseguire in SQLServer Management Studio (SSMS):

  • sp_who
  • sp_lock

Inoltre, in SSMS, puoi visualizzare blocchi e processi in diversi modi:

inserisci la descrizione dell'immagine qui

Versioni diverse di SSMS mettono il monitor delle attività in luoghi diversi. Ad esempio, SSMS 2008 e 2012 sono disponibili nel menu di scelta rapida quando si fa clic con il pulsante destro del mouse su un nodo del server.

Per andare direttamente a “chi è bloccato / bloccato” ho combinato / abbreviato sp_who e sp_lock in una singola query che offre una buona panoramica di chi ha quale object bloccato a quale livello.

--Create Procedure WhoLock --AS if object_id('tempdb..#locksummary') is not null Drop table #locksummary if object_id('tempdb..#lock') is not null Drop table #lock create table #lock ( spid int, dbid int, objId int, indId int, Type char(4), resource nchar(32), Mode char(8), status char(6)) Insert into #lock exec sp_lock if object_id('tempdb..#who') is not null Drop table #who create table #who ( spid int, ecid int, status char(30), loginame char(128), hostname char(128), blk char(5), dbname char(128), cmd char(16) -- , request_id INT --Needed for SQL 2008 onwards -- ) Insert into #who exec sp_who Print '-----------------------------------------' Print 'Lock Summary for ' + @@servername + ' (excluding tempdb):' Print '-----------------------------------------' + Char(10) Select left(loginame, 28) as loginame, left(db_name(dbid),128) as DB, left(object_name(objID),30) as object, max(mode) as [ToLevel], Count(*) as [How Many], Max(Case When mode= 'X' Then cmd Else null End) as [Xclusive lock for command], l.spid, hostname into #LockSummary from #lock l join #who w on l.spid= w.spid where dbID != db_id('tempdb') and l.status='GRANT' group by dbID, objID, l.spid, hostname, loginame Select * from #LockSummary order by [ToLevel] Desc, [How Many] Desc, loginame, DB, object Print '--------' Print 'Who is blocking:' Print '--------' + char(10) SELECT p.spid ,convert(char(12), d.name) db_name , program_name , p.loginame , convert(char(12), hostname) hostname , cmd , p.status , p.blocked , login_time , last_batch , p.spid FROM master..sysprocesses p JOIN master..sysdatabases d ON p.dbid = d.dbid WHERE EXISTS ( SELECT 1 FROM master..sysprocesses p2 WHERE p2.blocked = p.spid ) Print '--------' Print 'Details:' Print '--------' + char(10) Select left(loginame, 30) as loginame, l.spid, left(db_name(dbid),15) as DB, left(object_name(objID),40) as object, mode , blk, l.status from #lock l join #who w on l.spid= w.spid where dbID != db_id('tempdb') and blk <>0 Order by mode desc, blk, loginame, dbID, objID, l.status 

(Per cosa significano le abbreviazioni del livello di blocco, consultare ad esempio https://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx )

Copiato da: sp_WhoLock – un processo memorizzato T-SQL che combina sp_who e sp_lock …

NB la colonna [Xclusive lock for command] può essere fuorviante – mostra il comando corrente per quel spid; ma il blocco X potrebbe essere stato triggersto da un comando precedente nella transazione.

Ho una procedura memorizzata che ho messo insieme, che tratta non solo di blocchi e blocchi, ma anche di vedere cosa è in esecuzione in un server. L’ho messo nel master. Lo condividerò con te, il codice è qui sotto:

 USE [master] go CREATE PROCEDURE [dbo].[sp_radhe] AS BEGIN SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- the current_processes -- marcelo miorelli -- CCHQ -- 04 MAR 2013 Wednesday SELECT es.session_id AS session_id ,COALESCE(es.original_login_name, '') AS login_name ,COALESCE(es.host_name,'') AS hostname ,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch ,es.status ,COALESCE(er.blocking_session_id,0) AS blocked_by ,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype ,COALESCE(er.wait_time,0) AS waittime ,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype ,COALESCE(er.wait_resource,'') AS waitresource ,coalesce(db_name(er.database_id),'No Info') as dbid ,COALESCE(er.command,'AWAITING COMMAND') AS cmd ,sql_text=st.text ,transaction_isolation = CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,COALESCE(es.cpu_time,0) + COALESCE(er.cpu_time,0) AS cpu ,COALESCE(es.reads,0) + COALESCE(es.writes,0) + COALESCE(er.reads,0) + COALESCE(er.writes,0) AS physical_io ,COALESCE(er.open_transaction_count,-1) AS open_tran ,COALESCE(es.program_name,'') AS program_name ,es.login_time FROM sys.dm_exec_sessions es LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st where es.is_user_process = 1 and es.session_id <> @@spid and es.status = 'running' ORDER BY es.session_id end GO 

questa procedura mi ha fatto molto bene negli ultimi due anni. per eseguirlo basta digitare sp_radhe

Per quanto riguarda l’inserimento di sp_radhe nel database master

Io uso il seguente codice e ne faccio una stored procedure di sistema

 exec sys.sp_MS_marksystemobject 'sp_radhe' 

come puoi vedere sul link qui sotto

Creazione di stored procedure per il proprio sistema SQL Server

Riguardo al livello di isolamento della transazione

Domande sui livelli di isolamento delle transazioni T-SQL che avevi troppo da chiedere

Jonathan Kehayias

Una volta modificato il livello di isolamento della transazione, cambia solo quando l’oscilloscopio esce alla fine della procedura o una chiamata di ritorno, oppure se lo si cambia di nuovo esplicitamente utilizzando SET TRANSACTION ISOLATION LEVEL.

Inoltre, il LIVELLO DI ISOLAMENTO DELLA TRANSAZIONE è limitato solo alla stored procedure, pertanto è ansible avere più stored procedure nidificate che vengono eseguite a livelli di isolamento specifici.

 exec sp_lock 

Questa query dovrebbe fornire i blocchi esistenti.

 exec sp_who SPID -- will give you some info 

Avendo spids, puoi controllare il monitor delle attività (scheda dei processi) per scoprire quali processi stanno bloccando le tabelle (“dettagli” per maggiori informazioni e “kill process” per ucciderlo).

Puoi anche usare sp_who2 che fornisce maggiori informazioni

Ecco alcune informazioni http://dbadiaries.com/using-sp_who2-to-help-with-sql-server-troubleshooting