2

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?

  • What exactly were "the proper Resource Governor settings"? – Aaron Bertrand Jun 15 '16 at 16:37
  • I created a Resource Pool, created a Workload Group using that Resource Pool, created a function to attach the specified user to that Workload Group, and turned on the Resource Governor using that function. – Dave.Gugg Jun 15 '16 at 16:44
  • And what were the settings for the workload group and resource pool? I don't need help understanding how you turned on Resource Governor, the important details are how it's governing. – Aaron Bertrand Jun 15 '16 at 16:51
  • Sorry, I meant to include that in my previous comment: CREATE RESOURCE POOL limitUsers WITH (MIN_CPU_PERCENT = 0, MAX_CPU_PERCENT = 30, MIN_MEMORY_PERCENT = 0, MAX_MEMORY_PERCENT = 10) – Dave.Gugg Jun 15 '16 at 16:56
  • With those settings your power user might be power angry when their queries take a long(er) time. Mind the office politics, too. – Erik Darling Jun 15 '16 at 17:29
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.