+CAST(bt.SPID AS NVARCHAR (10)) ,spr.lastwaittype ,spr.loginame ,st. + RIGHT (CAST ((1000 + blc.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) ,blc.batchįROM #Blocks blc INNER JOIN BlockingTree bt ON blc.blocked = bt.SPID WHERE blc.blocked > 0 AND blc.blocked blc.SPID) WITH BlockingTree(spid,blocking_spid,batch)ĪS (SELECT blc.spid,blc.blocked,CAST(REPLICATE('0', 4-LEN(CAST (blc.spid AS VARCHAR)))+CAST(blc.spid AS VARCHAR) AS VARCHAR(1000)) ,blc.batchįROM #Blocks blc WHERE (blc.blocked = 0 OR blc.blocked = SPID) AND EXISTS (SELECT * FROM #Blocks blc2 WHERE blc2.BLOCKED = blc.SPID AND blc2.BLOCKED blc2.SPID) SELECT spid,blocked,REPLACE(REPLACE(st.TEXT, CHAR(10),' '),CHAR(13),' ') batch INTO #BlocksįROM sys.sysprocesses spr CROSS APPLY sys.dm_exec_sql_text(spr.SQL_HANDLE) st IF OBJECT_ID('tempdb.#Blocks') IS NOT NULL DROP TABLE #Blocks Here is another option that provides more info. Thank you very much for sharing this info. The wait resource is the last value in this screenshot showing the text TAB: Turned into a much more human-readable value. Traces, deadlock reports, and blocked process reports.Īt first glance, waitresource values can seem cryptic, but they can be easily These values can be found in sys.sysprocesses, sys.dm_exec_requests, sp_whoisactive, Table, row, or other object being locked and preventing it from moving forward. This "waitresource" is a text value indicating the specific file, This column returns the resource for which the request is currently waiting." Microsoft's online documentation states, "If the request is currently blocked, Same time, a "waitresource" will be defined. Wait type such as "LCK_M_IS" or "LCK_M_U". When a SQL Server process is being blocked, it will go to an "LCK" This tip will focus on determining what file, table, row, or other object is The first steps is to figure out what object is being locked. If that happens, the DBA needs to determine why this is happening. It is only an issue when locksĪre held for a long time, making a noticeable impact on end-user experiences. This means thatīlocking isn't inherently a bad thing. The software could not guarantee the consistency of the data. Without locking potential subsequent blocking, Locking and blocking are a natural part of any relational database management
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |