Multi-Statement Template Plan Guide
2
votes
0
answers
622
views
A piece of, um, software seems to be issuing ~20k queries of this form every few seconds:
DELETE FROM macVendor WHERE companyId=N' 08-00-'; INSERT INTO macVendor (companyId,organization) VALUES (N' 08-00-',N'TEKTRONIX INC.');
I don't know why, and no the values don't change...
Anyway, it really messes up the output of
dm_exec_cached_plans
so I figured I'd create a plan guide to force parameterization :
EXEC sp_create_plan_guide @name = N'[PlanGuide-Stop-The-macVendorMadness]',
@stmt = N'DELETE FROM macVendor WHERE companyId=@0; INSERT INTO macVendor (companyId,organization) VALUES (@0,@1);',
@type = N'TEMPLATE',
@params = N'@0 nvarchar(4000),@1 nvarchar(4000)' ,
@hints = N'OPTION(PARAMETERIZATION FORCED)'
It seems however that plan guides can't be created for multiple statements?
Msg 10506, Level 16, State 1, Line 1
Cannot create plan guide 'PlanGuide-Stop-The-macVendorMadness' because parameter @stmt has more than one statement.
Any hints/help would be much appreciated!
----------
Update: I've created 2 plan guides (DELETE/INSERT) and they *were* used but only ~17k times (each) out of the 110k executions.
Asked by Fowl
(131 rep)
Jan 22, 2014, 01:42 AM
Last activity: Dec 8, 2016, 10:13 PM
Last activity: Dec 8, 2016, 10:13 PM