asked
SQL Server Parallelism Disabled
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. ![alt text][1] 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][2], SQL Server determines parallelism itself when setting a default value. I use below query to get DOP of the server. Refer attached screenshot. ![alt text][3] 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)* [1]: /storage/temp/4463-dop-changes.png [2]:
https://msdn.microsoft.com/en-us/library/ms181007.aspx [3]: /storage/temp/4465-configuration.png
What is your cost threshold for parallelism? select * from sys.configurations where name in ('cost threshold for parallelism')
And both those execution plans where created on the same server? The exact same query against 2 different databases?
Are those Estimated execution plans or Actual execution plans?
First of all, creating missing indexes not always help you and in some cases make things worse for you. One must verify the impact on test servers with the daily workload of the live environment. So any major issues are addressed prior to the deployment on live servers. Secondly, the max degree of Parallelism governs how many processors to be used for a parallel query execution, but whether a query would have a parallel execution plan depends upon if the query is hitting the [cost threshold for parallelism][1]. According to BOL, The SQL Server query optimizer does not use a parallel execution plan for a query if any one of the following conditions is true: - The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan. - A serial execution plan is considered faster than any possible parallel execution plan for the particular query. - The query contains scalar or relational operators that cannot be run in parallel. Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode. So with addition of missing indexes, now the engine could be calculating the cost of parallel plan higher than the serial plan. But that is all what it seems to be, please share us DDL of the table/view, some sample data and most importantly the execution plans (.sqlplan files) to help you better. Moreover, please enlighten us with all the details of "legacy server", "restoration of DB" etc. stated by you as " I choose a simple select query and compared execution plan with the legacy server(I restored old database instance from AWS)". This information could also be helpful in identifying the reason as any migration to SQL Server edition could have an impact as well. [1]:
https://technet.microsoft.com/en-us/library/ms190949(v=sql.110).aspx
18 People are following this question.
