Slow and cheap, right? Managers and DBAs usually want the development server to be just slow enough to get the job done, and no faster. The dev server doesn’t have production load on it – you’re just working by yourself. If you can’t make your code fast when you’re the only person working in the server, then it’ll never scale in production with hundreds or thousands of simultaneous users.
Wrong – the development server should be faster than production. Ideally, the whole database should fit in memory, and we should be using solid state drives. Amazon’s running a huge >30% off sale on Samsung 840s – you can buy one of the fastest 250GB SSDs on the market for just $169, which reminded me I’ve been meaning to write this post for a while.
Developers (and DBAs) should be encouraged to try out new indexing ideas. Wanna see how a new set of indexes changes performance? Drop the existing ones and create new ones, then test your query again. Didn’t help? Try another set of indexes, or drop those and go back to the original ones. The key here is blazing fast storage to encourage experimentation. If it takes fifteen minutes to add a single index, developers won’t bother managing their indexes.
Long, drawn-out query times discourage rapid query testing, too. I’ve seen shops where the development server was so underpowered, nobody ever got an actual execution plan. They didn’t have time to wait for the query to run. Instead, they just tuned by comparing estimated execution plan costs – which are often wildly out-of-whack compared to actual metrics.
When my developers are tuning a query, I want them to compare query workloads with real metrics by using SET STATISTICS IO ON. This session option gives you the number of logical reads performed by the engine to fulfill your query. Improve the query, tweak indexes, and see if the logical reads go down. Wash, rinse, and repeat. Forget clock times – I want to know exactly how much data we’re consuming, and try to make that number go down.
To make this all work, developers need the same data size in their development server that we’re using in production. Not a subset, not an old dataset from two months ago, but as close as we can get to right now’s live production data. When I give developers the current data, they’re less likely to ask for production server access.
While my dev server’s configuration settings should match production for things like MAXDOP, there’s no need to cripple the hardware. If I’m running SQL Server Standard Edition in production, and I’m limited to 64GB of RAM, that doesn’t mean my development server has to match. It’s running Developer Edition anyway, which has Enterprise-level features – go ahead and throw enough memory in there to cache the entire database if you can.
“But wait,” the DBA says, “the developers will think their code is fast, and then we’ll get an ugly surprise when we go live in production!” Not so – train your developers on query costs and STATISTICS IO output, and give them thresholds for what numbers won’t perform well in production. Good developers are great at watching metrics – the bigger problem is that we’re not giving them the right metrics to watch.
Besides, with a slow development server, they’re not going to write fast code. They’re just going to assume everything’s slow because they’re using a crappy development server. Without the right metrics to watch, they’re going to get surprised EVERY time a query runs in production – they really won’t know what’s going to be fast or slow.
Go slap together a RAID 10 of these Samsung 840s and make your developers excited to tune their indexes and code. After all, you do want them working on that, right?