Inevitably, DBAs spend a lot of their time fire-fighting performance issues. A rogue blocking SPID urgently needs to be tracked down and killed in order to avert a crisis. The tools and methods for doing this, using sp_who2, Activity Monitor and so on, are well-known and documented. However, there is more to building a stable, well-performing system than the ability to put out fires.

DBAs need to continuously monitor and tune their systems, acquiring the performance data that will provide a deep understanding of an application's resource demands, and allow them to make informed, predictive decisions on where and when to add resources. To Microsoft's credit, it has generally responded with energy to requests for new and better ways of investigating and improving SQL Server performance. Profiler improves steadily; DMVs open up a swathe of new performance data; the capabilities of Activity Monitor have expanded substantially; "radical" new profiling techniques are emerging, based on XEvents and waitstats.

However, DBAs have been slow to adopt many of these tools. Faced with so many options, even choosing the right tool for a given problem is difficult. Having made the choice, some of the tools, DMVs and XEvents in particular, require patience and perseverance. Even having mastered usage of a given tool, it is not always easy to derive a clear plan of tuning action from the data it provides.

Profiler, for example, can provide you with a long list of "expensive" queries that have been executed against a given server or database, which you can order by CPU, duration and so on. However, it is often very hard to identify which of those queries that are having the biggest cumulative impact. This is especially true if your system is subject to a lot of ad-hoc SQL.

Even though it's SQL 2008 only, I'm excited by the advent of a response-time based tuning methodology, using XEvents and waitstats It is based on a very simple principle (Response time = service time + wait time). You start with the largest time consumer and work down. I like it because it is a clear and simple method that is guaranteed to tackle head-on the only performance metric that end users care about, namely response time.

Instead of piling on more, often increasingly complex, tools, I believe what is needed is a clear roadmap and methodology for systematically addressing SQL Server Performance issues. Although more about security than performance, books such as Kevvie Fowler's SQL Server Forensic Analysis take the right approach, focussing on method and technique, rather than just features and tools. This is what is required in the performance space, and I believe Microsoft, and communities such as SQLServerCentral, could do more to help. If you know of resources that provide a simple, reliable methodology for tackling performance issues, or have developed and documented your own tried and tested tuning methods, we'd love to hear about them.

Cheers,

Tony Davis (Guest Editor)