0

I'm trying to optimize an SQL Server stored procedure by using OPTION (MAXDOP 1) and this way avoid parallelism on the execution plan, but my question is, it is safe to use?

I have read it might cause some bugs on the result, and on other websites they say it is not recommended, so my question is? it is safe to use?

There are another ways to avoid parallelism without using OPTION (MAXDOP n)?

Thanks

Dale K
  • 25,246
  • 15
  • 42
  • 71
Legendarkz
  • 69
  • 1
  • 8
  • 5
    "I'm trying to optimize an SQL Server stored procedure by using OPTION (MAXDOP 1" - you would be better off making sure you have the 'right' indexes and that statistics are up to date. – Mitch Wheat Jun 14 '21 at 02:38
  • 1
    And include in your question the exact advice and references to it. – Dale K Jun 14 '21 at 02:39

2 Answers2

2

Yes, it's safe to use.

Setting OPTION (MAXDOP 1) is a documented query hint, and is therefore supported. If a query returned wrong results with this hint, it would be a bug and would get fixed.

Note "wrong results" is not the same thing as "different results". It is possible to write a query whose results depend on the query plan choice. For instance, a TOP 10 query without an ORDER BY that completely orders the rows will return a different TOP 10 depending on the choice of plan.

Dale K
  • 25,246
  • 15
  • 42
  • 71
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
0

It all depends on the execution plan of the query. If the query is having lots of CXPACKET waits due to parallelism, we can see whether setting MAXDOP to 1, will help the query. It needs to be tested before setting up this option.

Better to have right indexes and not go for these kinds of query hints. Because, Optimizer knows better on deciding, whether to go for parallel plan or not.

Also, refer to below articles for more information:

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58