February 8, 2008 at 5:09 am
I have an MS Agent job that runs every 30 mins and looks at any processes engaged in a blocking chain and logs the details into a table in a hierarchal order so I can see the piece of code that is causing the blocks and the other pieces of code that are affected by the blocking action so I can then try to resolve any blocking action that is problematic.
This has worked well as it has enabled me to improve numerous pieces of code by breaking the offenders into smaller batches etc. However today when I looked at the history table I can see a blocking chain that seems to be between two unrelated pieces of code.
The blocker is a stored proc that takes the last days worth of banner hit/view data from one table, collates it and then moves it to the historical table.
The piece of code that is being affected by this is another stored proc that handles an application being saved on the web site. The tables contained within each procedure are not linked in anyway and not referenced by the other proc.
So I am wondering why this has been logged. Is there something happening behind the scenes that is the link between both procs that is causing the block (e.g tempDB) or is the code thats logging the blocking chain a bit wonky and recording the wrong action e.g was it the process before the one that was logged that was actually being affected etc.
The code that does the logging is below:
DECLARE @DatabaseName nvarchar(255)
SELECT @DatabaseName = 'MYDB'
DECLARE @PROCESSES TABLE(SPID int, blockingSPID int, databaseName nvarchar(255), programName nvarchar(500), loginName nvarchar(255), ObjectName nvarchar(max), Definition nvarchar(max))
INSERT INTO @PROCESSES
SELECTs.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid,s.dbid),
Definition = CAST(text AS VARCHAR(MAX))
FROMsys.sysprocesses s
CROSS APPLY
sys.dm_exec_sql_text (sql_handle)
WHEREs.spid > 50 AND
1 = CASE
WHEN @DatabaseName IS NULL AND s.dbid = db_id() THEN 1
WHEN @DatabaseName = 'ALL' THEN 1
WHEN COALESCE(@DatabaseName,'')<>'' AND DB_NAME(s.dbid) LIKE @DatabaseName + '%' THEN 1
END
;WITH Blocking(SPID, BlockingSPID, DatabaseName, BlockingStatement, RowNo, LevelRow)
AS
(
SELECT
s.SPID, s.BlockingSPID, s.DatabaseName, s.Definition,
ROW_NUMBER() OVER(ORDER BY s.SPID),
0 AS LevelRow
FROM
@PROCESSES s
JOIN @PROCESSES s1 ON s.SPID = s1.BlockingSPID
WHERE
s.BlockingSPID = 0
UNION ALL
SELECT
r.SPID, r.BlockingSPID, r.DatabaseName, r.Definition,
d.RowNo,
d.LevelRow + 1
FROM
@PROCESSES r
JOIN Blocking d ON r.BlockingSPID = d.SPID
WHERE
r.BlockingSPID > 0
)
SELECT * FROM Blocking
ORDER BY RowNo, LevelRow
Everything else thats been reported so far has been spot on and I can see the link and correct any code that is causing blocks that then cause timeout errors.
Thanks for any help
February 14, 2008 at 10:44 am
check for triggers
check for tempdb locking (such as creating temp tables with long running queries doing a select ... into #table....)
February 14, 2008 at 11:11 am
What were they blocking on?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2008 at 4:33 am
could you in fact be recording two seperate blocking chains occurring at the same time?
---------------------------------------------------------------------
February 15, 2008 at 4:59 am
I don't think so as the output of the CTE is an adjaceny table where the process causing the block
has a blockingSPID = 0. Then all the processes affected by this are underneath and have the BlockingSPID = 172 (or whatever the SPID of the blocking process is)
I haven't had the same processes reported since I posted the original post a week or so back now.
The processes (stored procedures) that conflicted were:
-proc to transfer the banner hits from one table to another.
This used a temporary table variable to hold the data whilst it was being collated and then
small batches of transactions whilst it copied the data from one table to the other and deleted the same records from the original table.
-proc to save an application.
this uses no temp tables/vars or transactions.
It does an if exists statement to see whether the applicant has already tried applying to the job and failed. if not it does an insert into the applications table and then inserts into 2 other tables depending on a sites settings. There is no transaction as even if the other inserts fail the application still needs to be recorded.
Maybe it was a temporary glitch in the matrix as it hasn't reoccured and I haven't seen any other odd behaviour. Usually its pretty obvious why one process is blocking the others.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply