Force a plan using a plan guide

• PHXHoward

  SSCrazy Eights

  Points: 8389

  More actions

  May 19, 2016 at 9:59 am

  #318902

  We have a single select query with 32,663 single use plans in the plan cache. Apparently it is not reusing the plan ever. This is making a lot of procedure cache bloat.

  Example

  select * from APTRANS where TCODE = '2000' and MERCID = '123456' and RETURN_CODE = '0' and RESPCODE = 'C' and EXT = 'acb123abc' and AMOUNT = 500.50 and ORDER_NUMBER = '701234' and SEQUENCE_NUMBER <> '000002334344' and TRANS_DATE_TIME > {ts '2016-05-08 15:30:03'}

  I can see a few reasons that plan reuse might not be happening.

  Using SELECT * instead of the column names

  Not putting the schema name before the table name in the query

  Assuming it can't be fixed because it is a vendor database, would a plan guide possibly force plan reuse?

  When I run it though sp_get_query_template, it paramaterizes the first 8 conditions but leaves the last one "TRANS_DATE_TIME" as it is. Is that because of the {ts } in the last one?

  Is it possible that a plan guide with OPTION(PARAMETERIZATION FORCED) is a good way to address this issue?

  Thanks

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  May 19, 2016 at 11:02 am

  #1880315

  PHXHoward (5/19/2016)

  ---

  We have a single select query with 32,663 single use plans in the plan cache. Apparently it is not reusing the plan ever. This is making a lot of procedure cache bloat.

  Example

  select * from APTRANS where TCODE = '2000' and MERCID = '123456' and RETURN_CODE = '0' and RESPCODE = 'C' and EXT = 'acb123abc' and AMOUNT = 500.50 and ORDER_NUMBER = '701234' and SEQUENCE_NUMBER <> '000002334344' and TRANS_DATE_TIME > {ts '2016-05-08 15:30:03'}

  I can see a few reasons that plan reuse might not be happening.

  Using SELECT * instead of the column names

  Not putting the schema name before the table name in the query

  Assuming it can't be fixed because it is a vendor database, would a plan guide possibly force plan reuse?

  When I run it though sp_get_query_template, it paramaterizes the first 8 conditions but leaves the last one "TRANS_DATE_TIME" as it is. Is that because of the {ts } in the last one?

  Is it possible that a plan guide with OPTION(PARAMETERIZATION FORCED) is a good way to address this issue?

  Thanks

  The plan isn't being reused because it is textual. ANY character difference will lead to a different plan.

  I would try the forced parameterization route, and/or manually create your plan guide.

  https://msdn.microsoft.com/en-us/library/ms179880.aspx

  https://msdn.microsoft.com/en-us/library/ms191275.aspx

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• PHXHoward

  SSCrazy Eights

  Points: 8389

  More actions

  May 19, 2016 at 11:28 am

  #1880317

  Thanks Kevin for responding. I'm not sure what is meant by textual.

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  May 19, 2016 at 11:54 am

  #1880325

  PHXHoward (5/19/2016)

  ---

  Thanks Kevin for responding. I'm not sure what is meant by textual.

  A string of text. Each WHERE clause element can change with a different value for every query. Even a single space difference between the text of statements like that will result in a different query plan being stored in the plan cache with normal SQL Server settings.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• PHXHoward

  SSCrazy Eights

  Points: 8389

  More actions

  May 19, 2016 at 12:54 pm

  #1880337

  I see! Because it is ad hoc it is compiling a new plan each time it runs.

  I have created a template from the query but the last component, TRANS_DATE_TIME > {ts '2016-05-09 15:37:03'} is not being parameterized. That might be why SQL Server is not using the guide.

  EDIT: Yes confirmed, after removing the TRANS_DATE_TIME > {ts '2016-05-09 15:37:03'} from the query, it does use the plan guide. Unfortunately the query needs that part.