Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shaggy
Posting Yak Master
248 Posts |
 Posted - 2014-01-29 : 03:50:05
|
| Hi,Can I add below SP in a plan guide.If possible could anyone provide sample script for this.create proc SP1(@id int)asbegin update tab1 set col1 = 2 where id = @idupdate tab2 set col1 = 2 where id = @idendThanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
shaggy
Posting Yak Master
248 Posts |
Posted - 2014-01-29 : 04:18:43
|
| Thanks visakh,I tried looking from the link but I'm getting error (Cannot create plan guide 'Guide1' because parameter @stmt has more than one statement.)create table tab1 (col1 int,id int)create table tab2 (col1 int,id int)create proc SP1(@id int)asbegin update tab1 set col1 = 2 where id = @idupdate tab2 set col1 = 2 where id = @idendsp_create_plan_guide @name = N'Guide1',@stmt = N'update tab1 set col1 = 2 where id = @id update tab2 set col1 = 2 where id = @id',@type = N'OBJECT',@module_or_batch = N'dbo.SP1',@params = NULL,@hints = N'OPTION (OPTIMIZE FOR (@id = N''1''))';Msg 10506, Level 16, State 1, Procedure SP1, Line 1Cannot create plan guide 'Guide1' because parameter @stmt has more than one statement. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-29 : 05:15:07
|
| you can create plan guide on a statement. so if you need create separate plan guides for both of them------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Plan guides