-
-
-
In this SQL tutorial we want to give you an overview of how to improve query performance by looking at the following areas:
- Measuring Query Performance
- Tuning Query Performance
Measuring Query Performance
The most frequent access to a database is to retrieve data from it using SELECT statements simple or complex as the case may be. As a result, measuring and improving query performance can enhance SQL Server performance greatly.
You can measure the performance of queries using three main metrics:
- Query Cost which is a measure of the CPU and input-output resources used by the query.
- Page Reads which shows the number of pages read off the hard-disk during query execution.
- Query Execution Time which gives the time taken for a query to complete execution.
Each of these metrics will now be explained.
What is Query Cost?
Query cost assesses the CPU and the I/O resources that were used during the execution of a query.To view the query cost, in the SQL Server Management Studio:
- Type or open the query you want to analyze.
- Click Query > Include Actual Execution Plan.
- Execute the query.
- The Results window displays an Execution Plan tab.
- Click the Execution Plan tab to view the query cost.
- Shown below is the Execution Plan tab after multiple queries have been executed.
A low query cost is an indicator of good query performance. However, query cost does not account for resource contention or the time spent in dealing with locks.
Also, it does not include the cost resulting from user-defined functions and Common Language Runtime (CLR) routines.
Thus, this query cost is often referred to as estimated query cost.
What are Page Reads?
SQL Server reads data from storage in pages, where each page is 8KB. Page reads are a set of statistics that indicate how many pages were read from storage. To view page read metrics, before you execute the query, execute the command:
SET STATISTICS IO ON
Now when you run a query, the Messages tab of the Results window displays the page reads details. All logical reads must be totaled to get the page reads details as shown in the screen.
Note: Logical reads are pages read from the cache during query execution, while physical reads are pages read from the disk. It is possible that a page is read multiple times from the cache and so logical reads may be considerably higher than physical reads. You can lower logical reads by using well-planned indexes.
What is Query Execution Time ?
Query execution time measures the total execution time of a query in milliseconds. However, it also includes time consumed by resource contention and negotiating locks.
To view execution time, before you execute the query, execute the command:
SET STATISTICS IO ON
Tuning Query Performance
Among the various ways to tune the performance of a query are:
- Rewriting the query. There is no one way to write a query. If you find that a query with an OUTER JOIN is taking a long time, rewrite it using an INNER JOIN. Or perhaps the WHERE clause is written in such a way that it is causing excessive database read operations.
- Normalizing or de-normalizing tables. While normalizing tables results in ideal database design, it often leads to poor querying. If you find that frequently-used queries have to span too many normalized tables, consider some duplication of data. For example, in the Northwind database, the Unit Cost column is present in both the Order Details and Products table.
- Adding indexes. Lack of well-defined indexes is a frequent cause of queries taking a long time to execute. Creating indexes will cause a query to lookup the index rather than the table.
- Removing indexes. In some cases, it is a poorly defined index that is the cause of slow query execution. For example, an index which does not include the frequently looked up columns in a table. In such a case, it is better to drop the index and recreate it.
You can use one or more of these approaches to improve the performance.
Understanding Query Execution
Before we can optimize a query, let us take a look at how a query is executed. The typical order in which clauses in an SELECT statement are executed is shown in the table below.
Note that the execution order varies when a UNION operator is used. In addition, the Query Optimizer may change the execution order to improve performance.
Execution Order
(Including the UNION clause)Execution Order
(Excluding the UNION clause)FROM, JOIN, APPLY, and ON FROM, JOIN, APPLY, and ON WHERE WHERE GROUP BY and aggregate functions(included in the query) GROUP BY and aggregate functions(included in the query) HAVING HAVING TOP SELECT UNION and SELECT ORDER BY ORDER BY TOP FOR XML FOR XML Selecting Queries for Optimization
To select queries for performance, use the SQL Server Profiler to monitor the SQL:BatchCompleted and RPC:Completed events with a focus on the following columns:
- Duration: Time of execution in milliseconds (or microseconds)
- Reads: Total number of data pages read
- Writes: Total number of data pages written
- CPU: CPU time use in milliseconds
Any increase in the column values above the desired level implies that performance of the queries requires tuning.
You can also examine the Graphical Execution Plan as a means of identifying bottlenecks in query execution. The table below lists the visual components and how they can be used to optimize queries.
Components Indications Thick arrow The thicker the arrow, greater is the number of rows being transferred from one operation to another. Hash operation Hash operations on GROUP BY and JOIN operations indicate lack of appropriate indexes. Sort operation A high cost for a sort operation may indicate the need for an index. Table or clustered index scan Indicate lack of appropriate indexes on the given tables. To view the graphical execution plan, in the SQL Server Management Studio:
- Type or open the query you want to analyze.
- Click Query > Include Actual Execution Plan.
- Execute the query.
- The Results window displays an Execution Plan tab.
- Click the Execution Plan tab to view the Graphical Execution Plan.
- Shown below is the Execution Plan tab after multiple queries have been executed.
- Move the mouse pointer over the node whose details you are interested in.
Queries with Search Arguments (SARGs)
A WHERE clause helps you to restrict the number of rows returned by a query. However, the manner in which the WHERE condition is specified can impact the performance of the query. If the WHERE condition is written such that it uses a function that takes an indexed column as the input, then the index is ignored and the entire table is scanned. This results in performance degradation.
For example, the following results in a table scan because the column OrderDate is used in a function:
SELECT CustomerID, EmployeeID FROM Orders WHERE DATEDIFF(m, OrderDate, GetDate())>3
If the function is rewritten as shown below, then the query seeks the required value using an index and this improves performance:
SELECT CustomerID, EmployeeID FROM Orders WHERE OrderDate < DATEADD(m, -3, GetDate())
The filter criteria in the second query is said to use a Searchable Argument or SARG because the query optimizer can use an index seek operation during execution.
Queries with Joins
Joins in queries increase the cost of the query and thereby lowers its performance. Hence, it is best to minimize the number of join clauses to ensure optimized query performance.Outer joins incur more cost than inner joins as an outer join also works with the unmatched rows of the two tables. Queries with only inner joins and the WHERE clause have similar execution and therefore have similar performance.
Improving Query Performance - SQL tutorial Summary
There are many ways in which a query can be written, we hope this brief SQL tutorial has given you some more ideas in improving query performance.
-
-
