July 18, 2007 at 1:37 pm
I have s stored procedure that runs every fifteen minutes that performs some inserts, updates and deletes on two order tables.
About once or twice a day when this procedure runs we will have some blocking issues. I have the WITH(NOLOCK) query hint in the query but that does not seem to help.
I am not extremely familiar with isolation levels, but could I maybe use the SNAPSHOT isolation level to prevent this from happening.
Any help is greatly appreciated
Thanks
July 18, 2007 at 11:49 pm
NoLock only applies on selects. SQL will take locks for updates/inserts/deletes.
Check the indexes on the tables, make sure they're not fragmented.
Could you post the proc here with an indication of what tables have blocking issues?
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
July 19, 2007 at 2:20 am
Snapshot isolation level would help and will not cause blocking.
But again there is a resource overhead associated with Snapshot isolation level and that is the tempdb,
Snapshot isolation level uses tempdb a lot.
However a better option to be to use READ COMMITED SNAPSHOT instead of SNAPSHOT Isolation due to the following reasons.
From BOL : Choosing Row Versioning-based Isolation Levels
http://msdn2.microsoft.com/en-us/library/ms188277.aspx
1> It consumes less tempdb space than snapshot isolation.
2> It works with distributed transactions, whereas snapshot isolation does not.
3> It works with most existing applications without requiring any change. Applications written using the default isolation level, read committed, can be dynamically tuned. The behavior of read committed, whether to use row versioning or not, is determined by the database option setting, and this can be changed without affecting the application
Thanks
Sumit
MCDBA, MCTS (SQL Server 2005)
July 19, 2007 at 2:56 am
Just making changes without understanding the cause can be dangerous :- consider:-
Your car braking has degraded, someone suggests new pads - you do this but sadly this isn't the problem ; your brakes fail and you die in an accident taking some innocent bystanders with you. OK a very harsh example, but you don't just change something without analysing what the problem is/was in the first place - and , please don't take offence at this, but posters who like to add titles to their signature, should know better.
Buy a tool to monitor, say SQL diagnostic manager, or run profiler to capture the circumstance under which you have your problems, take to a test system and reproduce - when you can reproduce the problem on demand you can start to look at how to fix it.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 19, 2007 at 2:13 pm
Here is the stored procedure that is causing the blocking. When I look at the process Id in Activity monitor the command is always DELETE. Does that mean that is the statement that it is running at the time it locks up.
UPDATE
UPDATE_openorders SET orddt = orddt + 19000000 WHERE orddt < 1000000
UPDATE UPDATE_openorders SET orddt = (orddt-1000000)+20000000 WHERE orddt < 10000000
UPDATE UPDATE_openorders SET prmdt = prmdt + 19000000 WHERE prmdt < 1000000
UPDATE UPDATE_openorders SET prmdt = (prmdt-1000000)+20000000 WHERE prmdt < 10000000
UPDATE UPDATE_openorders SET reqdt = reqdt + 19000000 WHERE reqdt < 1000000
UPDATE UPDATE_openorders SET reqdt = (reqdt-1000000)+20000000 WHERE reqdt < 10000000
UPDATE UPDATE_openorderdetail SET orddt = orddt + 19000000 WHERE orddt < 1000000
UPDATE UPDATE_openorderdetail SET orddt = (orddt-1000000)+20000000 WHERE orddt < 10000000
UPDATE UPDATE_openorderdetail SET prmdt = prmdt + 19000000 WHERE prmdt < 1000000
UPDATE UPDATE_openorderdetail SET prmdt = (prmdt-1000000)+20000000 WHERE prmdt < 10000000
UPDATE UPDATE_openorderdetail SET reqdt = reqdt + 19000000 WHERE reqdt < 1000000
UPDATE UPDATE_openorderdetail SET reqdt = (reqdt-1000000)+20000000 WHERE reqdt < 10000000
--Update the Orders
Update [OPENORDERS]
Set ORDSTS=b.ORDSTS,CusNum=b.Cusnum,SLDNAM=b.SLDNAM,SLDAD1=b.SLDAD1,SLDAD2=b.SLDAD2,SLDAD3=b.SLDAD3,
SLDCTY
=b.SLDCTY,SLDST=b.SLDST,SLDZP=b.SLDZP,SHPNAM=b.SHPNAM,SHPAD1=b.SHPAD1,SHPAD2=b.SHPAD2,SHPAD3=b.SHPAD3,
SHPCTY
=b.SHPCTY,SHPST=b.SHPST,SHPZP=b.SHPZP,ORDAMT=b.ORDAMT,REGN=b.REGN,SLSMN=b.SLSMN,SHPVIA=b.SHPVIA,
PONUM
=b.PONUM,ORDDT=b.ORDDT,PRMDT=b.PRMDT,CLSDT=b.CLSDT,REQDT=b.REQDT,SHIPINST=b.SHIPINST,PRIORITY=b.PRIORITY,HOUSE=b.HOUSE
From [OPENORDERS] a WITH(NOLOCK) join [UPDATE_OPENORDERS] b on a.OrdNum = b.OrdNum
Update [OPENORDERDETAIL]
Set ORDNUM = b.ORDNUM,LINESEQ = b.LINESEQ,LINSTS = b.LINSTS,ITMNUM = b.ITMNUM,ITMDSC = b.ITMDSC,ORDQTY = b.ORDQTY,
SHPQTY
= b.SHPQTY,UNTPRC = b.UNTPRC,ORDDT = b.ORDDT,PRMDT = b.PRMDT,INVNUM = b.INVNUM,INVDAT = b.INVDAT,
REQDT
= b.REQDT,MO = b.MO,HOUSE = b.HOUSE,CONTRACT=b.CONTRACT
From [OPENORDERDETAIL] a WITH(NOLOCK) join [UPDATE_OPENORDERDETAIL] b on a.OrdNum = b.OrdNum and a.LineSeq = b.LineSeq
--Add new records
Insert Into [OPENORDERS]
Select * from UPDATE_OPENORDERS
Where OrdNum not in (Select Ordnum From OPENORDERS)
Insert Into [OPENORDERDETAIL]
Select * from UPDATE_OPENORDERDETAIL
Where OrdNum + '-' + Convert(Varchar,LINESEQ) not in (Select Ordnum + '-' + Convert(Varchar,LINESEQ) From OPENORDERDETAIL)
--Remove Deleted Orders
Delete From [OPENORDERS]
Where OrdNum Not In (Select Ordnum From UPDATE_OPENORDERS)
Delete From [OPENORDERDETAIL]
Where OrdNum + '-' + Convert(Varchar,LINESEQ) Not In (Select Ordnum + '-' + Convert(Varchar,LINESEQ) From UPDATE_OPENORDERDETAIL)
Thanks
July 19, 2007 at 2:17 pm
if this runs as a sp won't it run step by step under one spid? if you want check my post history, i posted my check blocking script a few days ago. pretty easy to monitor it without spending money on third party tools.
if you see the spid being blocked by it's own spid it's not real blocking but it's just waiting to finish. i would also do an execution plan on each part to make sure it's efficient and all index seeks
July 19, 2007 at 11:54 pm
Which table is the blocking on? You'll be able to see that from the wait_resource if you query sys.dm_exec_requests or from the resource_associated_entity_id in sys.dm_tran_locks.
Based on what you said, and the code, if I had to take a guess, I'd say that it is the following statement that's causeing the bocking
DELETE .... Where OrdNum + '-' + Convert(Varchar,LINESEQ) Not In (Select Ordnum + '-' + Convert(Varchar,LINESEQ) From UPDATE_OPENORDERDETAIL)
Simple reason is that the where clause in that query cannot use any indexes (because of the function you've got there) and will table scan, locking the entire table exclusivly.
I would recomend the following change:
Where
NOT EXISTS (SELECT 1 FROM UPDATE_OPENORDERDETAIL ood
WHERE ood.OrdNum = OPENORDERDETAIL.OrdNum AND ood.LineSeq = OPENORDERDETAIL.LineSeq)
That is SARGable and won't force a table scan, assumiing, of course, that there is an index on either OrdNum, LineSeq or, preferably, both.
p.s. I use this very simple script to find blocking connections and what they're blocking.
SELECT
er.session_id, blocking_session_id, wait_type, wait_resource, database_id, login_time, login_name, command, st.text
FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE blocking_session_id >0
UNION ALL
SELECT er.session_id, blocking_session_id, wait_type, wait_resource, database_id, login_time, login_name, command, st.text
FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE er.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id >0)
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply