June 3, 2011 at 1:57 pm
GSquared (6/3/2011)
GilaMonster (6/3/2011)
GSquared (6/3/2011)
The original post asserted no pending DDL. Worth double-checking, of course.The Sch-S is the only lock a select with nolock takes, that's only blocked by an Sch-M. Need to check the blocking, check the lock and check the wait type.
If I remember correctly, snapshot isolation is Enterprise Edition only, so may not be an option (or might).
It's available in all editions, both snapshot and read-committed snapshot.
Yeah, looks like I'm crossing it up with some other feature on that one. Wasn't sure, when I wrote it, and just finished fact-checking myself.
Probably database snapshots, which are Enterprise only
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
June 3, 2011 at 2:08 pm
Yeah, that makes sense (on the snapshots bit).
On the block/lock issue, I tend to suspect there's either a missing NoLock hint, or something similar. I've seen lots of queries with NoLock on 7 of the 8 tables involved, and things like that. Or NoLock on a table used in an Update without understanding that that's ignored. That kind of thing. It's essentially a sloppy programming practice, and other sloppy habits are commonly included with it.
- 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
June 3, 2011 at 3:00 pm
wow.. thank you for all the repies! I learned a lot today!
Here are some facts
1) The select statement is pretty simple. Straight forward 'SELECT * FROM theTable (NOLOCK)' and that is the only select statment from the table.
2) Yes, there are triggers (insert,delete) in the table but they are all disabled
3) Blocking
SessionID Status OpenTransactions Blocking Count Wait Type Command
215 Runnable 1 1 Select
142 Suspended 2 0 LCK_M_IX Insert
June 3, 2011 at 3:05 pm
What's the blocking session ID? Are there any other operations in the transaction?
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
June 3, 2011 at 3:14 pm
The blocking session ID is 215 ( from the select statement). Session 142 was blocked and wait type is (LCK_M_IX). There are other processes running that has command 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED'. But basically no other process was using that table.
June 3, 2011 at 3:27 pm
Is there anything else running in that transaction (don't care about other processes)
Trace everything coming from the SSIS is you don't know.
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
June 3, 2011 at 3:41 pm
OK.. I run a trace when the SSIS runs and here is what I see.. Is that the CAUSE?
SET FMTONLY ON select * from the Table where 1=2 SET FMTONLY OFF
June 3, 2011 at 7:06 pm
I think I found out the real cause and hopefully, what I found out will make sense to you..
The SSIS package dose the following things
1) select from table A, which another process write to and the blocking happened
2) dose a lookup from table B
3) write data to table C
thanks for SQL Diagnostic Monitor, I can look back the blocking and find out the blocking session has all 3 steps. step (1,2,3) are within one session ( same session ID).
Step 1) use shared lock
2) use shared lock
3) use schema stability lock
SSIS process data through data pipeline and that is why step 1, 2 & 3 happens concurrently. Since step3 ask for Sch-S lock which make the (nolock) a victim.
These are just my theroy, please advise.
June 4, 2011 at 5:22 am
Er, no. A schema stability lock is compatible with every other lock, other than schema modification. All queries must take a schema stability lock to ensure the table isn't changing as they read. You said there's no DDL, hence there are no Sch-M locks, and besides the blocking was on an IX lock, not an Sch-S or Sch-M
Those shared locks that are being taken are the ones probably causing the blocking.
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
June 7, 2011 at 12:10 pm
Thank you for pointing it out! I was too excited to post and did not understand the lock used here:w00t:
I still don't have a clue of why the blocking happens but here is what I did to reduce the blocking
Change the 'select *..' statements in SSIS and only select columns needed
It has been two work days past since the changes and I have not seen any blocking happened. Now, some new questions for you
1) READ_COMMITTED_SNAPSHOT is set on the database level. Is there anyway I can set it just for select query? (I don't want to have dirty read from the query and I don't want to have 'READ_COMMITTED_SNAPSHOT' set on the whole database either)
2) The 3 steps I talked about ealier in the SSIS package are wrapped in a transaction. Dose it create more possiblities of blocking?
Thanks
June 7, 2011 at 12:37 pm
Read committed snapshot is set at a database level, but you can enable snapshot isolation and set that for a connection with the usual SET TRANSACTION ISOLATION LEVEL statement.
If you do that, take the nolock hints out. Otherwise they'll override the requested isolation level.
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
June 8, 2011 at 1:03 am
Use Row Versioning if you are facing lots of lock in your database
Both are different ,in the case of with(nolock) ,owner and other users will see the uncommitted data but in the row versioning case only the owner of the transaction will see the uncommited data and other will see the committed data
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 9, 2011 at 11:07 am
digdave7 (6/3/2011)
No to sound rude, but are you kidding me by saying Nolock is a bad idea 99% of the time....? Everyone I know has clearly shown it has huge benefits and inhibit locking, every DBA i've worked with, and myself included personally tested it over and over showing it helps with contention each time.Now back to the question. The nolock is not 100% guaranteed, if there are inserts updates affecting that exact row, or other memory resource issues, and sometimes a clustered index you could have some issues. I would also check to see if you have enough free memory on the server, in laments terms nolock says give me what pages you have in memory for this record. If there is not enough FREE memory then SQL cannot page(store) it in memory and in that case Nolock will not help you at all because it must read from Disk and thus fight with the records being affected.
Remember just becuase a server had 8 gigs, doesnt mean SQL has enough to store what it needs.
Not to gang up on you, but I actually had a situation with this. There was an order entry system with the primary/clustered key of OrderNo, which was VARCHAR(20). It looked like '100001', '100002', 100003', etc. for the order header. The order detail inherited that OrderNo plus a sequence number for its primary/clustered key.
Everything looks fine, an ever increasing clustered index, right?
Well, when an order didn't have enough stock, a back order would have to be entered. The clustered key would then look like '100001-001', '100002-001' and so forth. On days when many back orders were being entered some reports went a bit strange. Weird duplicates would appear and sometimes orders would disappear and then re-appear.
I tracked this down to page splits when the back orders were entered because they had to be inserted into the same data pages.
When I removed the NOLOCK hint in all of the report queries this phenomenon went away never to return.
I normally only use NOLOCK on pretty much static data - never in a transaction environment.
Todd Fifield
June 9, 2011 at 11:09 am
If the data is really static just put the file as read-only. Won't need nolock and it'll be even faster!
June 9, 2011 at 11:16 am
"Now back to the question. The nolock is not 100% guaranteed, if there are inserts updates affecting that exact row, or other memory resource issues, and sometimes a clustered index you could have some issues. I would also check to see if you have enough free memory on the server, in laments terms nolock says give me what pages you have in memory for this record. If there is not enough FREE memory then SQL cannot page(store) it in memory and in that case Nolock will not help you at all because it must read from Disk and thus fight with the records being affected."
I have some confusion about the above statement.. Could anyone elaborate more on this?
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply