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.