有时候,我们需要查看存储过程的执行计划,那么我们有什么方式获取存储过程的历史执行计划或当前的执行计划呢? 下面总结一下获取存储过程的执行计划的方法。
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 executionplanFROM sys.dm_exec_procedure_stats AS dCROSS APPLY sys.dm_exec_sql_text(d.sql_handle) etCROSS APPLY sys.dm_exec_query_plan(d.plan_handle) eqpWHERE OBJECT_NAME(object_id, database_id) = 'xxxx'ORDER BY [total_worker_time] DESC;