SQL Server performance tuning doesn’t always require special skills or even years of experience, even though experience often makes a significant difference in the time it takes to identify and solve a problem.  Having a basic understanding of where to look for performance problems and how to identify the root cause of performance problems is the key to any SQL Server performance tuning exercise. If you are new to SQL Server, or aren’t a full time DBA, our Accidental DBA series would be a great place to start out.

Common SQL Server Performance Tuning Issues

With a combined experience of over 60 years working with SQL Server, our team has seen a wide variety of performance problems, ranging from small businesses with limited resources to multiple Fortune 100 clients with edge-case workloads. Our team has a 100% record of success in tracking down and fixing client performance problems, as efficiently as possible, including many where Microsoft’s own Product Support staff were unable to diagnose the problem. As a result, we’ve seen many performance issues that are common and easy to resolve when you understand the root cause. Here we present the most common problems that we look at when focusing on SQL Server performance tuning. Eliminating these basic issues will often solve many of the SQL Server performance tuning issues we see day to day in our consulting services.

Windows Power Management Settings

Sometimes simple configuration issues can lead to major problems and attempts at SQL Server performance tuning may not have the expected results. Specifically, the Windows Server Power Management option being set to Balanced Performance has been a common short-circuit fix for many consulting engagements over the last year.

Wait Statistics

One of the best ways to start out with SQL Server performance tuning is to look at what queries are waiting for during execution. SQL Server tracks any time that it waits for resources and Paul offers some insights into using wait statistics for simple root cause analysis in his blog post Wait Statistics or please tell me where it hurts. We start many of our SQL Server Consulting Services with a review of the wait statistics to save our customers time and money.

Plan Caching Issues

Analyzing plan cache usage can be an important part of any SQL Server performance tuning exercise. Efficient plan caching is important to SQL Server performance and Kimberly shows how you can troubleshoot poor plan cache usage in her blog post Ad hoc workloads and clearing the single use plan cache.

Implicit Conversion Issues

One of the things we always look for as a part of any SQL Server performance tuning engagement is the existence of implicit column-side conversions that force an Index Scan operation where an Index Seek might otherwise be possible without the implicit conversion. Jonathan has two blog posts that provide a method of Finding Implicit Conversions by Searching the Plan
Cache as well as information about Specific Implicit Conversions that Cause Index Scans.

Reviewing Existing and Missing Indexes

Appropriate indexes can have a significant impact to the performance of a SQL Server workload, and one of the items we commonly review while doing SQL Server performance tuning is the indexes that currently exist and the missing indexes that are being tracked by SQL Server internally. Paul offers insight into the impacts of Over and Under Indexing in SQL Server in his blog post. Kimberly also covers how to identify if your indexing strategies are working by showing how to use the various indexing DMVs in SQL Server and many of her custom-developed stored procedures to identify duplicate and unused indexes that may be affecting performance. Don’t underestimate the effects of missing and unused indexes for your workload!

Establishing a Baseline

Unless you know what “normal” looks like, it is very hard to tell when a problem is occurring. Establishing a baseline data collection is critical for SQL Server performance tuning exercises. Erin shows how to establish a baseline for SQL Server in her SQL Server Baselines Series on SQLServerCentral.com. Erin also shows how to customize the default counters for Performance Monitor to make it faster to collect data for comparison with your baseline.

Our SQL Server Performance Tuning Services and Training Offerings

We’d love to help you with your SQL Server performance tuning project. We offer a number of SQL Server consulting services and have a long list of clients that we’ve assisted in the past. Our team has a 100% record of success in tracking down and fixing client performance problems, as efficiently as possible, at all levels of SQL Server workload and data volume.

Problems we can help you with include:

  • General workload slowdown
  • Connection/query timeouts
  • Excessive CPU usage
  • Memory pressure
  • Excessive I/O read/write latency
  • Query plan analysis
  • Indexing strategy
  • Excessive transaction log or tempdb size
  • Establishing a performance baseline and measuring against it
  • Optimizing procedural and ad hoc code
  • T-SQL/application code performance and scalability

We can go to whatever depth is required to help you solve the problem, and then we can explain what the problem is and how to avoid it, at whatever your technical comfort level is. If you want, we can also do the work to fix the problem for you.

We have multiple SQL Server training courses that are focused on SQL Server performance tuning including:

If you can’t attend in person, we have multiple online training courses that focus on performance tuning.

Don’t let poor performance hold back your business and your clients – get the help you need now!

Contact us today to discuss your requirements by email, phone, or online meeting, for a comprehensive review of your SQL Server and all the help you need to quickly get your SQL Server performance tuning problems under control – free and with no obligation.