When it comes time to analyze the performance of a specific query, one of the best methods is to view the query execution plan. A query execution plan outlines how the SQL Server query optimizer actually ran (or will run) a specific query.
There are several different ways to view a query’s execution plan. They include:
From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If you turn this option on, then whenever you run a query in Query Analyzer, you will get a query execution plan (in graphical format) displayed in a separate window.
If you want to see an execution plan, but you don’t want to run the query, you can choose the option “Display Estimated Execution Plan” (located on the Query drop-down menu). When you select this option, immediately an execution plan (in graphical format) will appear. The difference between these two (if any) is accountable to the fact that when a query is really run (not simulated, as in this option), current operations of the server are also considered. In most cases, plans created by either method will produce similar results.
When you create a SQL Server Profiler trace, one of the events you can collect is called: MISC: Execution Plan. This information (in text form) shows the execution plan used by the query optimizer to execute the query.
From within Query Analyzer, you can run the command SET SHOWPLAN_TEXT ON. Once you run this command, any query you execute in this Query Analyzer sessions will not be run, but a text-based version of the query plan will be displayed. If the query you are running uses temp tables, then you will have to run the command, SET STATISTICS PROFILE ON before running the query.
If you see any of the following in an execution plan, you should consider them warning signs and investigate them for potential performance problems. Each of them are less than ideal from a performance perspective.
Index or table scans: May indicate a need for better or additional indexes.
Bookmark Lookups: Consider changing the current clustered index, consider using a covering index, limit the number of columns in the SELECT statement.
Filter: Remove any functions in the WHERE clause, don’t include views in your Transact-SQL code, may need additional indexes.
Sort: Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?
What Happens When a Query is Submitted?
When you submit a query to a SQL Server database, a number of processes on the server go to work on that query. The purpose of all these processes is to manage the system such that it will provide your data back to you, or store it, in as timely a manner as possible, whilst maintaining the integrity of the data.
These processes are run for each and every query submitted to the system. While there are lots of different actions occurring simultaneously within SQL Server, we’re going to focus on the processes around T-SQL. They break down roughly into two stages:
1. Processes that occur in the relational engine
2. Processes that occur in the storage engine.
In the relational engine the query is parsed and then processed by the Query Optimizer , which generates an execution plan. The plan is sent (in a binary format) to the storage engine, which it then uses to retrieve or update the underlying data. The storage engine is where processes such as locking, index maintenance and transactions occur. Since execution plans are created in the relational engine, that’s where we’ll be focusing our attention.
Estimated and Actual Execution Plans
there are two distinct types of execution plan. First, there is the plan that represents the output from the optimizer. This is known as an Estimated execution plan. The operators, or steps, within the plan will be labeled as logical, because they’re representative of the optimizer’s view of the plan.
Next is the plan that represents the output from the actual query execution. This type of plan is known, funnily enough, as the Actual execution plan. It shows what actually happened when the query executed.
Execution Plan Reuse
It is expensive for the Server to generate execution plans so SQL Server will keep and reuse plans wherever possible. As they are created, plans are stored in a section of memory called the plan cache ).
When a query is submitted to the server, an estimated execution plan is created by the optimizer. Once that plan is created, and before it gets passed to the storage engine, the optimizer compares this estimated plan to actual execution plans that already exist in the plan cache . If an actual plan is found that matches the estimated one, then the optimizer will reuse the existing plan, since it’s already been used before by the query engine. This reuse avoids the overhead of creating actual execution plans for large and complex queries or even simple plans for small queries called thousands of times in a minute.
Each plan is stored once, unless the cost of the plan lets the optimizer know that a parallel execution might result in better performance (more on parallelism in Chapter 8). If the optimizer sees parallelism as an option, then a second plan is created and stored with a different set of operations to support parallelism. In this instance, one query gets two plans.
Execution plans are not kept in memory forever. They are slowly aged out of the system using an “age” formula that multiplies the estimated cost of the plan by the number of times it has been used (e.g. a plan with a cost of 10 that has been referenced 5 times has an “age” value f of 50). The lazywriter process, an internal process that works to free all types of cache (including plan cache ), periodically scans the objects in the cache and decreases this value by one each time.
If the following criteria are met, the plan is removed from memory:
· More memory is required by the system
· The “age” of the plan has reached zero
· The plan isn’t currently being referenced by an existing connection
Execution plans are not sacrosanct. Certain events and actions can cause a plan to be recompiled. It is important to remember this because recompiling execution plans can be a very expensive operation. The following actions can lead to recompilation of an execution plan:
· Changing the structure or schema of a table referenced by the query
· Changing an index used by the query
· Dropping an index used by the query
· Updating the statistics used by the query
· Calling the function, sp_recompile
· Subjecting the keys in tables referenced by the query to a large number of inserts or deletes
· For tables with triggers, significant growth of the inserted or deleted tables
· Mixing DDL and DML within a single query, often called a deferred compile
· Changing the SET options within the execution of the query
· Changing the structure or schema of temporary tables used by the query
· Changes to dynamic views used by the query
· Changes to cursor options within the query
· Changes to a remote rowset, like in a distributed partitioned view
· When using client side cursors, if the FOR BROWSE options are changed
Since the cache plays such an important role in how execution plans operate, you need a few tools for querying and working with the plan cache . First off, while testing, you may want to see how long a plan takes to compile, or to investigate how minor adjustments might create slightly different plans. To completely clear the cache, run this:
DBCC FREEPROCCACHE
Execution Plan Formats
SQL Server offers only one type of execution plan (be it estimated or actual), but three different formats in which to view that execution plan.
· Graphical Plans
· Text Plans
· XML Plan s
The one you choose will depend on the level of detail you want to see, and on the individual DBA’s preferences and methods.
Graphical Plans
These are quick and easy to read but the detailed data for the plan is masked. Both Estimated and Actual execution plans can be viewed in graphical format.
Text Plans
These are a bit harder to read, but more information is immediately available. There are three text plan formats:
· SHOWPLAN_ALL : a reasonably complete set of data showing the Estimated execution plan for the query
· SHOWPLAN_TEXT : provides a very limited set of data for use with tools like osql.exe . It too only shows the Estimated execution plan
· STATISTICS PROFILE: similar to SHOWPLAN_ALL except it represents the data for the Actual execution plan
XML Plans
XML plans present the most complete set of data available on a plan, all on display in the structured XML format. There are two varieties of XML plan:
· SHOWPLAN_XML : The plan generated by the optimizer prior to execution.
· STATISTICS_XML : The XML format of the Actual execution plan.
