The Performance Game

Steve Jones, 2007-08-14

I’ve posted in the forums fairly often that there’s kind of a black art to performance tuning. As with most things, some people agree, some disagree, some don’t care, and some don’t know.

I stand by what I said for the most part, even though Andy teaches a Performance Tuning Class for End to End Training. Obviously there are some tried and true techinques that can help you tune queries and get better running database instances, but there’s more to it.

In any system, it tends to settle at some sort of level and the more you work with the database, you start to get an idea of how tables are structured, how often they’re used, where you get lots of activity, insert/delete levels, etc. A good DBA grows to know their systems very well, usually because of performance problems and issues. It’s that knowledge that you really need to be able to dig into performance issues.

However it’s not always enough. I’ve had a few forum discussions and a couple real life ones where the performanec of a query suddenly just drops. In one case I got called in with a friend, we saw horrible performance from a stored proc, yet the same proc ran fine as a script (no stored procedure code around it). We reboot and it works for an hour and then tips over again.

I happened to be at TechEd for that one and pushed on one of the query processor engineers, but they didn’t really give a good answer. He talked about how the query optimizer might feel pressure and create a new plan, but it wasn’t consistent and it didn’t make a lot of sense. I’ve asked other people since and have never gotten a great answer and I still see similar posts at times.

SQL Server 2005 gives you the ability to set hints in certain queries and while I hesitate to tune each query down to the performance I think it needs, this might be a good solution in certain cases where you have a stored procedure that runs inconsistently.  





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads