Disabling Resource Governor

I had known that the Resource Governor is always running in modern versions of SQL Server. It’s core to the operation of the SQLOS, though it can’t be used to do anything unless it is enabled.

You can read more about it if you have Enterprise Edition, and I’ve thought it was a good start to controlling and throttling the resources inside SQL Server. I’d like to see it expanded, and I do like the CAP addition in 2012.

However I wasn’t aware you could prevent someone from enabling Resource Governor. Apparently you can, as shown in this blog: Disabling Resource Governor permanently (somewhat). Amit Bansal shows that if you set trace flag 8040, the Resource Governor cannot be enabled. This prevents changes by junior administrators, developers, etc. that might not be aware of the consequences of their actions.

Personally for me, I’m not sure I like this. To me this is a case where you don’t have trust among your staff, or good communication, or perhaps something else. None of those are good situations.

I don’t know that I’d ever use this, but you can read Amit’s post for more details.

Note that this doesn’t remove Resource Governor, and your DAC still resides in the internal pool, which always has guaranteed resources.

Resource Governer–Memory Limits

I had looked at the Resource Governor early on when it was being developed and first released on SQL Server 2008, but I hadn’t spent a lot of time on it. It was the first cut at a throttle that I had been asking for since 2001 or 2002 when I saw IIS get a CPU throttle. I loved the idea of being able to slow down the amount of CPU that an individual query could take, which I thought would really help limit the amount of damage that a particular query could do on your system.

However I hadn’t spent much time on the memory limits. While studying for the MCM, I downloaded this white paper and watched the Paul Randal video from Technet. In it, I was reminded that the memory limits you set apply to the query memory, not the buffer pool memory.

What does this mean? It means that while you can limit the memory that a query uses for it’s data, for it’s plans, etc., you can’t prevent a query from flushing the buffer pool when it causes a lot of reads from disk.

This is a limitation of the Resource Governor, and we might see this changed later, but it’s a good thing to remember that you cannot use Resource Governor as a way to guarentee predictable performance from your SQL Server. The engine is still a shared resource and one person can still affect it’s operation. Resource Governor does allow you to limit the damage, and it can be used to prevent someone from bringing the instance to a standstill. However it does not provide the complete control that you might assume is implied from the name.