Hi All,
I have a basic question. I run SP_Who2 to check blocking and know that when we see SID in the BlkBY column then it is blocking..not sure if I am missing anything. Is it a good idea to look into additional things to confirm blocking except waiting for query to be executed....then what that would be?
However I sometimes cannot understand which query is blocking which one and how can I explain that to someone or kill ( after checking with the user or only if it is dev env maybe) the process ID. Do I have to use OPENTRAN(SID) to see the query which is blocking or is there a better way. How to efficiently handle blocking. Is it the code fix which is the permanent remedy. Please advise.
Also, what would you do if killing the process is not an option when you see blocking?
Thank you!
November 5, 2019 at 2:36 pm
right click on the server and use activity monitor - order it by the head blocker column - you should be able to see a better picture of what is going on
if you have lots of blocking then you have 3 options - look at code quality and try and get transaction times down, .... look at missing indexes....and think about nolock
as for not having the option to kill a process... just explain to the business the implications and let them decide.
this is a huge topic, so it would be better if you could give us examples of blocks of code that are affected - then we can help more specifically
MVDBA
There are a number of good scripts on the internet that will list the blocking chain in order and also tell you the blocking header query. Here's one I use regularly. I can't remember where I got it, but I know i didn't write it originally.
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
You've asked a lot of questions there:
No, there's typically no permanent fix. Blocking is one of the ways SQL manages consistent reads. There are work rounds and ways to mitigate/reduce the blocking but they have pros and cons. Investigate using Read Committed Snapshot Isolation, but you need to test this.
Ideally you want to have queries running as quickly as possible. Keep long running queries out of business hours.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 5, 2019 at 10:42 pm
Thanks!!!, I ran the script for test scenario and below are the results..
BLOCKING_TREE
HEAD - 71 USE AdventureWorks2012 BEGIN TRANSACTION --rollback --commit SELECT * FROM [dbo].[DatabaseLog] WITH (TABLOCK, XLOCK, HOLDLOCK)
| |------ 67 SELECT * FROM [dbo].[DatabaseLog]
So can we say SID 67 was blocking SID71?
November 6, 2019 at 8:48 am
other way round... almost looks like someone forgot to commit the transaction - and with those lock hints....ouch
MVDBA
November 6, 2019 at 7:25 pm
As has been said, the other way around. The "HEAD" is the problem, so SPID 71 is blocking 67.
Also each extra indent indicates another layer of blocking. In a busy system you may see SPID 71 (HEAD) blocking multiple other threads, then see one of the blocked threads blocking other threads.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 6, 2019 at 7:43 pm
Great. Thanks!!
Now, at least I am ready with clear answers when I see blocking.
Obviously, fixing the root cause is deep. I work in an environment where there is lot of blocking.
So, working to fix the code, make necessary changes etc.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply