SQL Server 2008 : Performance Tuning - Working with Query Execution Plans

2/18/2011 7:36:38 PM
An execution plan is a plan containing the steps SQL Server works through in order to satisfy a query. Most stored procedures or ad hoc Transact-SQL queries access more than one table, and most tables have a clustered index and usually multiple non-clustered indexes. The SQL Server Operating System has a component called the Query Optimizer, which is responsible for selecting the optimal execution plan to satisfy a query.

Getting visibility of execution plans is important since it can help developers understand several key aspects of designing a well-performing database including the following:

  • Index selection

  • Join type and direction

  • Statistics creation/ maintenance

  • Application of predicates (WHERE clauses)

Knowledge of the steps SQL Server uses to deliver query results provides insight into how the Query Optimizer functions and ideas about how you can influence the choices made to help ensure data is returned in the most efficient method possible.

When troubleshooting a poorly performing query, an execution plan provides a very helpful view of the cost of each step involved. In many situations, a query plan consists of multiple steps each with different associated costs. Performance tuning is often an iterative process of identifying the most expense step then reducing this cost, either through altering the query/ stored procedure or by making changes to indexes, statistics, an underlying table, or several of these possibilities.

Compilation and Recompilation

Compilation is the process by which the Query Optimizer determines a good plan to execute a query. Recompilation is the process by which the Query Optimizer recalculates a query plan because a condition affecting query execution has changed.

The first time a query is executed, SQL Server checks the procedure cache, an area of memory reserved for compiled query plans. If a plan is found, it will be used. Otherwise the optimizer compiles an execution plan based on statistics to determine the best method to satisfy a query. Once the query plan has been compiled, it is stored in the procedure cache for re-use in the future.

Recompilation occurs when plans stored in the procedure cache can’t be used because of a change since the plan was compiled. Below is a summary of common conditions that could influence recompilation:

  • Changes to statistics used to generate the cached plan

  • Using sp_recompile or calling a stored procedure using WITH RECOMPILE

  • ALTER TABLE or ALTER VIEW commands affecting the query

  • Changes to indexes referenced by the query plan (including dropping indexes)

  • Large number of inserts or deletes (exceeding a threshold value)

Cost-Based Optimization

Understanding query execution plans requires an introduction to query execution within SQL Server. SQL Server uses a cost-based optimizer where alternative relational database management systems use rule-based optimization or a hybrid of rule- and cost-based optimization.

Cost-based optimization means that a query plan is chosen based on a cost value that is placed on each of the different query plans or access methods that could be used to satisfy a query. Once the options have been evaluated (index seeks or scan, table scans, hashes and sorts, etc.), the optimizer selects and executes the execution plan with the lowest cost.

The Query Optimizer doesn’t actually execute the query with many different plans; its decision is based on statistics. Statistics within SQL Server are a histogram of the data whereby values for each column are sampled at preset intervals throughout the table. Using this method, SQL Server has a view on data distribution without having to examine every row for each query.

Statistics are essentially summary data the optimizer uses to make decisions about query execution. If statistics are missing or inaccurate, the optimizer is likely to make a poor choice when executing queries. This can affect query response times and overall server performance.

When troubleshooting SQL Server performance problems, it is important to examine statistics and query plans with a view to including or eliminating statistics problems to reach a resolution. Using the Database Console Commands (DBCC) family of commands, you can determine when statistics were last updated and how many rows were sampled, as well as resample statistics to rule out statistics from the possible causes of poorly performing queries.

Capturing Query Execution Plans

Execution plans can be obtained using either SQL Server Management Studio or SQL Profiler. Depending on the nature of the problem, you’ll need to determine the most appropriate tool. Queries with large execution plans are often best captured with SQL Profiler, whereas validating ad hoc changes and the impact of changes is often better suited to SQL Server Management Studio. There are two ways to display an execution plan: either in a graphical plan or in text format. The following sections describe each method, as well as how to capture and interpret the plans.

Graphical Execution Plans

Graphical execution plans provide a visual representation of the steps required to satisfy a query. More information can be drawn from the execution plan by moving the pointer over each step. To view a graphical execution plan, choose one of the following from the Query menu:

  • Display Estimated Execution Plan (does not run query)

  • Include Actual Execution Plan (does run query)

Both plans look the same or at least similar . Figure 1 shows an actual execution plan for a query that joins three tables. Here, you can see that 61 percent of the total query execution time is spent executing a table scan on a table named “_Individual”. Creating a clustered index on this table would remove the table scan and improve query completion time.

Figure 1. Graphical Actual Execution Plan

Graphical execution plans can be cumbersome to view and navigate especially for large queries or queries executed in multiple batches. It is often easier to navigate a query plan in full-screen mode: just right-click on the plan, choose Save Execution Plan As, and then give the file name the default extension.sqlplan. When you double-click the execution plan, it opens in full-screen view making it much easier to explore.

Text-Based Execution Plans

This actual query execution plan can also be captured using the SHOWPLAN_TEXT SET option.

|--Hash Match(Inner Join, HASH:([ca].[AddressID])=([a].[AddressID]))
|--Hash Match(Inner Join,HASH:([c].[CustomerID])=([ca].[CustomerID]))
| |--Table Scan(OBJECT:([AdventureWorks].[dbo].[__Customer] AS [c]),
WHERE:([AdventureWorks].[dbo].[__Customer].[CustomerType] as
| |--Hash Match(Inner Join, HASH:([i].[CustomerID])=([ca].[CustomerID]))
| |--Table Scan(OBJECT:([AdventureWorks].[dbo].[__Individual] AS [i]))
| |--Table Scan(OBJECT:([AdventureWorks].[dbo].[__CustomerAddress] AS [ca]))
|--Table Scan(OBJECT:([AdventureWorks].[dbo].[__Address] AS [a]))

Interpreting Graphic Execution Plans

Reading query execution plans requires an investigative approach and an understanding of how they represent decisions and steps within queries. Graphic execution plans contain icons representing operations that occur during query execution. SQL Server Books Online contains a complete list of operators; Table 1 lists a sample of common operators with corresponding icons and descriptions:

Table 1. Common Operators and Corresponding Icons
Clustered index scanScans a clustered index
Clustered index seekSeeks through a clustered index to find specific row(s)
Non-clustered index scanScans a non-clustered index
Non-clustered index seekSeeks through a non-clustered index
Nested loop joinShown when a nested loop is used
Merge joinUsed to perform some inner and outer joins
Hash matchRepresents a hash join where a computed hash table is used to match rows based on hash values
RID lookupRetrieves a row based on a row identifier (RID) (replaces bookmark lookup in SQL Server 2005)

Reading Execution Plans

The ability to read an execution plan takes time and practice. Use the following guidelines to extract the most interesting elements from execution plans:

  • Read plans right to left and top to bottom.

  • Use the + symbol in the bottom-right corner to navigate large plans.

  • Mouse-over each step to get more information.

  • Mouse-over connecting lines to see details such as number of rows transferred.

  • Note that heavier weight connecting lines indicate more rows moved.

  • Where a query consists of multiple batches, investigate the most expensive batch first.

It is essential to keep in mind the actual query. As you browse the execution plan, try to visualize how SQL Server is collecting, joining, and reducing the data from a very wide dataset (entire tables) to return just the results required to satisfy the query.

Once you’re familiar with the look and feel of execution plans, try to use the plans to draw conclusions. A common approach to tuning poorly performing queries is to identify the highest cost batch and then determine alternatives—such as new indexes, statistics, or altering the query—to remove the step.

Estimated and Actual Execution Plans

Execution plans are useful in understanding how SQL Server delivers query results. There are two types of plans you can capture: estimated plans and actual plans. Estimated plans are captured by selecting Display Estimated Execution Plan from the Query menu. The estimated plan displays the plan the optimizer should follow if the query were executed (without actually executing the query). Actual execution plans are captured by selecting Include Actual Execution Plan from the Query menu.

Selecting actual execution plan executes a query and captures the actual execution plan used to deliver the query results. The output is visible in a new tab within the query results pane: select this tab to reveal the graphic actual execution plan. If the query uses any temporary tables, you’ll need to use the actual execution plan because the temporary table won’t be constructed if the code isn’t executed. If you are using table variables instead of temporary tables, an estimated execution plan can be used.

In most cases, an estimated query plan is the same as an actual execution plan. However, there are differences in some instances usually related to statistics. Use DBCC commands to verify statistics on the objects accessed by the query and recalculate these statistics if necessary.

  •  Externalizing BLOB Storage in SharePoint 2010 (part 2) - Installing and Configuring RBS & Migrating and Moving BLOBs Between BLOB Stores
  •  Externalizing BLOB Storage in SharePoint 2010 (part 1)
  •  Sharepoint 2010 : Managing SharePoint Content Databases
  •  Sharepoint 2010 : Maintaining SQL Server in a SharePoint Environment
  •  Sharepoint 2010 : Monitoring SQL Server in a SharePoint Environment
  •  Database Availability Group Replication in Exchange Server 2010 : Load Balancing in Exchange Server 2010
  •  Database Availability Group Replication in Exchange Server 2010 : Comparing and Contrasting DAG Versus CCR/SCR/SCC
  •  SQL Server 2008: Managing Query Performance - Forcing Index Seeks
  •  Exchange Server 2010 : Deploying a Database Availability Group (part 4)
  •  Exchange Server 2010 : Deploying a Database Availability Group (part 3)
    Video tutorials
    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Install Windows Server 2012 On VirtualBox

    - How To Disable Windows 8 Metro UI

    - How To Install Windows Store Apps From Windows 8 Classic Desktop

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
    programming4us programming4us