My most un-favourite things about Resource Governor

First, why is the Resource Governor packaged as a feature only in Enterprise Edition? After all, it is fully working in Standard edition, and DBAs have a chance to use it on a daily basis even in Standard edition. (the first one of you who comments to this post and mentions which SQL Server module is using Resource Governor in Standard edition will get a golden star).

Second, the paradigm of managed resources is ‘half-way-there’. Think about it: there are 3 general groups of resources on which SQL Server depends on: CPU, Memory and IO.

IO is not addressed at all, even though this is the biggest bottleneck in most systems. (Hmm, I guess by the time Resource Governor version 2 is released, everyone will be using SSDs, so there will be no need to govern the disk subsystem 🙂 )

The memory subsystem is also clumsily governed, since the Resource Governor administers how much memory is assigned to a query at runtime, and NOT how much memory is used by the query’s data. In other words, a query which needs a small amount of memory to compile and run might bring tons of data into memory, and the Resource Governor cannot do anything about it.

The quiet mode: there is no reporting, no notification. How would I prove to my client that the Resource Governor is really helping? In a case of contention I would like to know how the resources were assigned, who got what and what actually triggered the resource governing.

To the last point I got a few comments, that I can use the Extended Events and Perfmon to track down contention. It is true that in Perfmon we can see the SQLServer:ResourcePools class and get some information about what the Resource Governor is doing. It is also true, that with Extended Events we can do quite some detailed research.

Here is the problem, though: Resource Governor, in its nature, is a proactive tool. The Perfmon and the Extended events are by nature reactive, and with some effort, they can turn into data source of performance information. (After all, SQL Server would have been such a simple tool, if we did not have the factor ‘Time’ involved! 🙂 )

Think about it: Resource Governor is like the smoke alarm with self adjustable sprinkler hose built in. In case the stove catches fire, the sprinkler points and puts it off. The owner comes back and sees eventually that a fire was put off, but there is no report saying when, how and why. The Perfmon and the Extended Events require quite some work in order to be set up properly in order to give us all the information we need about when, how and why.

Anyway, I just wanted to share an observation of mine, that SQL Server has been highly dependent on the factor ‘Time’ since it was created, but the monitoring of it is still a mystery. Here it is: SQL 2000 and earlier versions relied mainly on Profiler and Perfmon – the factor ‘Time’ is involved there, but it is very tedious hard work to overview an entire system at all times. In 2005 we got DMVs – quite clumsy, when it comes to historical detailed data. In 2008 we got the first edition of the Extended events, which give a great deal of detailed information and then some, but there is no official UI, and the extended events have their drawbacks. I will write in detail about the Extended events in a later post.

Hmm. On a positive note, however, I am sure that the Resource Governor version 2 will rock. By the way, are there any improvements already in Denali?

To be continued…

/ Feodor Georgiev