主页 / dba / 问题 / 130413
Accepted
Limonka
Asked: 2016-02-26 00:44:54 +0800 CST

修复了存储过程的查询计划

  • 772

我正在探索计划指南主题。听起来很简单，只要我们浏览文档中的示例。

问题是我找不到任何工作示例（我自己也没有设法弄清楚）为存储过程或过程中的语句设置固定计划。

我已经阅读了很多博客文章，但它们都只是从微软文档中复制/粘贴示例。

任何人更有创意/经验丰富？

sql-server execution-plan

2 个回答

  1. Best Answer

    下面是一个使用计划指南为过程中的语句强制执行计划的示例：

    USE tempdb;
GO

CREATE PROCEDURE dbo.test
AS
BEGIN

    SET NOCOUNT ON;

    SELECT svp.name AS login_name, dbp.name AS user_name
    FROM sys.database_principals AS dbp
    INNER JOIN sys.server_principals AS svp
        ON dbp.sid = svp.sid;

END
GO

-- Use Ctrl+M to capture the execution plan
-- You will see loop joins used
EXEC dbo.test;

    现在使用强制连接策略生成此语句的执行计划：

    SELECT svp.name AS login_name, dbp.name AS user_name
FROM sys.database_principals AS dbp
INNER JOIN sys.server_principals AS svp
    ON dbp.sid = svp.sid
OPTION (HASH JOIN);

    复制执行计划 XML 并将其粘贴到查询窗口。用（四个单引号）替换任何出现的'（单引号）。''''

    sp_create_plan_guide 过程的最终参数将采用以下形式：

    @xmlPlan = 'OPTION (USE PLAN N''your plan goes here'')'

    现在您可以看到为什么要使用四个引号：转义一次以将 XML 放入字符串文字中，转义两次以将 XML 放入另一个字符串文字中的字符串文字中。

    确保将 XML 计划粘贴在 之后OPTION (USE PLAN N''：任何空格或换行符都会使其无效。

    DECLARE @planXML nvarchar(max) = 'OPTION (USE PLAN N''your modified XML plan here'')';


EXEC sp_create_plan_guide 
    @name = 'UseHash', 
    @stmt = 'SELECT svp.name AS login_name, dbp.name AS user_name
    FROM sys.database_principals AS dbp
    INNER JOIN sys.server_principals AS svp
        ON dbp.sid = svp.sid;',
    @type = 'OBJECT',
    @module_or_batch = 'dbo.test',
    @hints = @planXML
GO

-- Use Ctrl+M to capture the execution plan
-- You will see hash joins used
EXEC dbo.test;
    • 3

  2. 在深入挖掘之后，我在 Itzik Ben-Gans 等人中找到了替代解决方案。T-SQL 编程书籍。他的解决方案使用sp_create_plan_guide_from_handle。

    IF OBJECT_ID('dbo.GetOrders', 'P') IS NOT NULL DROP PROC dbo.GetOrders;
GO

CREATE PROC dbo.GetOrders @odate AS DATETIME
AS
    SELECT SalesOrderID, CustomerID, OrderDate /* 33145f87-1109-4959-91d6 */
    FROM Sales.SalesOrderHeader
    WHERE OrderDate >= @odate;
GO

EXEC dbo.GetOrders '99991231';
GO

DECLARE @plan_handle VARBINARY(64), @offset INT, @rc INT;
SELECT @plan_handle = plan_handle, @offset = statement_start_offset
FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1 )
    LIKE N'%SELECT SalesOrderID, CustomerID, OrderDate /* 33145f87-1109-4959-91d6 */%' AND st.text NOT LIKE '%sys%';

SET @rc = @@ROWCOUNT;

IF @rc = 1
    EXEC sp_create_plan_guide_from_handle
        @name = N'MySecondPlanFromProc',
        @plan_handle = @plan_handle,
        @statement_start_offset = @offset;
ELSE
    RAISERROR ('Number of matching plans should be 1 but is %d. Plan guide not created.', 16, 1, @rc);
GO
SET SHOWPLAN_XML ON;
GO
EXEC dbo.GetOrders '99991231';
GO
SET SHOWPLAN_XML OFF;
GO
    • 0

相关问题