Sample Header Ad - 728x90

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