I have been researching the Resource Governor in SQL 2008 and SQL 2008 R2. I gather that I can only really use this to limit the CPU and memory used by certain sessions. It seems strange to me, though, because if I use a governed session to create a block on another table/session, doesn't using Resource Governor make the situation much worse?
Let's say that my query in the default pool (allowing 100% CPU/Memory) would execute in 10 seconds while holding blocks in 3 tables because I've done a poor job of optimizing it.
Now let's say I am governed to 10% CPU and 10% Memory and I run the same query. It would take much longer to execute because of Resource Governor. It follows that it would block for that much longer, right?
I would really want to avoid that problem by configuring Resource Governor to not allow my session to block objects in the first place, and give me an error message. Is that not possible?
Let me know if that makes any sense at all.
Resource Governor can't be used to prevent blocking at all. Locking and blocking is how the database just works, it can't be prevented.