SQL Server sp_ExecuteSQL 和执行计划

问题描述

我有一个查询，它在 SQL Server Management Studio 中速度超快，而在 sp_ExecuteSQL 下运行时速度超慢.

I have a query which is super fast in SQL Server Management Studio and super slow when run under sp_ExecuteSQL.

这是否与在 sp_ExecuteSQL 下运行时不会缓存执行计划有关?

Is this to do with caching of execution plans not happening when run under sp_ExecuteSQL?

推荐答案

没有

您可以查看两个执行计划并使用以下查询进行比较.

You can see both execution plans and compare them using the following query.

SELECT usecounts, cacheobjtype, objtype, text, query_plan, value as set_options
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
cross APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
where text like '%Some unique string in your query%'
                                          and attribute='set_options'

sp_executesql 版本的 objtype 是prepared"

The sp_executesql version will have an objtype of "prepared"

这篇关于SQL Server sp_ExecuteSQL 和执行计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！