SQL SERVER – Root Cause to Performance Problems – Notes from the Field #002

[Note from Pinal]: This is a second episode of Notes from the Fields series. When there is a performance problem we all relentlessly work to fix the issue, however, we hardly spend time to find what has created the issues in the first place. For example, it is easy to turn off our car alarm if it goes off every hour but it is equally important for us to find out what is causing it to happen and prevent it. Just like that Root Cause Analysis (commonly known as RCA) has been a very important element.

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces in their career – how you perform a performance related root cause analysis. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.

The first time completing a root cause analysis for a performance issue can be a stressful experience for a DBA who is new to performance root cause analysis or performance tuning. Many years ago during my first root cause analysis I honestly had no clue what I was doing. I had developers, managers and spectators over my shoulder pondering my every move. It was an experience I will never forget.  As an accidental DBA becoming a production DBA, I quickly discovered the difference between what I thought I knew and what I actually knew.

I never want to see you have a similar experience. The biggest performance tuning mistake I still see in the field is DBAs skipping the process of doing a root cause analysis and making knee-jerk reactions due to something they see or are told.  I have good news: a lot has changed in the last ten years with SQL Server.  In SQL Server 2012 you can find the root cause to performance problems without writing a single line of code [Youtube]. Even if you are not using SQL Server 2012 today, a Jr. DBA, accidental DBA or IT professional that wears multiple hats can easily do performance root cause analysis with SQL Server, all you need is a good checklist and some helpful scripts and you are ready.

The following scripts and documentation will get you started doing your own performance root cause analysis:

• Baseline SQL Server Wait Statistics
  • Capture and measure your server’s wait statistics, as this gives deep inside in your server’s query waits and resource bottlenecks (link)
• What SQL Statements are Causing Top Waits?
  • Identify queries offending server resources (link)
• What is Running?
  • Understand what are the different activities going on in your server at any point of time. (link)

In additional to above three primary RCA practices it is very important to understand baseline for disk latency as well as query cache offendors. Hopefully, this will get you started with finding the root cause to your performance problems.

Are you doing Root Cause Analysis of your SQL Server Performance Problems? If not, you MUST do it. If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Additional reading:

• Introduction to Wait Stats and Wait Types – Wait Type – Day 1 of 28
• Identify Most Resource Intensive Queries
• Reducing CXPACKET Wait Stats for High Transactional Database

Reference: Pinal Dave (https://blog.sqlauthority.com)