November 16, 2006 at 10:34 am
hi all. I have a Q re: Locking. I understand the basics of how locking works, and lock escalation, and intent locks, etc.
I have the following blocking scenario:
SPID BLOCKED LOCKWAITTYPE WAITRESOURCE
125 69 LCK_M_IS TAB: 46:624161419
75 0 IOCOMPLETION
69 75 LCK_M_IS TAB: 46:624161419
All 3 SPIDs are SELECTs.
SPID 75 is blocking 69, and 69 is blocking 125.
SPID 75 is waiting for IO to complete, which could indicate IO problems. My question to you-all is, if all 3 statements are SELECT's, why are SPID 69 and 125 waiting on trying to take an intent lock at the TABLE level. Shouldn't the locks be compatible? I'm having the same problem where the blocks are PAGEIOLATCH_SH.
Can anyone tell me why I am blocking?
Cheers,
Wayne
November 17, 2006 at 4:30 am
io completion indicates a stall in your disk subsystem .. but I would only really expect to see an io completion wait on a write - not a read .. if that's so then you might be waiting for a transaction to complete.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 17, 2006 at 7:20 am
November 17, 2006 at 8:18 am
An IO may not block a table. For example,
SELECT * FROM myTable WITH (NO_LOCK)
November 20, 2006 at 6:09 am
a dirty read has nothing to do with with an i/o completion wait, you're totally missing the point.
It's possible for a write wait on the tran log to cause blocking as control won't be returned to the client.. it's a somewhat unusual circumstance. Possibly running profiler checking for scans would be an idea - check for poor query plans too.
Try these counters in an effort to assist - disk read and write i/o completion time - difficult to pin a figure but I'd expect values under 6 ms, page life expectancy , a poor query could make this dip suddenly. You might want to examine disk time and disk queues. Page faults. lazywrites, checkpoint pages.
Explicit transactions will cause problems check for open transactions too when your getting this problem, then use dbcc inputbuffer to identify the code from the spid(s)
Check for inadequate or less than useful indexes, out of date stats etc. etc.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 20, 2006 at 11:46 am
That is my point. In order to not lock data page, should not we use the hint NOLOCK?
November 21, 2006 at 5:40 am
There's some interesting points about dirty reads --
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 21, 2006 at 5:46 am
maybe http://sqldev.net/articles/wait_types.htm can get you on track to solve this issue.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply