January 28, 2011 at 8:10 am
Hi,
I am seeing higher levels of blocking on an table that we UPDATE regularly.
I have used NOLOCK on static tables in the past to reduce SELECT locking.
Was wondering if there is away to reduce the impact of an UPDATE lock.
The UPDATE lock will be using the standard out of the box UPDATE method. Wondering if its maybe TABLE LOCKING and if it would be possible to ROWLOCK instead.
I will also examine reducing the impact of SELECT statements on this table too.
The table in question contains a single row per parent (about 30000 rows in total). The UPDATE is applied to several fields including date/time field. Effectively shows the last details about an object. History is stored in another location.
Thank you for any information.
Scott
January 28, 2011 at 8:35 am
Update will take the locks it needs.
You might look into isolation levels. Snapshot (couple of flavors here) isolation can really improve concurrency in some cases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2011 at 8:48 am
hi!
CHeck more executed queries for this table and check they are full optimized. Some bad queries can handle some locks and update statement may be affected. It is because SQL uses Read Committed Isolation Level as default and long running queries may help you to get more locks.
Another suggestion but your team 'd need to make a regression test is to change to Read Committed Snapshot Isolation Level. I encourage you to take a look at this Isolation Level to see if it works for you. Based on my experience, you can improve a lot your LOck problems if you can change it.
Best Regards,
Roy
January 29, 2011 at 10:09 am
Thank you for the reply. Will investigate SNAPSHOT ISOLATION. Spotted this article re how to enable: http://www.sqlservercentral.com/Forums/Topic711799-146-1.aspx. Will test first.
February 2, 2011 at 4:46 am
Seems my biggest issues are wait locks of the following type:
LCK_M_U (waiting to acquire update lock)
LCK_M_S (waiting to acquire shared lock)
On a single table (parent).
PARENT
|
DATA
Parent is update with refresh overwrite data prior to complete row save in data table.
My understanding of SNAPSHOT ISOLATION LEVELS is that everything is loaded in to memory. Is this correct ?
Trouble is my db is 1TB and im worried about the impact. Working on getting a testing environment that can support this load but not available yet.
February 2, 2011 at 6:48 am
http://www.mssqltips.com/tip.asp?tip=1081
Running:
ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON
so far on R&D has taken 60mins.
Database in question use CLR and functions, added NOLOCK to select queries a while back but bottleneck has caught up. I dont understand exactly how CLR works (loaded in DLL ref to db when installing) but its looks like it could be a bottleneck.
QUEST spotlight WAIT STATISTIC shows "WAIT (LOCK)" and "WAIT (CLR)" are 2 large areas of waiting , LOCK tab displays MyDB objects (functions i think) as a source of locking.
When WCF service that uses it is offline, problem dissipates.
1) Not sure if SNAPSHOT isolation will help CLR issues ?
2) also looking at apps to load this DB into mem , not sure is this will help CLR issues ether ?
February 3, 2011 at 9:10 am
variety of things could be at play here. I HIGHLY recommmend you engage a performance tuning consultant for a day or two to dig into what is really going on. Proper indexing and/or not holding open read locks more than necessary are common culprits here.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 4, 2011 at 12:44 am
Thank you for the reply. Apologies , i had 2 issues that turned out to be the same problem and two threads crossed. Added an index to a table and the issue was resolved. Agree consultancy could be helpful , we have used our ISPs sql experts in the past.
February 4, 2011 at 3:06 pm
Ahh, the old "add an index to avoid a table-scan update" solution! Good job! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply