6

I am exploring the plan guides topic. Sounds easy, as far as we go through the examples in documentation.

The problem is that I cannot find any working example (nor did I manage to figure it out by myself) for setting a fixed plan for a stored procedure, or a statement inside the procedure.

I've read lots of blog posts, but all of them just copy/paste examples from microsoft docs.

Anyone more creative/expirienced?

3

Here is an example of forcing a plan for a statement inside a procedure using a plan guide:

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;

Now generate the execution plan for this statement with a forced join strategy:

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);

Copy the execution plan XML and paste it to the query window. Replace any occurence of ' (single quote) with '''' (four single quotes).

Your final parameter for the sp_create_plan_guide procedure will be in the form:

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

Now you can see why four quotes: escape once to put the XML inside a string literal, escape twice to put the XML inside a string literal inside another string literal.

Make sure that the XML plan is pasted right after OPTION (USE PLAN N'' : any whitespace or newline will make it invalid.

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;
  • I'm not asking for setting a hint, but a fixed query plan, from either xml or a plan handle. – Limonka Feb 25 '16 at 9:17
  • @Limonka changed my answer based on your input – spaghettidba Feb 25 '16 at 9:46
  • @spaghettidba You can just put the plan XML in the hint argument of sp_create_plan_guide, no need to wrap it in OPTION ( USE PLAN ..., replace single quotes with double quotes only eg EXEC sp_create_plan_guide @hints = N'<ShowPlanXML ... '. Can post a more complete example if required. – wBob Feb 26 '16 at 10:48
  • 1
    @wBob I knew it was possible, but I couldn't get it to work yesterday. Now, seeing your comment I found the reason: you have to get rid of the first line of the plan XML: <?xml version="1.0" encoding="utf-16"?>. Interestingly enough, with USE PLAN it works, but using the plan directly doesn't accept the XML tag. I wonder if this is documented somewhere. – spaghettidba Feb 26 '16 at 11:03
0

Having digged a little more I have found alternative solution in Itzik Ben-Gans et.al. T-SQL Programming book. His solution uses 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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.