MAXDOP and cost threshold for parallelism settings

  • vsamantha35

    SSChampion

    Points: 11167

    Hi All,

    On one of our newly migrated sup-prod azure vm which is running on SQL Server 2012 EE, we are seeing high 100% CPU. On further looking at server parameters, we see that "maxdop=0" and "cost threshold for parallelism=5" which is by default. From task mgr and perfmon it is evident that sustained 94% of CPU is driven by SQL Server. The VM has 1 socket 8 logical cpu's. Also, CXPACKET is the top 2 contibutor of waits (i..e 75%). Its an OLTP application. So, we tweaked the values as follows

    maxdop set to 4 and cost threshold for parallelism is set to 50.

    Now question, is does it apply for ongoing processes /currently running spids?

    or these settings will be available for newly spawned threads?

    Reason for asking this question is, even after making this changes, still we are seeing sustained high cpu with little difference of 1 or 2 percent.

    Regards,

    Sam

  • This was removed by the editor as SPAM

  • frederico_fonseca

    SSChampion

    Points: 14636

    new queries only - so if you are seeing high cpu you will need to use a few dmv's to figure out which queries are taking that cpu

  • vsamantha35

    SSChampion

    Points: 11167

    frederico_fonseca wrote:

    new queries only - so if you are seeing high cpu you will need to use a few dmv's to figure out which queries are taking that cpu

     

    We captured those queries Fred. However, I want to know the effect is immediate or for fresh queries. Thank you very much for clarification.

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

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