数据库技术:SQLServer如何查看存储过程的执行计划

有时候，我们需要查看存储过程的执行计划，那么我们有什么方式获取存储过程的历史执行计划或当前的执行计划呢？ 下面总结一下获取存储过程的执行计划的方法。

1:我们可以通过下面脚本查看存储过程的执行计划，但是有时候，你会发现这种方式并不总是能够获取到存储过程的执行计划。

select

        d.object_id ,

        db_name(d.database_id) dbname ,

        object_name(object_id, database_id) 'spname' ,

        d.cached_time ,

        d.last_execution_time ,

        d.total_elapsed_time/1000000    as total_elapsed_time,

        d.total_elapsed_time / d.execution_count/1000000 

                                        as [avg_elapsed_time] ,

        d.last_elapsed_time/1000000     as last_elapsed_time,

        d.execution_count ,

        d.total_physical_reads ,

        d.last_physical_reads ,

        d.total_logical_writes ,

        d.last_logical_reads ,

        et.text sqltext ,

        eqp.query_plan executionplan

from    sys.dm_exec_procedure_stats as d

cross apply sys.dm_exec_sql_text(d.sql_handle) et

cross apply sys.dm_exec_query_plan(d.plan_handle) eqp

where   object_name(object_id, database_id) = 'xxxx'

order by [total_worker_time] desc;

有时候使用这种方式并不能获取存储过程的执行计划，脚本查询出来的结果，query_plan字段为null值，那么为什么是null值呢？这个是因为有一些限制或条件的缘故，官方文档的解释如下：

under the following conditions, no showplan output is returned in the query_plan column of the returned table for sys.dm_exec_query_plan:

·         if the query plan that is specified by using plan_handle has been evicted from the plan cache, the query_plan column of the returned table is null. for example, this condition may occur if there is a time delay between when the plan handle was captured and when it was used with sys.dm_exec_query_plan.

·         some transact-sql statements are not cached, such as bulk operation statements or statements containing string literals larger than 8 kb in size. xml showplans for such statements cannot be retrieved by using sys.dm_exec_query_plan unless the batch is currently executing because they do not exist in the cache.

·         if a transact-sql batch or stored procedure contains a call to a user-defined function or a call to dynamic sql, for example using exec (string), the compiled xml showplan for the user-defined function is not included in the table returned by sys.dm_exec_query_plan for the batch or stored procedure. instead, you must make a separate call to sys.dm_exec_query_plan for the plan handle that corresponds to the user-defined function.

when an ad hoc query uses simple or forced parameterization, the query_plan column will contain only the statement text and not the actual query plan. to return the query plan, call sys.dm_exec_query_plan for the plan handle of the prepared parameterized query. you can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects view or the text column of the sys.dm_exec_sql_text dynamic management view.

在以下情况下，sys.dm_exec_query_plan的返回表的query_plan列为空值（query_plan列中未返回showplan输出）:

·         通过使用plan_handle查询指定的查询计划(query plan)，如果plan_handle已从计划缓存中踢出（逐出），返回的表的query_plan列为null。 例如，如果在捕获计划句柄与将其与sys.dm_exec_query_plan一起使用之间存在时间延迟，则可能会出现这种情况。

·         有些transact-sql语句不会cached，例如大容量操作语句(bulk operation statements)或包含大于8 kb的字符串大小的sql语句。无法使用sys.dm_exec_query_plan检索此类语句的xml showplans，除非批处理当前正在执行，因为它们不存在于缓存中。

·         如果transact-sql批处理或存储的过程包含对用户定义函数的调用或执行动态sql，例如使用 exec (字符串)，则用户定义函数的已编译xml showplan不包含在返回的表中通过sys.dm_exec_query_plan获取批处理或存储过程。相反，您必须单独调用sys.dm_exec_query_plan以获取与用户定义函数对应的计划句柄。

当即席查询使用简单或强制参数化时，query_plan列将包含仅语句文本，而不是实际查询计划。 若要返回查询计划，请调用sys.dm_exec_query_plan准备参数化查询的计划句柄。 您可以确定查询是否已参数化通过引用sql的列sys.syscacheobjects视图或文本列sys.dm_exec_sql_text动态管理视图。

注意：sys.dm_exec_query_plan返回的是实际执行计划。

2：使用set showplan_all on 和set showplan_xml on获取存储过程的执行计划。

如下所示，在adventureworks2014数据库中，查看存储过程[dbo].[uspgetemployeemanagers] 的执行计划

set showplan_all on

go

set fmtonly on

go

exec dbo].[uspgetemployeemanagers] 242;

go

set fmtonly off

go

set showplan_all off

go

set showplan_all on

go

exec [dbo].[uspgetemployeemanagers] 242;

go

set showplan_all off;

go

set showplan_xml on

go

exec [dbo].[uspgetemployeemanagers] 242;

go

set showplan_xml off;

go

这种方式获取的是存储过程的预估执行计划，并不是实际执行计划，而且这种方式不会执行存储过程。另外，如果存储过程中存在临时表，那么就会出错。出现类似下面这样的错误：

msg 208, level 16, state 0, procedure integritytesting, line 57

invalid object name '#dbtocheck'.

3：ssms中选中执行存储过程的脚本后，使用快捷方式ctrl+l或勾选“display estimated execution plan”获取存储过程的执行计划。   

这种方式其实只是方式2（命令方式）的图形界面操作而已。所以此处不做展开

4：ssms中使用快捷方式ctrl+m或勾选“include actual execution plan”后，执行存储过程后，就能看到实际执行计划

这种方式看到的是存储过程的实际执行计划，但是很多时候，这种方式适用于开发、测试环境，但是我们在生产环境诊断问题时，这种方式并不适用。因为有时候存储过程会有dml操作，会修改数据。

5：set statistics xml 或set statistics profile查看实际执行计划。

set statistics xml on

go

exec [dbo].[uspgetemployeemanagers] 242;

go

set statistics xml off;

go

或

set statistics profile on

go

exec [dbo].[uspgetemployeemanagers] 242;

go

set statistics profile off;

go

这个方式会执行存储过程。所以弊端上面也说过，有时候生产环境并不适用。

7：适用sql server profile跟踪获取存储过程的实际执行计划

1: 打开sql server profiler工具

2：在文件菜单，选择开启一个新的跟踪，跟踪对应的数据库服务器

3：“events section”选项卡中，”use the template“选择“blank”

4： 然后在performance节点，勾选相关字段。“column filter”设置过滤选项。例如只跟踪捕获某个数据库的某个存储过程的执行计划。如下例子所示

总结：我们可以使用上面方式获取存储过程的预估执行计划或实际执行计划。各有利弊，主要看使用场景，如果在生产环境诊断问题，优化sql时，建议使用方式1 或 sql server profiler, 因为有时候预估的执行计划，可能跟实际执行计划有出入，以及参数嗅探问题等等。

参考资料：

需要了解更多数据库技术:SQL Server如何查看存储过程的执行计划，都可以关注数据库技术分享栏目—编程笔记