如何在 SQL Server 中获取查询执行计划? [英] How do I obtain a Query Execution Plan in SQL Server?
问题描述
在 Microsoft SQL Server 中，如何获取查询/存储过程的查询执行计划?
In Microsoft SQL Server how can I get a query execution plan for a query / stored procedure?
推荐答案
有多种获取执行计划的方法，使用哪一种取决于您的情况.通常您可以使用 SQL Server Management Studio 来获取计划，但是如果由于某种原因您无法在 SQL Server Management Studio 中运行您的查询，那么您可能会发现能够通过 SQL Server Profiler 或通过检查来获取计划会很有帮助计划缓存.
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.
SQL Server 带有几个简洁的功能，可以很容易地捕获执行计划，只需确保勾选包括实际执行计划"菜单项(位于查询"菜单下)并运行您的正常查询.
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:
exec p_Example 42
查询完成后，您应该会在结果窗格中看到一个名为执行计划"的额外选项卡.如果您运行了许多语句，那么您可能会在此选项卡中看到许多计划.
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.
从这里您可以在 SQL Server Management Studio 中检查执行计划，或右键单击该计划并选择将执行计划另存为..."以将计划以 XML 格式保存到文件中.
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.
此方法与方法 1 非常相似(实际上这是 SQL Server Management Studio 内部所做的)，但是为了完整性或如果您没有可用的 SQL Server Management Studio，我将其包含在内.
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:
SET SHOWPLAN_TEXT ON
SET SHOWPLAN_ALL ON
SET SHOWPLAN_XML ON
SET STATISTICS PROFILE ON
SET STATISTICS XML ON -- The is the recommended option to use
这些是连接选项，因此您只需为每个连接运行一次.从这一点开始，所有运行的语句都将伴随一个附加结果集，其中包含所需格式的执行计划 - 只需像往常一样运行查询即可查看计划.
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:
SET <<option>> OFF
执行计划格式对比
除非您有强烈的偏好，否则我的建议是使用 STATISTICS XML 选项.此选项等效于 SQL Server Management Studio 中的包括实际执行计划"选项，并以最方便的格式提供最多的信息.
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- 显示基于文本的基本估计执行计划，不执行查询SHOWPLAN_ALL- 显示基于文本的估计执行计划和成本估计，而不执行查询SHOWPLAN_XML- 显示基于 XML 的估计执行计划和成本估计，而不执行查询.这相当于 SQL Server Management Studio 中的显示估计的执行计划..."选项.STATISTICS PROFILE- 执行查询并显示基于文本的实际执行计划.STATISTICS XML- 执行查询并显示基于 XML 的实际执行计划.这相当于 SQL Server Management Studio 中的包括实际执行计划"选项.
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.
如果你不能直接运行你的查询(或者你的查询在你直接执行时不会运行缓慢——记住我们想要一个执行不佳的查询计划)，那么你可以使用 SQL Server Profiler 捕获一个计划痕迹.这个想法是在捕获Showplan"事件之一的跟踪正在运行时运行您的查询.
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.
请注意，根据负载，您可以在生产环境中使用此方法，但显然您应该谨慎使用.SQL Server 分析机制旨在最大限度地减少对数据库的影响，但这并不意味着不会对性能产生任何影响.如果您的数据库被大量使用，您也可能在过滤和识别跟踪中的正确计划时遇到问题.您显然应该咨询您的 DBA，看看他们是否对您在他们宝贵的数据库上执行此操作感到满意！
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!
- 打开 SQL Server Profiler 并创建一个新的跟踪，该跟踪连接到您希望记录跟踪的所需数据库.
- 在事件选择"选项卡下，选中显示所有事件"，选中性能"->显示计划 XML"行并运行跟踪.
- 在跟踪运行时，执行任何您需要执行的操作以使运行缓慢的查询运行.
- 等待查询完成并停止跟踪.
- 要保存跟踪，请右键单击 SQL Server Profiler 中的计划 xml，然后选择提取事件数据..."以将计划以 XML 格式保存到文件中.
您获得的计划相当于 SQL Server Management Studio 中的包括实际执行计划"选项.
The plan you get is equivalent to the "Include Actual Execution Plan" option in SQL Server Management Studio.
如果您无法直接运行查询，也无法捕获分析器跟踪，那么您仍然可以通过检查 SQL 查询计划缓存来获取估计计划.
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.
我们通过查询 SQL Server DMV 来检查计划缓存.以下是一个基本查询，它将列出所有缓存的查询计划(作为 xml)及其 SQL 文本.在大多数数据库中，您还需要添加额外的过滤子句以将结果过滤到您感兴趣的计划.
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.
SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
执行此查询并单击计划 XML 以在新窗口中打开计划 - 右键单击并选择将执行计划另存为..."以将计划以 XML 格式保存到文件中.
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.
因为涉及的因素太多(从表和索引架构到存储的数据和表统计信息)，您应该总是尝试从您感兴趣的数据库中获取执行计划in(通常是遇到性能问题的那个).
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.
实际执行计划是 SQL Server 实际运行查询的执行计划，而 估计 执行计划 SQL Server 计算出它将会不执行查询.尽管在逻辑上是等效的，但实际执行计划更为有用，因为它包含有关执行查询时实际发生的情况的其他详细信息和统计信息.这对于诊断 SQL Server 估计值不正确的问题(例如统计数据过时时)至关重要.
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).
这是一个值得(免费)book 本身.
This is a topic worthy enough for a (free) book in its own right.
这篇关于如何在 SQL Server 中获取查询执行计划?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
