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?

  • Sure your query text is exactly the same? It works for me SSMS Plan Properties screenshot if I do CREATE VIEW [dbo].[vwRelatives] AS SELECT *, number as person_id FROM master..spt_values then follow the steps in your question and exec sp_executesql N'SELECT * FROM vwRelatives WHERE person_id = @P1',N'@P1 int',5. Also what edition are you on? – Martin Smith Dec 10 '13 at 15:14
  • Yeah, you're right; it works fine for me too when I try that. I suspect the use of recursion in the view is making it ineligible for plan guide use (I suppose I can sort of see why that might be the case). We're on Standard Edition, by the way. – db2 Dec 10 '13 at 15:31

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:


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

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.