SQL Server Performance Tuning Insider Secrets

We recently convened a Twitter chat with a panel of SQL Server and database performance experts to discuss the myths and realities of SQL Server database performance (read Part 1 here and Part 3 here) and recommendations for improving performance, which we’ll share in this article.

To answer these SQL Server performance questions, we convened a Twitter chat with a panel of SQL Server and database performance experts, including: Tim Chapman (SQL Server Dedicated Support Engineer (PFE) at Microsoft, Microsoft Certified Master), Joey D’ Antoni (Solutions Architect and Anexinet), Chris Bell (Founder, WaterOx Consulting), Robert Davis (SQL Server 2008 Certified Master and Principal DBA at OuterWall), Steve Karam (Oracle ACE and Technical Manager at Delphix),  Karen Lopez (SQL Server MVP and Sr. Project Manager / Architect at Infoadvisors), and Thomas LaRock (SQL Server MCM and MVP, and Technical Evangelist at SolarWinds).

 

First, define the performance issue

It may seem basic, but when tackling database performance issues, it’s important, as Tim Chapman points out, to define the problem—identify the specific performance issues observed by end users:

 

In the heat of the moment, it’s all too easy to be reactionary and leap into problem solving without understanding the basics of the problem, such as which users its affecting, when it’s happening and so on. Steve Karam notes that it’s important to understand the true scope of the issue, such as whether it’s global or local, or whether the issue is seen only under load, or whether other specific conditions apply to the performance issue:

 

So next time a performance issue makes its way to your attention, start by documenting as precisely as possible what the performance issue is.

 

Next, examine the wait events

Once you understand the scope of the performance issue, Tom LaRock observes, the best place to start looking for what might be causing those specific performance issues is to look closely at wait events.

 

Current waits critical to understanding the root cause of the issue, according to Robert Davis.

 

Equally important is the ability to understand the wait type information in context of resources used and historical trends.

Frequently, looking at the plan cache can provide valuable information about performance issues, as can PERFMON counters:

 

To help DBAs assess this performance information, there are free, third-party performance tools such as SolarWinds Database Monitor, and Diagnositcs Manager, as Tim Chapman recommends here:

There are also full-featured tools such as SolarWinds Database Performance Analyzer.

 

Finally, develop a plan of action for addressing the root cause of waits

Using wait event information, you can apply response time analysis to assess the impact of waits on performance, and specifically performance as measured by end user response. Finally, map a plan of action to address the root cause of the performance issue. Generally, it’s best to make small, discrete changes, one at a time, to understand the impact on performance.

Be sure to read Part 1 of this series here about the myths and facts of the impact of hardware on SQL Server performance, and Part 3 about the best performance features of SQL Server 2014.