Sql-server – Resource Governor seems to increase resource usage

resource-governorsql-server-2008-r2

We are in the unfortunate position of running a data warehouse on the same instance as our production OLTP system. Recently a "power user" joined our company and has begun running SQL statements against the DW that are hogging resources. Yesterday he ran a query that sent our page life expectancy from the usual 200K+ seconds down to 39.

I have thought of two solutions to this problem. The first is to move the data warehouse to an Azure database. This protects our OLTP systems from running out of resources, but it comes with a monetary cost. The second solution is to use Resource Governor to limit the resources available for either specific users or the entire DW database. This seems like a free solution, so it is the one that I am investigating first. I have never used Resource Governor before, so I set up a few tests to make sure it would work as expected. I first ran a few big queries with no Resource Governor set up and recorded the CPU usage, Reads, and Duration. Then I created the proper Resource Governor settings and re-ran the queries. The duration rose as expected, but I was surprised to also see the CPU and Reads increase drastically. The CPU usage more than doubled and the Reads increased tenfold. It did protect the Page Life Expectancy from dropping. Is that a typical occurrence when using Resource Governor? Does anyone have any other ideas for limiting specific users or databases from hogging resources?

Best Answer

I have three suggestions

  1. Look into your Data Warehouse designs. It should be optimized to let ad-hoc queries run relatively quickly. Analysis Services can help with this.
  2. Off load DW queries into no busy hours. I find this works really well for companies I have worked at. One should not run "big" queries when most of your users are accessing the system. Instead, they should be saved at times when the server has a low load.
  3. Check to see if the power user is writing proper queries. You can have highly optimized indexes, great architecture, and preprocessed cubes, but if the power user is writing solutions procedurally and not set-based, that will be a problem. Maybe he is brining in too much data yet only uses a small part. Who knows, but a poorly written query can very likely be the culprit.
Related Question