Can MAXDOP be set for just a stored procedure?

Question

I have a stored procedure that contains many 'SELECT' comands.

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

Thank you for your help in advance.

Answer 1

I ran this to set parrallelism to 1 for everything

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

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.

Answer 3

Luckily no. The recommended way of doing such things is to not do them at all. Overwriting query should be only the rare exception when SQL Server truly picks a bad plan, not the rule. And when a query plan has to be enforced, the proper action is to use query plan guides:

• Understanding Plan Guides
• Designing and Implementing Plan Guides

The most important thing to understand is that query hints are design time options (they require code changes to modify the queries) and abusing them greatly reduces the future maintainability of the application (when hardware changes, or on different deployment sites, the hint will be wrong). By contrast query plan guides are deployment time options, they do not require code changes and can be customized on each deployed site, according to the on-site hardware capacity and workload.