- During normal processing in a database, it is blocked. Blocking is “by design” because your database is on ACID.
- In an OLTP database, most of your transactions are less in the duration, so we expect that blocking will also be less in the period. If it is blocked for a long time, then it can cause problems.
- When blocking goes bad, it can go really bad sometimes it can actually get worse. Sometimes this happens because someone forgets to commit the transaction and and those open locks cause a bunch of blocking.
- sometimes a data load runs on a strange time, or an unusual amount of data is loaded, or a query becomes a bad plan and starts running for a long time. There are a bunch of reasons it can come.
Below scripts identify blocking in SQL Server with much more information:-
use master go SELECT SPID,ER.percent_complete, CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,DATEADD(second,estimated_completion_time/1000, getdate()) as est_completion_time, ER.command,ER.blocking_session_id, SP.DBID,LASTWAITTYPE,DB_NAME(SP.DBID) AS DBNAME, SUBSTRING(est.text, (ER.statement_start_offset/2)+1,((CASE ER.statement_end_offset WHEN -1 THEN DATALENGTH(est.text) ELSE ER.statement_end_offset END - ER.statement_start_offset)/2) + 1) AS QueryText, TEXT,CPU,HOSTNAME,LOGIN_TIME,LOGINAME,SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME FROM SYSPROCESSES SP INNER JOIN sys.dm_exec_requests ER ON sp.spid = ER.session_id CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
After Execution of above script you get the output like below:-
Lead Blocker In SQL Server:-
we shown in above output and find that we will have blocking But sometimes you get a query or two blocked for long enough to cause a problem. Even more rarely you end up with a long chain of blocked sessions. Session 60,63 are blocked by 57 which is blocked by 58 and so on.It can be very difficult to scan through all of those blocked sessions in production server to find the root cause.that’s why we have to finds a lead blocker quickly in the blocking chain and provides useful information on it.
below Scripts quickly finds a lead blocker in the blocking chain and provides some useful information:-
--Author -Pinal Dave-------
SET NOCOUNT ON
GO
SELECT SPID, BLOCKED,
REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T FROM sys.sysprocesses R
CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS(SELECT SPID,BLOCKED,CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR)))
+ CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID
AND R2.BLOCKED <> R2.SPID)UNION ALL
SELECT R.SPID,R.BLOCKED,CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4)
AS VARCHAR (1000)) AS LEVEL,R.BATCH FROM #T AS R INNER JOIN BLOCKERS
ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID)
SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) +CASE WHEN (LEN(LEVEL)/4 - 1) = 0
THEN 'HEAD - ' ELSE '|------ '
END+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH
AS BLOCKING_TREE FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO
After Execution of above script you get the output like below:-