Post reply

question on parallelism

  • February 22, 2023 at 1:56 pm

    #4152807

    Hi All,

    We have 16 CPU's, 256GB RAM Azure VM on which SQL Server 2017 is running.

    Our workload runs pretty well when MAXDOP = 1 and when we try to set the MAXDOP = 4 or MAXDOP = 8, we start seeing deadlocks all over and application team start complaining about slowness of some queries.

    Questions

    =========

    1. Did anyone came across workloads where MAXDOP =1 and works fine and why is that? otherwise we see a lot of deadlocks in app.

    We are thinking like we aren't able to take advantage of more CPU's.

    2. What should be the value set for 'cost threshold for parallelism' for OLTP and OLAP datawarehouse instead of default value 5.

    Our db size is 3.5 TB?

    3. How to find the right value to be set for MAXDOP for workloads running on MAXDOP=1. What things to be monitored and analyzed further?

    Please share your thoughts.

    Thanks,

    Sam

  • February 23, 2023 at 2:10 pm

    #4153292

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • February 23, 2023 at 4:18 pm

    #4153332

    MAXDOP = 1 yeah, there are certain workloads where this is actually recommended, SharePoint, Dynamics AX for example, their best practise is to set MAXDOP = 1

    You may be experiencing "intra query parallelism deadlocks", do you have a deadlock graph you are able to share?

     

    There is no right or wrong answer for the cost threshold, most set it to 50 and then further tune it.

    If you take a read of https://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/, there is a query to see what the cost threshold are from the cache per query which may lend you to tuning either the query or the cost threshold value.

     

    Again for maxdop there is no right or wrong answer, it is going to be workload dependent, however Microsoft do offer some guidance on potential settings for maxdop in this link https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16

     

  • March 1, 2023 at 3:04 pm

    #4155503

    Have you reviewed the queries involved in the deadlocks?

    Perhaps you are under -indexed or over-indexed.  Maybe the queries can be tuned to hit the tables more efficiently (and same order).

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply