title: SQL Server Parallelism Disabled
body: <p>I came up with a strange issue where the performance of select queries was degraded after creating missing indexes on tables. Initially, it looks like all the queries are affected but when looked into details we found that queries that have inner join with a view were affected. A view contains a couple of tables but those are not modified.</p>
  
  <p>To investigate it further, I choose a simple select query and compared execution plan with the legacy server(I restored old database instance from AWS).  I see that except DOP(Degree of Parallelism) everything looks same. DOP was changed from 4 to 1. i.e somehow parallelism got disabled after adding indexes.</p>
  
  <p><a href="https://i.stack.imgur.com/mBM7j.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mBM7j.png" alt="Degree of parallelism changed"></a></p>
  
  <p>The execution time of the query is almost double now so I assume DOP has affected it very badly. I read a couple of articles related to DOP but found nothing that can help me. </p>
  
  <p>I tried providing a hint <strong>OPTION (MAXDOP 4)</strong> to query but it's not working.</p>
  
  <p>We have not changed "<strong>max degree of parallelism</strong>" configuration, it's set to default value, so as per <a href="https://msdn.microsoft.com/en-us/library/ms181007.aspx" rel="nofollow noreferrer">link</a>, SQL Server determines parallelism itself when setting a default value. I use below query to get DOP of the server. Refer attached <a href="https://i.stack.imgur.com/i5TpM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/i5TpM.png" alt="screenshot"></a>.</p>
  
  <pre><code>select * from sys.configurations where name in ('max degree of parallelism')
  </code></pre>
  
  <p>SQL Server Version is 
  <em>Microsoft SQL Server 2014 - 12.0.4422.0 (X64)   Jul 27 2015 16:56:19   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)</em></p>