主页 / dba / 问题 / 54782
Accepted
db2
Asked: 2013-12-11 06:58:37 +0800 CST

为通过 sp_executesql 调用的查询创建计划指南

  • 772

长话短说，我有一个名为 vwRelatives 的视图，它使用 CTE 递归来构建家谱。它意味着一次查询一个人。

这大约在四分之一秒内运行：

SELECT * FROM vwRelatives WHERE person_id = 5

这（从应用程序执行查询的方式）大约需要 4.5 秒：

exec sp_executesql N'SELECT * FROM vwRelatives WHERE person_id = @P1',N'@P1 int',5

（请注意，我已经稍微简化了查询。真实的东西有一个明确的列列表和一个ORDER BY，但WHERE语义是相同的。我在任何一个版本中都得到相同的症状。）

person_id = 5最有可能的是，SQL Server在为第一个查询创建执行计划时能够考虑到，但是参数化它会导致整个视图运行，然后按 person_id 过滤。

所以我想我会创建一个计划指南。现在我有两个问题。

这些是我正在采取的步骤，似乎没有效果。

首先，运行“好”查询以将其放入计划缓存中...

SELECT * FROM vwRelatives WHERE person_id = 5

...然后执行标准步骤将其变成计划指南...

--Get the 'good' plan
SET @xml_showplan = (
    SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs 
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
        CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
        WHERE st.text LIKE N'SELECT * FROM vwRelatives WHERE person_id = 5'
)

--Apply a plan guide to the meat of the sp_executesql query
EXEC sp_create_plan_guide 
    @name = N'vwRelatives_Test_Plan_Guide', 
    @stmt = N'SELECT * FROM vwRelatives WHERE person_id = @P1', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = N'@P1 int', 
    @hints = @xml_showplan;

这成功完成，但是当我再次运行原来的 sp_executesql 语句时，仍然需要 4.5 秒。我正在运行 Profiler，并且选择了“计划指南成功”和“计划指南不成功”事件。这些事件都没有出现在跟踪中。

我做错了什么导致 SQL Server 无法将此计划指南视为 sp_executesql 查询的匹配项？

sql-server sql-server-2012

1 个回答

  1. Best Answer

    对于那些在尝试创建计划指南时遇到这个问题的人来说，我最初的语法是正确的。它不起作用的原因（我怀疑 - 我在文档中找不到任何确认）是视图使用 CTE 进行递归。显然，这使其无法使用计划指南。

    我最初的问题是，当SELECT通过带有参数的 sp_executesql 发出语句（客户端是 Access 数据库）时，递归视图的性能很差。

    我终于在 Stack Overflow 上遇到了这个老问题，有人遇到了基本相同的问题：

    https://stackoverflow.com/questions/4226035/why-does-a-query-slow-down-drastical-if-in-the-where-clause-a-constant-is-repl

    我开始怀疑是否需要通过将递归推送到用户定义的函数来欺骗/帮助查询优化器，这证实了怀疑。我将视图中的所有 CTE 移动到一个内联 UDF 中，该 UDF@person_id直接在递归的锚点中使用参数，现在即使使用 sp_executesql 也快得令人愉快。

    所以，不是我最初认为我需要的解决方案，但我会接受它。（这种方式也可能更直接。我不必担心将计划指南附加到 Access 可能构建的查询的每个细微变化中。）

    • 5

相关问题