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?