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.