Query Plan Guide don''t seem to work... Please help

• dave-425393

  Grasshopper

  Points: 19

  More actions

  May 15, 2007 at 3:17 pm

  #172384

  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

• Zubeyir

  SSCrazy

  Points: 2559

  More actions

  May 16, 2007 at 2:26 am

  #706695

  Maybe not directly related but why are you using "%" with the test query. You just give the actual query and let the sql produce the template.

  Zubeyir