Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Method to the Madness

By Tony Davis,

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)

Total article views: 139 | Views in the last 30 days: 2
 
Related Articles
FORUM

Is it a proper indexing method for boosting performance?

Indexing method for boosting performance

FORUM

Linked Server "provider" issues.

Linked Server "provider" issues.

FORUM

LINK Server/Provider

SQL Server 2005 Provider

ARTICLE

CPU and Scheduler Performance Monitoring using SQL Server and Excel

This article will demonstrate a method of creating an Excel-based CPU/scheduler performance dashboar...

SCRIPT

Get SQL Server 2005: Details about running or defined traces

This provides information about traces that are defined on SQL Server 2005.

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones