12 Tuning Tips For Better SQL Server Performance

Like all critical tech functions, growing servers and databases require regular upgrades and maintenance for superior usability. However, with the high costs and marginal results of common improvement measures, many expert developers have turned to alternative methods for improving server performance. Instead of crediting performance issues to hardware limitations, network administrators are finding that most operational issues are caused by poorly written queries and inefficient indexing – problems which can both be resolved with query tuning. In fact, industry experts believe that query tuning is one of the fastest and most reliable ways to improve SQL Server performance.

What Is SQL Tuning?

In 1988, Microsoft released the first version of SQL Server as a skeletal database product that simply managed and stored information. By 1993, Microsoft developed the SQL Server 4.2 which combined the power of a high-performance database with a simple user-friendly interface. This fresh approach to informational management and storage quickly established Microsoft as one of the most popular distributors of high-end relational database software.

Today, SQL Server is an RDBMS – relational database management system – that’s used in the enterprise environment to aid in transaction control, exception and error handling, row processing, and declared variables. As an industry-leading database, SQL Server provides features that meet the needs of a variety of users like large enterprises, medium organizations, small business, and even individuals.

SQL tuning helps keep these features functioning properly to improve overall database performance, and allows you to search, organize, segment, and optimize your organization’s vast collections of information more efficiently.

Three Steps Of SQL Server Query Tuning

The process of SQL Server query tuning can be divided into three broad categories using SQL language:

Basic query analysis: helps to isolate the issue by exposing all layers and information associated with expensive queries. Completing a basic query analysis requires you to know top SQL statements, top wait types, SQL plans, blocked queries, resource contention, and the effect of missing indexes.
Advance query analysis: moves beyond the basics by determining what the root issue is and what needs to happen in order to resolve the situation.
Facilitate tuning: Use a database monitoring tool to survey health metrics and facilitate the SQL server performance tuning process.

The following 12 SQL Server performance tuning tips break down these broad categories to help you improve database functions.

1. Check Table and Row Counts

Before you start the performance tuning process, be sure you’re working with an actual table. Many DBAs often begin querying on a view or table-valued function that has unique performance results and doesn’t contribute to the overall server performance. Examine table details by hovering SSMS over elements and verify the row count by querying the DMVs.

2. Note the Filtered Row Count

After querying the DMVs, examine the returned filtered row count. Queries are often slowed down if no filters are returned, indicating a larger issue that requires further investigation.

3. Know the Query Selectivity

Tips 1 and 2 should give you a relatively good idea of how many rows you’ll be working with throughout the query process. At this point you should also know the size of logical set. Consider using the SQL diagramming tool for assessing queries and query selectivity.

4. Look for Extra Columns

The SELECT* or scalar functions allow you to closely examine the query in order to detect additional columns. The more columns returned, the poorer the system performance will be because it becomes more difficult for an execution plan to utilize certain index operations.

5. Constraints Can Help

Knowing and utilizing constraints can assist you as you begin advanced query analysis. Take time to review existing keys, constraints, and indexes to avoid duplicating efforts or overlapping existing indexes. Access information about existing indexes by running the sp_helindex store procedure.

6. Know the Execution Plan

While estimated plans are used in the basic query analysis process to determine an estimated number of rows (Tip 1), the actual execution plan uses verified statistics at runtime to produce an actual number of rows. Differing plan results often indicate that further investigation is needed.

7. Record Results

A small, but significant tip, recording your results enables you to accurately track your changes and determine how these changes impacted performance over time.

8. Make Single Changes

Instead of making too many changes at once and risking the effectiveness of the query, start by looking at the most expensive operations and adjust your query based on your findings. By making small, single changes you have a better chance at achieving the optimal result for the given situation.

9. Rerun and Record

By this point, chances are you’ll have seen at least some minor improvements in server performance. However, if the problem is not yet fully resolved you must repeat Tip 8 and examine the results of further adjustments. Make sure to rerun the query after every single change you make, record your findings and compare results until you’ve reached a solution.

10. Adjust Indexes for Further Improvement

While altering indexes isn’t always recommended, often times it is the only way to improve system performance if you can’t adjust the code. Look at existing indexes, a covering index, and a filtered index until improvements are made.

11. Look for Common Faults

During the query process, take note of common performance inhibitors such as:

Code first generators

Abuse of wildcards

Scalar functions

Nested views
Cursors
Row by row processing

12. Use a Performance Monitoring System

After completing your SQL tuning, consider ways you can facilitate the process in the future. While keeping a record of which queries worked and didn’t work is helpful during the initial tuning process, utilizing tools like a continuous database performance monitoring system can help to integrate performance information in one secure location while also providing you with invaluable database performance analysis information such as: