September 20, 2010 at 10:16 am
Can I not use OPTION (MAXDOP #) inside a view? I am having a little trouble finding examples seaching on the web for an answer because of the common words OPTION and VIEW mean other things.
I can run a query fine with the OPTION (MAXDOP #), but when I try to turn it into a view, I get a syntax error near the keyword 'OPTION'.
Am I just missing something obvious?
Thank in advance for any responses to this post.
September 20, 2010 at 2:25 pm
Doesn't appear that you can. You would need to apply the MAXDOP to the query referencing the view.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 27, 2010 at 8:42 am
That's what I figured. How could I setup MAXDOP on a query that a user runs? I was just going to setup a view on the table that was basically SELECT * FROM table OPTION (MAXDOP 4), but obviously no.
September 27, 2010 at 4:19 pm
I'm not sure you can. If you have the Enterprise version of SQL Server you could use the resource governor to set the MAXDOP for the users connection, but that would impact all of their queries, not just the ones for a particular view.
September 28, 2010 at 5:47 am
It depends whether they literally select from that view on it's own using the same statement each time or whether they also join it to other objects/change the columns they are selecting
If the query they are running is static, I believe you should be able to create a plan guide that's non-parallel:
September 29, 2010 at 10:18 pm
HowardW (9/28/2010)
If the query they are running is static, I believe you should be able to create a plan guide that's non-parallel:
He didn't want a non-parallel plan, he just wants one that only uses 4 CPUs. Is that part of the plan i.e. if you capture the plan running the query with MAXDOP 4, would that work? Or will a parallel plan just use the most CPUs that it is allowed based on the restrictions?
September 30, 2010 at 6:00 am
UMG Developer (9/27/2010)
I'm not sure you can. If you have the Enterprise version of SQL Server you could use the resource governor to set the MAXDOP for the users connection, but that would impact all of their queries, not just the ones for a particular view.
This is definitely what I need (to learn how to do). I will look into the resource governor. Thanks for all of your responses to this post.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply