Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

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 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 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 at 3:39
    
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 at 4:10

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.