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 optimization tools that will help you with query tuning.
How to Optimize a Query
Now, 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.
Let's assume that we have a table showing time when employees of all departments start their shifts. Given,
that same emoloyees 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 departmentns 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
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
To learn more on SQL query tuning, read the SQL Server Execution Plans post at the Devart Blog.