Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Sure, but why not let me reject queries that would create a block? Does the SQL engine not know before running a query if a block is needed? Thank you though, I ran a test to prove that I can block a table indefinitely in a session held by Resource Governor. It just isn't a solution for the common problem of staff writing bad queries that block tables and take down production. :) –  rayrayrayraydog Jun 23 '11 at 19:11
    
No it does not. The only time a block would be needed would be if another query was to start and attempt to access the same page which was locked by the first query. –  mrdenny Jun 23 '11 at 19:12
    
Thanks. I've mixed up locking and blocking behaviors again... –  rayrayrayraydog Jun 23 '11 at 19:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.