Force a plan using a plan guide

Question

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

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 · Answer 2

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

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 · Answer 4

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.