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.”
Gulp.
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.
Anyway, I’m proud to say that last night I helped Geoff Dalgas upgrade StackOverflow to SQL Server 2008, do more performance tuning. If StackOverflow is slow today, it’s all me.
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.
4 Comments. Leave new
Brent, since they’re using TOAD, my SQL Tool of choice btw, aren’t they also using the SQL Tuning Tools in it to optimize their queries?
It’s one of my favorite pieces of the suite and has made me a much better query writer.
That being said, I’m sure “pressure” is your middle name and you’ll do fine with the infamous Jeff Atwood.
They’re not, and I wanted to bring it up to ’em, but I didn’t want to come in with a sales pitch. I wanted to be able to help them on a one-to-one basis, prove myself, and then go back and say, “Alright, to make your life easier, check out these tools. You won’t have to hire a DBA.” But man, I tell you, they’re SERIOUS programmers, and they don’t rely on crutches for anything.
I don’t consider it a crutch. I write my queries to the best of my ability and then use the Tuning Tool. Often there is no improvement to be had, but sometimes the program will point out something I missed and it is a learning experience for me.
The value of a tool is in how you use it. Anybody who’s read Coding Horror knows that he is definitely in favor of developers having all the necessary tools to perform optimally.
After all, he wouldn’t advocate his guys program in Notepad, just because it’s techically possible. Using the crutchless idea they wouldn’t be using TOAD at all. If they were really “hard core” they’d just use the built in SQL tools which we all know leave a lot to be desired.
I understand your point though Brent about not wanting to sound spammy, after all he is Jeff Atwood.