October 25, 2013 at 2:05 am
Good Day All
We have a issue with a Merge Repl Store proc that is paralyzing and we would like to force it to only use maxdop 1.
We have been told to create a plan guide to do this rather than alter the proc with the hint.
When trying to create a plan guide for the proc (sp_MSenumgenerations90) i get an error.
"Cannot create plan guide because the module 'sys.sp_MSenumgenerations90' does not exist or you do not have needed permission."
Has anyone managed to do this before?
It will take too long to explain why we doing this but bottom line is we cant change server maxdop for this one query and we "should not" change a system proc.
Regards
October 25, 2013 at 5:47 am
I've never tried creating a plan guide for a system procedure. I'm not sure it will work.
But, the error suggests you might not be trying to create it in the right database. Check the location of the proc then try creating it again.
"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
October 25, 2013 at 7:13 am
Thanks for response Grant.
I have double checked everything but i think it boils down to the fact that it is a system store proc and if you try select the object from sys.objects then you cant query it either.
It is there (you can go right click modify on it to alter it) but you can only query the normal non system stored proc's.
Think this might just be a hard limitation on plan guides that you cant create one for a system stored proc.
Regards
October 28, 2013 at 2:43 am
Does anyone else have any ideas on how to Force a system SP to use maxdop 1 without changing the server maxdop or altering the proc with hint?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply