In Microsoft SQL Server how can I get a query execution plan for a query / stored procedure?
相关问题
- SQL join to get the cartesian product of 2 columns
- sql execution latency when assign to a variable
- Difference between Types.INTEGER and Types.NULL in
- What is the best way to cache a table from a (SQL)
- php PDO::FETCH_ASSOC doesnt detect select after ba
There are a number of methods of obtaining an execution plan, which one to use will depend on your circumstances. Usually you can use SQL Server Management Studio to get a plan, however if for some reason you can't run your query in SQL Server Management Studio then you might find it helpful to be able to obtain a plan via SQL Server Profiler or by inspecting the plan cache.
Method 1 - Using SQL Server Management Studio
SQL Server comes with a couple of neat features that make it very easy to capture an execution plan, simply make sure that the "Include Actual Execution Plan" menu item (found under the "Query" menu) is ticked and run your query as normal.
If you are trying to obtain the execution plan for statements in a stored procedure then you should execute the stored procedure, like so:
When your query completes you should see an extra tab entitled "Execution plan" appear in the results pane. If you ran many statements then you may see many plans displayed in this tab.
From here you can inspect the execution plan in SQL Server Management Studio, or right click on the plan and select "Save Execution Plan As ..." to save the plan to a file in XML format.
Method 2 - Using SHOWPLAN options
This method is very similar to method 1 (in fact this is what SQL Server Management Studio does internally), however I have included it for completeness or if you don't have SQL Server Management Studio available.
Before you run your query, run one of the following statements. The statement must be the only statement in the batch, i.e. you cannot execute another statement at the same time:
These are connection options and so you only need to run this once per connection. From this point on all statements run will be acompanied by an additional resultset containing your execution plan in the desired format - simply run your query as you normally would to see the plan.
Once you are done you can turn this option off with the following statement:
Comparison of execution plan formats
Unless you have a strong preference my recommendation is to use the
STATISTICS XML
option. This option is equivalent to the "Include Actual Execution Plan" option in SQL Server Management Studio and supplies the most information in the most convenient format.SHOWPLAN_TEXT
- Displays a basic text based estimated execution plan, without executing the querySHOWPLAN_ALL
- Displays a text based estimated execution plan with cost estimations, without executing the querySHOWPLAN_XML
- Displays an XML based estimated execution plan with cost estimations, without executing the query. This is equivalent to the "Display Estimated Execution Plan..." option in SQL Server Management Studio.STATISTICS PROFILE
- Executes the query and displays a text based actual execution plan.STATISTICS XML
- Executes the query and displays an XML based actual execution plan. This is equivalent to the "Include Actual Execution Plan" option in SQL Server Management Studio.Method 3 - Using SQL Server Profiler
If you can't run your query directly (or your query doesn't run slowly when you execute it directly - remember we want a plan of the query performing badly), then you can capture a plan using a SQL Server Profiler trace. The idea is to run your query while a trace that is capturing one of the "Showplan" events is running.
Note that depending on load you can use this method on a production environment, however you should obviously use caution. The SQL Server profiling mechanisms are designed to minimize impact on the database but this doesn't mean that there won't be any performance impact. You may also have problems filtering and identifying the correct plan in your trace if your database is under heavy use. You should obviously check with your DBA to see if they are happy with you doing this on their precious database!
The plan you get is equivalent to the "Include Actual Execution Plan" option in SQL Server Management Studio.
Method 4 - Inspecting the query cache
If you can't run your query directly and you also can't capture a profiler trace then you can still obtain an estimated plan by inspecting the SQL query plan cache.
We inspect the plan cache by querying SQL Server DMVs. The following is a basic query which will list all cached query plans (as xml) along with their SQL text. On most database you will also need to add additional filtering clauses to filter the results down to just the plans you are interested in.
Execute this query and click on the plan XML to open up the plan in a new window - right click and select "Save execution plan as..." to save the plan to file in XML format.
Notes:
Because there are so many factors involved (ranging from the table and index schema down to the data stored and the table statistics) you should always try to obtain an execution plan from the database you are interested in (normally the one that is experiencing a performance problem).
You can't capture an execution plan for encrypted stored procedures.
"actual" vs "estimated" execution plans
An actual execution plan is one where SQL Server actually runs the query, whereas an estimated execution plan SQL Server works out what it would do without executing the query. Although logically equivalent, an actual execution plan is much more useful as it contains additional details and statistics about what actually happened when executing the query. This is essential when diagnosing problems where SQL Servers estimations are off (such as when statistics are out of date).
How do I interpret a query execution plan?
This is a topic worthy enough for a (free) book in its own right.
See also:
Beside the methods described in previous answers, you can also use a free execution plan viewer and query optimization tool ApexSQL Plan (which I’ve recently bumped into).
You can install and integrate ApexSQL Plan into SQL Server Management Studio, so execution plans can be viewed from SSMS directly.
Viewing Estimated execution plans in ApexSQL Plan
Viewing Actual execution plans in ApexSQL Plan
To view the Actual execution plan of a query, continue from the 2nd step mentioned previously, but now, once the Estimated plan is shown, click the “Actual” button from the main ribbon bar in ApexSQL Plan.
Once the “Actual” button is clicked, the Actual execution plan will be shown with detailed preview of the cost parameters along with other execution plan data.
More information about viewing execution plans can be found by following this link.
In addition to the comprehensive answer already posted sometimes it is useful to be able to access the execution plan programatically to extract information. Example code for this is below.
Example
StartCapture
DefinitionExample
StopCapture
DefinitionHere's one important thing to know in addition to everything said before.
Query plans are often too complex to be represented by the built-in XML column type which has a limitation of 127 levels of nested elements. That is one of the reasons why sys.dm_exec_query_plan may return
NULL
or even throw an error in earlier MS SQL versions, so generally it's safer to use sys.dm_exec_text_query_plan instead. The latter also has a useful bonus feature of selecting a plan for a particular statement rather than the whole batch. Here's how you use it to view plans for currently running statements:The text column in the resulting table is however not very handy compared to an XML column. To be able to click on the result to be opened in a separate tab as a diagram, without having to save its contents to a file, you can use a little trick (remember you cannot just use
CAST(... AS XML)
), although this will only work for a single row:My favourite tool for obtaining and deeply analyzing query execution plans is SQL Sentry Plan Explorer. It's much more user-friendly, convenient and comprehensive for the detail analysis and visualization of execution plans than SSMS.
Here is a sample screen shot for you to have an idea of what functionality is offered by the tool:
It's only one of the views available in the tool. Notice a set of tabs to the bottom of the app window, which lets you get different types of your execution plan representation and useful additional information as well.
In addition, I haven't noticed any limitations of its free edition that prevents using it on a daily basis or forces you to purchase the Pro version eventually. So, if you prefer to stick with the free edition, nothing forbids you from doing so.
UPDATE: (Thanks to Martin Smith) Plan Explorer now is free! See http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view for details.