I’ve mentioned Jeff Atwood of CodingHorror.com a few times over the years here, and it bears repeating: he writes a great blog for developers, and DBAs need to read it too. I also follow him on Twitter, and a couple of weeks ago he mentioned he was having problems deciphering execution plans:
Holy cow – I could actually help the world’s most dangerous programming blogger with something! I fired off a Tweet and started helping him read execution plans. One thing led to another, and next thing I know, I’m tuning StackOverflow‘s SQL Server for him.
Now, I’ve done a fair bit of performance tuning, but I should have known that tuning is a little different in Jeff’s world. The easiest way to explain it is by relaying the first thing out of somebody’s mouth when we start performance tuning:
- Data warehouse manager – “The nightly loads are taking 6-7 hours a night, and we need to get that number down.”
- Web site manager – “Our queries are timing out after 30-60 seconds.”
- Jeff Atwood – “This query is taking 150ms, and I want it faster.”
And I should mention that Jeff’s written blog entries like:
- Who Needs Stored Procedures, Anyway? (and I would be remiss if I didn’t point out that he uses Quest Toad in that blog entry, baby!)
- My Database Is A Web Service (which contains this priceless quote: “Database performance is almost always the bottleneck anyway.”)
Nooo pressure. No pressure.
But wait, there’s more. Usually, when I go into a shop that’s never had a DBA, the server is a mess. Tables, views, field naming conventions and formats all over the place, no consistency, nobody knows if anything’s actually in use, etc. Not here. The schema on this thing was tighter than the Pope’s poop chute, as they say. (Really, they do. “They” being my parents.)
There is almost no low-hanging fruit here. Well, I mean, there’s a little, but we’re not talking big fruit. Berries. And they’re eight feet up. I got all the way down to comparing specific query plans on 2005 vs 2008 to find out why exactly one table was in the wrong join order on the execution plan to save 80ms on a query. I fixed it with SQL Server’s trace flag 2301 to get it to spend more time building the execution plan, but when we went live, the server couldn’t handle the load on queries using sp_executesql, so I had to rip that back out. Dammit, I want my 80ms back, and I gotta figure out a way to get it.
Update 10/30 – it was in fact slower, but it wasn’t me. I blogged about the problems we’re having with SQL Server 2008 full text search performance.