Max Degree of Parallelism (MAXDOP) is a configuration option that determines the maximum number of threads dispatched by SQL Server to service queries that might benefit from parallel operation.

learn more… | top users | synonyms

0
votes
0answers
25 views

MAXDOP Recommendations - Is my understanding correct? [duplicate]

I have a VM SQL 2014 Server with the following CPU setup. If I follow MS recommendations for MAXDOP settings (https://support.microsoft.com/en-us/kb/2806535) I believe that MAXDOP should be 2 as the ...
3
votes
1answer
115 views

Multi-instance SQL Server Standard Editon MaxDop settings

You read the title and are still confused? Well I am too. I've just started a new job as DBA for a new employer and have come across some creative ways of installing SQL Server. My previous ...
5
votes
1answer
190 views

Cost Threshold for Parallelism with Maxdop = 1

Been debating with my company's app vendor regarding parallelism. They have maintained that Maxdop = 1 has significant performance improvements for the app/db but they have provided absolutely no ...
1
vote
1answer
388 views

why sql server has high Worker threads? [closed]

Product : SQL Server 2008 (SP4) CPU : 64 64-Bit system Worker thread configuration = 0 SCOM Alerts : SQL DB 2008 Engine Thread Count is too high After receiving this alert from SCOM i have ...
4
votes
2answers
2k views

How to reduce huge CXPACKET & LATCH_EX (ACCESS_METHODS_DATASET_PARENT) wait times?

Problem We have been experiencing high levels of user disruption due to SQL timeouts accross our systems since the beginning of the year. The SQL-Server instance in question has very high CPU usage (...
3
votes
1answer
45 views

MAXDOP query for SQL Server 2012

I have a 8 core VM machine running SQL Server 2012. The current setting of MAXDOP is set as 5. There is recommendation from my colleagues to change the MAXDOP to 5 for better performance as there has ...
0
votes
1answer
144 views

SQL Server 2012 - Deadlock happening daily

We have a performance issue with a 3rd-Party application using SQL Server 2012: deadlock happen at least 10 times an hour. The server is virtual server on VMWare 5.5 with PVSCSI adapter in persisted ...
2
votes
1answer
94 views

Ideal MAXDOP & CPUs - how to trace for parallel queries?

There is a discussion going on in our company on what the ideal CPU count and Max Degree of Parallelism are for a 3rd party database server. The server has 12 CPUs, 32GB RAM and all database sizes ...
-1
votes
2answers
77 views

Under what circumstance would setting MaxDop to anything other than 1 cause an issue? [closed]

we are having continued discussions with our vendor whom is insistent on setting MaxDop to 1 we have performed internal testing and see a general 20% performance improvement by setting this to 4 ...
4
votes
1answer
309 views

NUMA Nodes - MAXDOP - PLE

We have a server with 8 CPUS across 2 NUMAs with hyperthreading enabled. Currently Maxdop is set to 8, but actually should be set to 4 as per the Maxdop section of this article: https://support....
5
votes
1answer
509 views

MAXDOP not working?

I wanted to test out MAXDOP on my computer. So I set MAXDOP to 2 for a specific query. However, when I looked at my logical processors in Task Manager as I ran the query, it looked like they were all ...
0
votes
2answers
141 views

Max Degree Of Parallelism [closed]

What is the Best Practices for Enabling and Disabling the MaxDop Setting for SQL Server on regular basis. Is it ok to create a job and run the job as required or have the script stored as SP?
0
votes
0answers
322 views

Sql Server, a single threaded query spawns multiple threads (or internal parellelizaion during execution)

In Sql Server, when I run a procedure or a query that takes multiple minutes, sometimes spawns multiple threads. Even though there is no MAXDOP hint or necessity in the query, I see multiple instances ...
0
votes
1answer
205 views

Number of TempDB files if we can't use parallelism

Currently we have 4 cores and 4 tempdb files. I just came on as the DBA about 2 months ago and, for some reason, 3 of the tempDB files are 2GB and 1 is 1GB. Obviously, that is not best practice. ...
3
votes
1answer
185 views

Maxdop in Profiler

Say I run a server side trace against a SQL Server 2008 R2 instance, which has Maxdop = 1 in place, then I run the trace through readtrace.exe to get RML files for replay. Does the replay preserve ...
5
votes
2answers
862 views

SQL Server R2 Standard Edition MAXDOP setting

I'm looking for confirmation/guidance on setting MAXDOP in a SQL Server instance with the following config: Version : SQL Server 2008 R2 Standard Edition Processor : 2 x AMD Opteron(TM) ...
34
votes
6answers
19k views

What is a good, repeatable way to calculate MAXDOP on SQL Server?

When setting up a new SQL Server 2012, I use the following code to determine a good starting point for the MAXDOP setting: /* This will recommend a MAXDOP setting appropriate for your machine's ...