Creating plan guide for query called through sp_executesql

Question

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?

Answer 1

For those stumbling upon this question trying to create a plan guide, the syntax I had originally is correct. The reason it wasn't working (I suspect - I can't find any confirmation in the documentation) is that the view uses a CTE for recursion. Evidently this disqualifies it from plan guide use.

My original problem was that the recursive view performed poorly when a SELECT statement was issued via sp_executesql with parameters (the client is an Access database).

I finally came across this older question on Stack Overflow where somebody is having basically the same problem:

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

I was kind of beginning to wonder if I would need to trick/help the query optimizer by pushing the recursion out to a user-defined function, and this confirmed the suspicion. I moved all the CTEs from the view into an inline UDF that uses the @person_id parameter directly in the anchor of the recursion, and now it's pleasantly fast even with sp_executesql.

So, not the solution I originally thought I was going to need, but I'll take it. (It's probably more straight-forward this way, too. I don't have to worry about attaching a plan guide to every subtle variation of the query that Access might construct.)