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.

Slow Progress

I spent a frustrating morning with a client recently trying to use a Progress database as the source for an Integration Services package. The fix, as is so often the case, was simple, but I figured it was worthwhile documenting it here. I'm afraid that it's a more technical post than I usually put here, but if I can save someone else the same trouble we had then it'll be worthwhile.

The problem was as follows: we were trying to automate importing data from a Progress database to an MS SQL database using an SSIS package built on a 64-bit Windows 7 machine. We had an ODBC link set up (using the 32-bit OpenEdge drivers) that appeared to be working fine for the Import/Export Wizard and source previews and the likes - we could see and query data - but failed with ODBC error messages when run as an SSIS package from within the Business Intelligence Development Studio. "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" 

Google searching the problem wasn't very encouraging. We tried a dozen different workarounds, from editing the connection strings to bypass the ODBC DSN, registry hacks, trying to fake a 64-bit ODBC connection, but nothing helped. Then, as sometimes happens, I remembered a setting in BIDS that I'd seen a long time ago, never used and then forgot about.

In BIDS, select "Properties" from the "Project" menu. Select "Debugging" on the left-hand side of the Property Pages dialog, then change the value of Run64BitRuntime under "Debug Options" to "False". That simple. Once I'd done that, the import package ran like a train.

Read more »