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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.