Tuning Performance of SQL Server Query Plans

When it comes to performance, the SQL database details should never be neglected, because even the smallest thing can turn out to be a great opportunity for SQL Server query optimization. Generally, one needs a significant amount of experience to detect that kind of details easily. But what shall the rest do till they gain the required knowledge?

In this case, the salvation are specialized SQL Server optimizers that enable to carry out a deep analysis of query execution plans and find bottlenecks in them. SQL Query Profiler, established in dbForge Studio for SQL Server, is one of the best SQL Server database tools that will help you with query tuning and optimization.

How to Optimize a Query

Let's take a look at how we can use Query Profiler for trivial, but nevertheless commonly-used task — deletion of record duplicates in specified table.

Suppose we have a table showing time when employees of all departments start their shifts. Given, that same employees may work in different departments, we would like to determine when employees of the sales department start working.

A standard solution for this task is to get a list employees from sales department (given, that the Department ID for sales department is the maximal value in the list), and delete data about another departments:

Let's have a look at the execution plan:

Actually we refer to the table two times. Before the deletion, to avoid several scans of the same data from the initial table, SQL Server applies Table Spool operation (the most resource-consuming in our case).

This operator scans the input table and copies each row into a hidden spool table. The table is stored in the tempdb database and exists only for the lifetime of the query. This operator allows the next ones to read data from the buffer instead of the real table.

To avoid the table spool reading, let's try to rewrite this query by using ROW_NUMBER. The execution plan got simpler and that means that the query will be executed faster. However, now we have another problem — the most resource consuming operation is sorting, which is used for grouping data by shift hours:

The probability that the same employee simultaneously starts working in several departments is very low, so we can drop department column in favor of a composite primary key. If we turn to the theory, we remember that a cluster index, which sorts table rows, is based on primary key columns. We have also simplified the initial table by removing the unnecessary column.

Execution of query #2 with the modified table resulted in the following execution plan:

It's easy to notice that the primary key modification allows us to omit sorting operator and makes the query execution faster:

Now let's take a look at the SQL profiling comparison table to determine the most effective example for performing the formulated task:

Summary

To sum it up, you only need few things to significantly accelerate query execution and simplify database structure:

• Some theoretical knowledge
• Optimize SQL query plan with profiler (for example, with SQL Server optimization tool which is implemented in dbForge Studio for SQL Server)
• Use a combo of different techniques

Further Reading

To learn more about SQL query tuning, read the SQL Server Execution Plans post on the Devart Blog.

If you want to discover more about the COUNT function, refer to SQL Server COUNT() and effective row counting.