Menu
I have a stored procedure that contains many SELECT commands.
Instead of putting OPTION (MAXDOP 8) at the end of every select command, is there a way I can set it at the start of the stored procedure and remove it at the end, without actually setting it on the server?
ex.
sp_configure 'max degree of parallelism', 4
go
reconfigure with override
924
Setting MAXDOP to 2, 4, or 8 generally provides the best results in most use cases. We recommend that you test your workload and monitor for any parallelism-related wait types such as CXPACKET .
The MAXDOP option is utilized to powers the maximum level of parallelism value at the individual T-SQL statement. MAXDOP value cannot be the same as the SQL Server Degree of Parallelism parameter value. Actually, it is more helpful when additional CPU resources are required to execute the specific SQL statement.
Simply open the database properties dialog in the UI from Object Explorer and navigate to the Options tab. Once on the Options tab you'll see a new section that holds configuration settings for MAXDOP, Legacy Cardinality Estimation, Parameter Sniffing, and Query Optimizer Fixes.
There is no way to "calculate" the best MAXDOP setting for your environment. This is a configuration setting which is unique to every usage. Unless you are actually having an issue, I do not recommend changing it from the default value of 0.
I wish there was a way to set MAXDOP for the scope of the proc but I agree there is no way to do this.
To counter some of the advice above, there are great reasons for using the MAXDOP hint, such as for after hours index builds, updates or data builds in an multi processor environment, but where MAXDOP is set lower than the processor count to help balance activity during busy "peak" hours, such as normal business hours. Unlike some hints, such as index hints, this will not force your processes to use more threads, but it will enable it to do so if the plan so chooses.
Some index builds will execute nearly twice as fast if you double the threads, so there is a genuine benefit.
135
Sadly, no.
Your options are either set it at the server level using
sp_configure 'max degree of parallelism', or update each SELECT statement in your stored procedure to use OPTION (MAXDOP 8).
That said, query options should be a last resort and if your queries are performing poorly, there may be an underlying problem.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
