Creating plan guide for query called through sp_executesql

5 votes

1 answer

1742 views

sql-server sql-server-2012 plan-guides

                          To make a long story short, I have a view called vwRelatives which uses CTE recursion to build family trees. It's meant to be queried for a single person at a time.

This runs in about a quarter second:

    SELECT * FROM vwRelatives WHERE person_id = 5

This (the way the query is executed from the application) takes more like 4.5 seconds:

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

(Note that I've simplified the query a bit. The real thing has an explicit column list and an ORDER BY, but the WHERE semantics are the same. I get the same symptoms with either version.)

Most likely, SQL Server is able to take person_id = 5 into account when creating an execution plan for the first query, but parameterizing it is causing the whole view to be run and *then* filtered by person_id.

So I figured I'd create a plan guide. And now I have two problems.

These are the steps I'm taking, which appear to have no effect.

First, run the 'good' query to get it into the plan cache...

    SELECT * FROM vwRelatives WHERE person_id = 5

...then perform the standard steps to turn it into a plan guide...

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

This completes successfully, but when I run the original sp_executesql statement again, it still takes 4.5 seconds. I have Profiler running, and both the Plan Guide Successful and Plan Guide Unsuccessful events are selected. Neither of those events ever shows up in the trace.

What am I doing wrong that's preventing SQL Server from seeing this plan guide as a match for the sp_executesql query?

                        

Asked by db2 (9708 rep)

Dec 10, 2013, 02:58 PM
Last activity: Dec 8, 2016, 10:14 PM

Creating plan guide for query called through sp_executesql

Related Questions