Root Cause Analysis

Root cause analysis is a term that is used in almost every industry. The American Society for Quality defines root cause analysis as “a collective term that describes a wide range of approaches, tools, and techniques used to uncover causes of problems.”

In the database sector, root cause analysis generally means determining the specific problem(s) underlying database performance issues.

SQL expert John Sterrett offers a basic checklist of what’s involved in a root cause analysis:

• First, get a good grip on your baseline SQL Server wait statistics. The best plan is to capture and measure your server’s wait statistics so you can get the insight you need into your server’s query waits and resource bottlenecks
• Second, determine which SQL Statements are causing the longest waits. The key step here is to identify queries over-utilizing server resources
• Develop a thorough understanding of all the different activities occurring in your server at any point of time

In addition to the three primary RCA practices outlined above, Sterrett emphasizes it is also important to have a good idea of baseline for disk latency as well as the most common query cache offenders.

Idera’s SQL Diagnostic Manager also allows you to undertake in-depth root cause analysis. Diagnostic Manager automatically takes a snapshot of a variety of important information at set intervals. These snapshots are stored it in your repository database, and this kind of granular retrospective analysis is great resource when you’re trying to get the bottom of vexing performance issues.