Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [maxdop]

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.

0
votes
1answer
33 views

SQL Server Configuration on Multiple Instance Server

I have a 2016 Standard edition server with two instances. The server has 8 logical processors and 40GB memory (it's a testing server on hyper-v so the spec isn't high!) and it doesn't have any ...
8
votes
4answers
2k views

MAXDOP Settings for SQL Server 2014

I know this question has been asked number of times and also has answers to it but, I still need a bit more guidance on this subject. Below is the details of my CPU from SSMS: Below is CPU tab from ...
5
votes
1answer
108 views

Queries running parallel with default maxdop

I need to understand below behavior of sql server queries System has 4 CPUs with hyper thread ratio of 20 making 80 logical processors count on my sql server instance. Currently MAXDOP is 0 and ...
1
vote
2answers
96 views

Workaround to implement Minimum Degree of Parallelism

Please correct me if I am mistaken but as I understand it, when a query is being scheduled for execution, the engine takes into account the number of available free threads and will adjust the query ...
0
votes
1answer
55 views

MAXDOP recommendation

We would like to implement best practice on our SQL Server instances. All of our instances still run with the default setting of MAXDOP = 0. Having read several pages on the recommended settings for ...
1
vote
1answer
288 views

SQL Server MAXDOP on VMWare Virtual Servers

When setting MAXDOP on VMWare virtual servers VMWare recommend following the Microsoft guidlines https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-...
10
votes
2answers
2k views

MAXDOP = 1, Query Hints and Cost Threshold For Parallelism

If an instance has MAXDOP set at 1 and query hints are used to allow specific queries to go parallel, is the Cost Threshold For Parallelism value still used by SQL to decide whether or not to actually ...
2
votes
2answers
323 views

What can I do about high CXPACKET waits?

We are experiencing some slowness on our MS SQL Server 2016 database, I have been using Brent Ozar's first aid kit to do some initial troubleshooting. I am seeing a high amounts of CXPACKET wait ...
3
votes
1answer
910 views

MaxDOP Limits SQL Server 2016 Standard

I have SQL 2016 Standard Edition instance with 40 logical processors in 2 nodes. (2x20=40) Per Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server ...
0
votes
1answer
177 views

What is the MAXDOP value for the server which doesn't have NUMA [duplicate]

Im using a SQL Server with the below configurations. 4 Core. 8 Logical processors. No NUMA. NO hyperthreading. I read this Doc: Recommendations and guidelines for the "max degree of parallelism" ...
3
votes
1answer
3k views

SQL Server Threads and Degree Of Parallelism

My understanding about SQL Server threads is that SQL Server breaks down an execution request within a session into several tasks and associates each task with a worker thread. So SQL Server always ...
1
vote
2answers
267 views

MAXDOP or 1 only? Or sql can use a value between 1 and configured maxdop

Is there a way for sql server to use no of cores which is between 1 and the configured MAXDOP? For example if maxdop is 8. Is it possible for sql to use 3? Lets say that resource gov, hints, and any ...
-1
votes
1answer
222 views

MAXDOP and Physical Cores

I've got an instance built with 4 cores. We have some queries that are going parallel. I know changing the MAXDOP to 0 will single thread the queries. My question is whether the 4 cores will no ...
0
votes
0answers
48 views

SQL Server MAXDOP , microsoft reommendation [duplicate]

Microsoft's recommendation for setting MAXDOP is based on logical processors count. https://support.microsoft.com/en-gb/kb/2806535 On my server which has one hex core CPU, hyper threaded,6 physical ...
4
votes
1answer
623 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
395 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
3k 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 ...
5
votes
3answers
9k 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
173 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
546 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
421 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
131 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
622 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....
7
votes
1answer
1k 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
290 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
691 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
319 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
314 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
1k 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) ...
65
votes
7answers
41k views

MAXDOP setting algorithm for SQL Server

When setting up a new SQL Server, 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 NUMA ...