I have an unusual situation going on right now, which is giving me a chance to get some unusual views of the guts of SQL Server. I'm wondering if what I see is known, is a feature or a bug, or if I shouldn't believe my lying eyes!
What I have is a situation where an app developer is accidentally (harumph) submitting some impossible cartesian joins to the server, and letting them run for an hour before noticing or killing them. Fun to start with, right? But here's the thing.
We have four processors and maxdop=0. These queries generate parallel plans. So what happens is the first one in grabs all the processors and runs forever. Sometimes smaller queries leak through, somehow. I'm not sure how that works!
But here's the thing, there are two more cartesian joins already running also, only ... they aren't really running. They are consuming CPU time steadily, but doing no logical IOs. I presume what is going on is that they can't reserve all the processors
they need for their plans, and so are "resource starved" blocked, which does NOT show as blocked on things like sp_who2.
So, my question is, if they're just sitting there resource blocked, and the cpu times are running up fast - is that a bug? Or are they actually maybe doing something useful with all that CPU, that does n
View Complete Post