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.

enter image description here

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 enter image description here

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)

  • Did you saw that estimated number of rows in 2nd plan(which according to you is bad) is much greater than first so basically both are not returning same number of rows so plans may not be similar. Is this actual plan or estimated plan ?.How did you came to conclusion that missing index is definitely going to help the query. When you have not set MAXDOP SQL Server is going to internally decide whether to use parallelism or not . – Shanky Jan 18 '18 at 9:00
  • Did the cost threshold for parallelism change? What's the value set to? MAXDOP determines "how" of parallelism but CTP determines the "if". – Doug Lane Jan 18 '18 at 18:56
  • try to recompile views and run a query again. updating statistics might help too. – Natalia Jan 18 '18 at 22:56

Parallelism it not disabled after adding an index, so the reason is some where else.

From your text I understand that you ran the query on a restored database on a different server (legacy server you say), is that correct? If yes, then I would check that both instances have same configuration, specially MAXDOP and the Cost Threshold for Parallelism.

Check that underlying data is the same on both tests. As Shanky commented, the 2nd query shows a higher number of Estimated number of rows, 132432 against 99489.

Also, as recommended on the comments, be sure that all statistics related to the query are up to date, if not, update them to be certain than both queries run with same information to get the same execution plan.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.