May 12, 2010 at 11:06 am
We have one server (SS 2005) with terrible IO. Latencies (seconds/read and seconds/transfer) can easily run 200-500 msec for big blocks of time. (I can't do anything about this right now due to budgetary constraints.)
We are getting all kinds of weird blocking on this server. By weird I mean that we have NOLOCK SELECT queries blocking all kinds of other queries.
I have a vague recollection that you can get blocking reported if the
waittimes go too high for ANY reason. In other words, you may have a harmless query that is written correctly suddenly showing up as blocking another query just because the server is having to wait forever for an IO response. Can someone verify this? Or am I offbase?
May 12, 2010 at 11:17 am
You are not off base. I/O is basically reads & writes to the hard drive. If your disk controller went whooey recently or if there's another problem bottlenecking your I/O, you could definitely end up with blocking when things get too bad. The processes will just keep getting bottled up.
Run PerfMon on your Server to identify the I/O issue. Is it network? CPU-related? If it's just hard drive related, you may have to replace something on your SAN or local hard drive. Once you know for sure, though, you can go about remediating the issue.
FYI - this does NOT mean your only problem with blocking is related to I/O. It just means that if you know I/O is a contributing issue, get it cleared up first, then check your blocking to see if it resolved or if you have other problems to hunt down.
May 12, 2010 at 11:22 am
Brandie Tarvin (5/12/2010)
You are not off base. I/O is basically reads & writes to the hard drive. If your disk controller went whooey recently or if there's another problem bottlenecking your I/O, you could definitely end up with blocking when things get too bad. The processes will just keep getting bottled up.Run PerfMon on your Server to identify the I/O issue. Is it network? CPU-related? If it's just hard drive related, you may have to replace something on your SAN or local hard drive. Once you know for sure, though, you can go about remediating the issue.
FYI - this does NOT mean your only problem with blocking is related to I/O. It just means that if you know I/O is a contributing issue, get it cleared up first, then check your blocking to see if it resolved or if you have other problems to hunt down.
Thx. I should have been a little more specific. What I mean is that we are seeing a "perfectly fine" query blocking, as in when you look in sp_who2, another job/process. Are you saying this is entirely possible if your IO is bad enough?
And, yes, something is definitely wrong with the SAN here and we talking to the vendor.
May 12, 2010 at 11:28 am
Whisper9999 (5/12/2010)
Thx. I should have been a little more specific. What I mean is that we are seeing a "perfectly fine" query blocking, as in when you look in sp_who2, another job/process. Are you saying this is entirely possible if your IO is bad enough?
To my knowledge, if Query 1 cannot be written to the data file, then it will hold itself open (and its transactions / locks) until it can write to the data file. Which means if Query 2 wants to do something to a locked table / page / row, Query 2 will be blocked by Query 1.
Now, I don't know for sure if there's any kill-rollback contingency in the SQL engine for situations where I/O prevents Query 1 from finishing its writes in a "timely" manner, but I don't think there are.
To know for sure, the only way is to run a server-side Profiler trace and a PerfMon trace at the same time. After you've run them, import them both into the Profiler GUI and take a look at where your I/O spikes are compared to the blocking. That'll give you your answer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply