February 6, 2009 at 5:21 am
Hello, I stuck with creating plan guide. I got following query:
INSERT INTO dbo.table1 (col1, col2, col3)
SELECT col4, col5, col6, 0, 1, 3, 'test'
FROM dbo.table2
WHERE col4=1 AND col5='N'
I would like to create plan guide for that query to use MAXDOP=1.
In past I create only simple plans, but got issue witht he INSERT INTO one.
and also do I have to create two plans, one for the INSERT and another for SELECT in this statement.
Could you please help ?
I got to that point, but I would like to get that plan works for all selects, with other columns ie. 0, 1, 3, ''test''
sp_create_plan_guide @name = N'planguide_test',
@stmt = N'INSERT INTO dbo.table1 (col1, col2, col3)
SELECT col4, col5, col6, 0, 1, 3, ''test''
FROM dbo.table2
WHERE col4=@P1 AND col5=@P2',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@P1 integer,@P2 varchar(1)',
@hints = N'OPTION (MAXDOP 1)'
Do I add that 0, 1, 3, ''test'' as another parameters to it look like that ? or this is wrong!?
sp_create_plan_guide @name = N'planguide_test',
@stmt = N'INSERT INTO dbo.table1 (col1, col2, col3)
SELECT col4, col5, col6, @P3, @P4, @P5, @P6
FROM dbo.table2
WHERE col4=@P1 AND col5=@P2',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@P1 integer,@P2 varchar(1),@P3 integer,@P4 integer,@P5 integer,@P6 varchar(255)',
@hints = N'OPTION (MAXDOP 1)'
February 6, 2009 at 6:32 am
You don't want the second one.
If the procedure is called, does it get the parameters as you outlined or does it get the literal values? Plan guides are extremely picky. Any white space difference, extra carriage returns & line feeds, anything will prevent the guide from being applied.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply