Query Plan Guides don't seem to work

We have a couple of ad hoc queries in our app that we need to force parameterization. There queries can't be put in sp's because they come from a 3rd party app. The goal is to reduce the amount of cache being accumulated. We are on sql server 2005. The queries in question look like this:

select * from TABLE1 where SEQUENCE_NUMBER = '<some literal>'

When i query syscacheobjects i see thousands of compiled plans for that single query with different literal values in the WHERE clause. I want to force parameterization and make it reuse one plan. Seems to best way to do this is to turn FORCED PARAMETERIZATION on for the db or to create plan guides. Both don't seem to be working.

I turned FORCED PARAMETERIZATION on for the db, then cleared proc cache (DBCC FREEPROCCACHE). I query syscacheobjects and it's empty. I run a test script with 10 queries similiar to the one above, passing a different literal into the where clause. I would expect there to be only one compiled plan for the paramitized version of the query but again there are 10 compiled plans for that query.

I then turned SIMPLE PARAMETERIZATION on for the db and created a plan guide for the query:

DECLARE @.stmt nvarchar(max);
DECLARE @.params nvarchar(max);
EXEC sp_get_query_template
N'select * from TABLE1 where SEQUENCE_NUMBER = ''%''',
@.stmt OUTPUT,
@.params OUTPUT;
EXEC sp_create_plan_guide
N'Templat1',
@.stmt,
N'TEMPLATE',
NULL,
@.params,
N'OPTION(PARAMETERIZATION FORCED)';

I again clear the cache and syscacheobjects is empty. I run my test script. This time i can a row for the plan guide BUT there is still 10 rows for the queries in my test script. I expected to just see the row for the plan guide, indicating that it's using that compiled plan for all the queries but this isn't the case.

Has anyone used query plan guides? Is my testing correct, should i expect LESS rows to accumulate in syscacheobjects? I can query sys.plan_guides and see that my plan guide is created and enabled but from my tests it doesn't seem like it's being used when i run my tests. Any advise on how to get this going?

thanks,
Dave

Did you see any entry where cacheobjtype = 'Compiled Plan' and objtype = 'Prepared' and sql = '(@.0 varchar(8000) )select * from TABLE1 where SEQUENCE_NUMBER = @.0'?

If yes, then it is working. Check the number for column [usecounts], it should be 10.

If you are not qualifying the table with the schema / owner, then the plan generated will not be share among multiple users. You can check column [uid] and see if it is different from -2.

AMB

|||thanks for the response hunchback! I AM seeing an entry in syscacheobjects where cacheobjtype = 'Compiled Plan' and objtype = 'Prepared' and sql = '(@.0 varchar(8000) )select * from TABLE1 where SEQUENCE_NUMBER = @.0'

Below are the results of:
select cacheobjtype, objtype, usecounts, sql from syscacheobjects
where sql like '%TABLE1%' order by sql

cacheobjtype objtype usecount sql
Compiled Plan Prepared 10 (@.0 varchar(8000))select * from dbo.TABLE1 where SEQUENCE_NUMBER = @.0
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '1'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '10'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '2'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '3'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '4'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '5'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '6'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '7'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '8'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '9'

Any idea why is it compiling a plan for each query even though it's using the Plan Guide? When i run Profiler and check out the ExplainPlan XML i don't see any references to the TemplatePlan which was also making me think that it wasn't using the plan guide.
|||

What about the [usecounts] associated to the ('Compiled Plan', 'Prepared')?

SQL Server has to compile the Adhoc query anyway, in order to get the query tree and be able to separate the constant values that will be pass to the parameters. Based on the resources available and cost of compilation, SQL Server could save or not those compiled plans.

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Plan Cache Concepts Explained

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/08/plan-cache-concepts-explained.aspx

AMB

|||I'm running into the same behavior as tenatiousd is reporting.

I create the template plan guide and setup tests to watch SQL Server behavior (syscacheobjects). When I execute a SQL statement that matches the plan guide, it seems to be "referencing" the Prepared Plan (refcount goes up), but creates an Ad Hoc compiled plan and (re)uses that instead. Everything else that matters for plan resuse is the same (user id, execution context, qualified table names, etc.), but it doesn't reuse the Prepared Plan defined from the template plan guide. If I change the parameters, it references the Prepared Plan, but compiles a new Ad Hoc plan and continues to reuse the ad hoc plan over and over (as long as the parameters are the same).

The resulting behavior is that the Prepared Plan that matches the SQL statement is not reused and the plan cache would continue to grow with new ad hoc plans being added as the parameter values change.

Why isn't the Prepared Plan being reused? Will the Prepared Plan only be reused if there is enough pressure on the Plan Cache and it can't/won't store the ad hoc plan?
|||

Hi Nickvalo,

What about [usecounts] for the "Prepared" execution plan, is it being incremented?

AMB

|||The first time I execute a SQL statement that has a template plan guide (after issuing a DBCC freeproccache), this is what happens:

1. The usecount for the Prepared Plan goes up by one and it's refcount goes up by 2 (or 3).
2. The usecount for the ad hoc plan goes up by one (a new one gets created for each unique set of parameter values).

Then if I execute that same SQL statement (same parameter values) 5 times in a row (without clearing the plan cache), the usecount for the *ad hoc* plan goes up by 5. So, essentially it appears that the prepared plan is used somehow the first time, but so is an ad hoc plan for that same statement ... then only the ad hoc plan is used when the same parameters are used in the SQL statement. My expectation was that there would not be an ad hoc plan prepared and cached if the template plan guide matched the SQL statement and forced parameterization.
|||

Nickvalo,

I which somebody from the Microsoft SQL Server group, in charge of "plan guides", could answer this question.

My guess is that the cost of generating an execution plan for that statement is low and the resources available are high, so putting the plan in the cache can help. See if you can download a stress tool and do the test with more connections (create more strees, consume more, and reduce resources available).

Support Tools Available For Stress Testing & Performance Analysis

http://www.microsoft.com/downloads/details.aspx?familyid=5691ab53-893a-4aaf-b4a6-9a8bb9669a8b&displaylang=en

AMB

|||

Hello,

maybe this page: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

especially the Appendix A section can help you to find out why there is no parametrisation

regards,

Peter

sql