This article will give complete insight on blocking, how you can manually create a blocking, how to find the root cause, how to set alert and how to fix it.
Blocking in SQL:
Blocking in SQL Server is very dangerous as it can heavily impact the performance. Not only that; it will create angry Clients along with numerous escalation emails.
To fix SQL SERVER blocking from growing bigger, we have to immediately identify the root blocker and take the needed steps.
Script to send an Alert email:
The below TSQL script will help you to quickly jump on blocking and fix it rather than someone informing to you through escalation emails. It sends an Alert Email with following info
- Key Blocking Session ID
- Count of other transactions in the queue
- Total blocking time
; WITH T1 ( csid ,cstime ,cstats ,cbid ,cdb ,ccmd ,ctxt ) AS ( SELECT session_id ,start_time ,STATUS ,blocking_session_id ,Db_name(database_id) ,command ,sql_text = Cast(TEXT AS VARCHAR(max)) FROM sys.dm_exec_requests WITH (NOLOCK) CROSS APPLY sys.Dm_exec_sql_text(sql_handle) WHERE STATUS <> 'Background' ) ,T2 ( csid ,cstime ,cstats ,cbid ,ccmd ,ctxt ,cnum ,clvl ) AS ( SELECT r1.csid ,r1.cstime ,r1.cstats ,r1.cbid ,r1.ccmd ,r1.ctxt ,Row_number() OVER ( ORDER BY r1.csid ) ,0 AS LevelRow FROM T1 r1 INNER JOIN T1 r2 ON r1.csid = r2.cbid WHERE r1.cbid = 0 UNION ALL SELECT r3.csid ,r3.cstime ,r3.cstats ,r3.cbid ,r3.ccmd ,r3.ctxt ,b.cnum ,b.clvl + 1 FROM T1 r3 INNER JOIN T2 b ON r3.cbid = b.csid WHERE r3.cbid > 0 ) SELECT * INTO ###table FROM T2 ORDER BY cnum ,clvl -- select * from ###table DECLARE @id INTEGER ,@cmd VARCHAR(max) ,@ftime DATETIME ,@tcount INTEGER ,@mins INTEGER SET @id = ( SELECT TOP 1 csid FROM ###table ) SET @ftime = ( SELECT TOP 1 cstime FROM ###table ) SET @cmd = ( SELECT TOP 1 ccmd FROM ###table ) SET @tcount = ( SELECT count(*) FROM ###table ) SET @mins = datediff(mi, @ftime, getdate()) -- select @id, @ftime, @cmd, @tcount - 1, @mins DECLARE @message VARCHAR(1000) SET @message = 'Session ID : ' + cast(@id AS VARCHAR(10)) + ' is blocking ' + cast(@tcount AS VARCHAR(10)) + ' other transactions for ' + cast(@mins AS VARCHAR(10)) + ' minutes. Please check ASAP !' SELECT @message DROP TABLE ###table EXEC msdb.dbo.sp_send_dbmail @profile_name = '<SQL SERVER DBMAIL PROFILE NAME>' ,@recipients = '<RECIPIENT EMAIL ADDRESS. SEPARATE MULTIPLE EMAIL ADDRESS BY ;' ,@subject = 'Blocking' ,@body = @message
Steps involved while using this script:
Below is the sample Email that will be sent out by the script.
Soon as you see this email, the first step that you have to do is to connect to the SQL Server instance. Next you must analyze the sysprocesses table to quickly identify the login that is running this spid. After that you can contact the User and check if the spid can be killed. If yes, kill the spid using KILL Command and resolve the blocking.
Proactive testing of this script by creating a block on SQL Server:
Of course, you cannot wait for the blocking to happen on the production server to test this script. Creating blocking is easy. Try this on your Test server. Select some records from your test table by setting some lock on the table. Add a delay for the transaction to complete. While this is running from one query window, open another query window and try to select records from the same table. This time it is not necessary to set a lock.
BEGIN TRANSACTION SELECT * FROM <YOUR TEST DATABASE NAME>.<SCHEMA NAME>.<TABLE NAME> WITH (TABLOCKX, HOLDLOCK); WAITFOR DELAY '00:11:00' -- DURATION IN HH:MM:SS. ROLLBACK TRANSACTION
SELECT * FROM <YOUR TEST DATABASE NAME>.<SCHEMA NAME>.<TABLE NAME>
Points to remember if blocking happens often:
If blocking occurs often, check if index, statistics are proper on the target database. Share the problematic scripts/statements to the development team so that they can fine tune the query.
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook data problem and excel recovery software products. For more information visit www.datanumen.com