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.
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.
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.
I tried providing a hint OPTION (MAXDOP 4) to query but it's not working.
We have not changed "max degree of parallelism" configuration, it's set to default value, so as per link, SQL Server determines parallelism itself when setting a default value. I use below query to get DOP of the server. Refer attached
select * from sys.configurations where name in ('max degree of parallelism')
SQL Server Version is 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)