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)' ,

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.

  • You have two statements there. You need to create a plan guide for each statement (though I'm not sure one will be necessary for the insert, on the other hand I don't know what "messes up" means). And you also need to go back to the vendor of the software and give them the 'ol WTF. – Aaron Bertrand Jan 22 '14 at 1:47
  • I tried two separate plans, but they didn't seem to be used. Messes up = 22k cached query plans just moments after DBCC FREEPROCCACHE. – Fowl Jan 22 '14 at 2:09
  • This is the table and query I'm using pastebin.com/K0Cd9Erq - I'm not sure the best place to put the output? – Fowl Jan 22 '14 at 4:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.