Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
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
The question needs more detail (e.g. table & index definitions, cached plans query and output, *.sqlplan file) so we can see exactly what you're talking about. I would expect the simple DELETE statement to be simple-parameterized and reused. If the values don't change, what are the extra cached plans? Makes little sense to me as currently stated. – Paul White Jan 22 '14 at 3:39
This is the table and query I'm using - I'm not sure the best place to put the output? – Fowl Jan 22 '14 at 4:10

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.