Can MAXDOP be set for just a stored procedure?

Question

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

Answer 1

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.

Answer 2

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.