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?
I see what you’re saying and I don’t disagree with all of it, but I do like a slower server to be in one of the dev -> testing -> prod environments. It probably depends somewhat on the data set size but I find that if I can’t make a query fast on the slow test server, I might need to re-evaluate that query even if it runs fine on my dev box.
Alan – but what does “fast” mean? Are you measuring by clock time? If so, that’s totally irrelevant between isolated and production environments. Even a “fast” query in an isolated environment doesn’t prove anything when there’s no concurrency issues.
Brent – Yup, I used “fast” very ambiguously. In my particular case there aren’t concurrency issues due to the nature of what we’re doing so my thoughts aren’t applicable to most people’s situations. I do have an under-powered production server problem. Of course some day that will be fixed but until then, I consider fast enough as under 3 seconds and fast as under 1. (The reason for this is our system runs about 4700 queries and has a 2 hour window to do it so 7200/4700 ~ 1.5)
I do consider IO statistics when tuning but we’re almost always looking at entire tables or large subsets so they’re not always as helpful as I would like.
I think the idea that the dev servers should be fast is absolutely awesome. Nothing is more boring than sitting around waiting for 10 minutes for an execution plan to come back. I just like having a slower one in the mix because on occasion it has helped me notice something that wasn’t a problem on the dev box.
What do you think of a smaller than production QA or UAT/E2E server. This means developers still have a fast playground, but there is an environment where not all the pages will live in memory and you will have production like performance problems?
Justin – when you say “production like problems”, does that mean you’re simulating production loads? If so, you can’t do that on a slower server.
Justin – but that doesn’t mean you want a “smaller than production” QA, you want an equivalent-to-production QA to have production-like issues.
I like the idea of a fast DEV as Brent proposes for the reasons he outlines, but have yet to work with any company where they would go for it 😉
I’ve talked several companies into it by bringing management into a room and demonstrating how long it takes to build an index on their current dev servers.
I wait a painful 30-45-60 seconds with no talking allowed, and as they start to get the point, we talk about how much money they’re losing. Either their developers are losing productivity, OR they’re just flat out not tuning at all. Both of those are expensive propositions. Then I pull out a spec list with prices of what I’d recommend, and it’s a slam dunk at that point.
Well imagine a fortune nn company that forces people to work on a laptop with vista, 3gb ram, x watching services and 240gb ide drive.