Friday, May 25, 2012

Being Resourceful

A client of mine has a system in development that consists of a normal, multi-user .NET desktop application plus a number of services that manage some occasional heavy-duty background processing on the database server. The problem is, when the services are running, the application performance grinds to a standstill and users are left for minutes on end waiting for their application to react.

This obviously isn't acceptable, but lowering the impact of the background processes isn't straightforward, either. It's not as simple as just rewriting a runaway query or adding a few indexes, and re-architecting the entire solution far into development just isn't going to happen. The question was, would it be possible to limit the amount of resources these background processes consume? My customer decided to try the Resource Governor feature in SQL Server but had no useful or tangible performance improvement, so I was asked to take a look at their setup and see what, if anything, was going wrong.

My own experience with the Resource Governor was limited to what I'd read about it, as well as trying out some example test setups, but I'd never configured or observed it on a semi-live environment, so I was delighted to get the opportunity to learn more about it.

We spent almost two days in total configuring and testing, making tiny changes in the RG configuration and observing the change in behaviour and processing time for similar workloads. With the RG disabled, the background processes would take a full 100% of the server processor time for minutes on end, so the goal was to brake this enough to leave enough processor capacity for the desktop app users without overly limiting the background processing.

Our results were surprising - to me, at least. The first surprise was how far down we had to take all the settings before we saw any effect at all. The second surprise (and I'd imagine that it's not always the case),  was that we consistently observed a sort of threshold effect while changing settings; nothing would change in observed or measured load through a range of changes until suddenly, with one change up or down, there'd be a huge increase or decrease in consumed resources. After a lot of experimentation and measurement, we eventually arrived at a configuration that gave us a decent mix of resource use and allocation.



So, is the Resource Governor a worthwhile addition to the DBA's toolbox? Absolutely. You're obviously still better off trying to tackle the source of excessive resource consumption rather than artificially limiting it, but when this isn't possible the Resource Governor gives you one more option to work with before going to the boss, cap in hand, and asking for a more powerful server. It's tricky enough to configure properly and not massively well documented yet but you can definitely get results with it.

Labels: , ,