I have a procedure with two SELECTs. One statement respects the server's/database's/query hint's maxdop setting and uses parallelism, the other is being difficult and never going parallel. The 8 core server and this db are configured for maxdop 0 (not great, and I can change it, but I'd like to leave it and figure out what's going on). Cost threshold is 5. Auto update stats is on and stats on the table are showing as being updated earlier today. I may try updating the stats on the table manually after hours tonight. This server and db are an AG secondary replica. The same procedure on the primary runs fast and both statements go parallel. The primary has more cores, and its maxdop is set explicitly to to 8. Statement 1 query hints - No query hint -> DegreeOfParallelism = 1 - Maxdop 0 -> DegreeOfParallelism = 1 - Maxdop 1 -> DegreeOfParallelism = 0 (reason MaxDopSetToOne) - Maxdop 8 -> DegreeOfParallelism = 1 - Maxdop 7 -> DegreeOfParallelism = 1 Statement 2 query hints - No query hint -> DegreeOfParallelism = 8 - Maxdop 0 -> DegreeOfParallelism = 8 - Maxdop 1 -> DegreeOfParallelism = 0 (reason MaxDopSetToOne) - Maxdop 8 -> DegreeOfParallelism = 8 - Maxdop 7 -> DegreeOfParallelism = 7 Thanks for any help!

Seems like I have problems with parallelism because largest wait types are CXCONSUMER and CXPACKET. Server has 8 cores. So I am planning to bump up Cost Threshold to **50** and MaxDop to **4** Currently I have default values, which is * 5 - cost threshold for parallelism * 0 - max degree of parallelism Mostly, only a couple of databases are used intensively out of all databases we have on instance. Which makes me wonder whether I should implement those changes on a whole instance or only on a couple of databases.

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 types, out of a 17.5 hour data sample we saw 99 hours of wait across our 10 CPU's, that 55.5%! I was hoping someone here could confirm that we should be concerned about this number and resolve it asap. We have MAXDOP setting of 4 which is accurate to MS recommendations but our CTP is set to 5 which I believe needs to be changed to 50. Just looking for clarification before I take this information to my boss, yes, I am new to database administration and yes I am looking at other wait types but this seems to be the most significant so far. Cheers, Josh

Link: https://learn.microsoft.com/en-US/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15#Recommendations I understand the basic concept of max dop and cost threshold. I am reviewing 3 SQL servers (**virtualized**, not physical machines) with the following combinations of socket and virtual processor: 1. 1 socket 2 virtual processors 2. 12 sockets 24 virtual processors 3. 24 sockets 24 virtual processors The max dop is 0 and cost threshold is 5. I am reading the recommendation from the above link and they have used terms like logical processor, NUMA node. Where as when I open the task manager I can see number of sockets and virtual processors. > Starting with SQL Server 2016 (13.x), during service startup if the > Database Engine detects more than eight physical cores per NUMA node > or socket at startup, soft-NUMA nodes are created automatically by > default. I understand that Soft NUMA means that - it creates software based NUMA so that cores can be shared with multiple sockets. Questions: 1. Does NUMA node and socket mean the same thing? 2. In my examples, does the server configuration (see table from the above link) classify as single NUMA node or multiple NUMA nodes? If NUMA and socket is the same thing then I assume answer is multiple NUMA nodes? 3. Does physical core also mean virtual processor?

There is good general advice for how to configure Maximum Degree of Parallelism (MAXDOP). However, I am in a special circumstance. There are two instances on my box. If I have 16 cores, how should I divide MAXDOP between them? What metrics would reveal that I have made a bad choice? I am on SQL Server 2019 and [the standard advice for MAXDOP changed in 2016](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16#recommendations) so info from before June 2016 (the release date of SQL Server 2016) may not be applicable.

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 with cost threshold to 50 we suspect they may be other motives of insisting setting of 1. I'm unsure what they maybe however. ** EDIT:: to make the question more specific, is there a scenario where by setting maxdop to anything other than 1 cause errors to occur** - Microsoft Guidance https://support.microsoft.com/en-us/kb/2806535 - relevant Paul Randall post http://www.sqlskills.com/blogs/paul/maxdop-configuration-survey-results/ - Brent Ozar on CX packet waits http://www.brentozar.com/archive/2013/08/what-is-the-cxpacket-wait-type-and-how-do-you-reduce-it/

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 memory configuration. You will need to evaluate this setting in a non-production environment before moving it to production. MAXDOP can be configured using: EXEC sp_configure 'max degree of parallelism',X; RECONFIGURE If this instance is hosting a Sharepoint database, you MUST specify MAXDOP=1 (URL wrapped for readability) http://blogs.msdn.com/b/rcormier/archive/2012/10/25/ you-shall-configure-your-maxdop-when-using-sharepoint-2013.aspx Biztalk (all versions, including 2010): MAXDOP = 1 is only required on the BizTalk Message Box database server(s), and must not be changed; all other servers hosting other BizTalk Server databases may return this value to 0 if set. http://support.microsoft.com/kb/899000 */ DECLARE @CoreCount int; DECLARE @NumaNodes int; SET @CoreCount = (SELECT i.cpu_count from sys.dm_os_sys_info i); SET @NumaNodes = ( SELECT MAX(c.memory_node_id) + 1 FROM sys.dm_os_memory_clerks c WHERE memory_node_id 4 /* If less than 5 cores, don't bother. */ BEGIN DECLARE @MaxDOP int; /* 3/4 of Total Cores in Machine */ SET @MaxDOP = @CoreCount * 0.75; /* if @MaxDOP is greater than the per NUMA node Core Count, set @MaxDOP = per NUMA node core count */ IF @MaxDOP > (@CoreCount / @NumaNodes) SET @MaxDOP = (@CoreCount / @NumaNodes) * 0.75; /* Reduce @MaxDOP to an even number */ SET @MaxDOP = @MaxDOP - (@MaxDOP % 2); /* Cap MAXDOP at 8, according to Microsoft */ IF @MaxDOP > 8 SET @MaxDOP = 8; PRINT 'Suggested MAXDOP = ' + CAST(@MaxDOP as varchar(max)); END ELSE BEGIN PRINT 'Suggested MAXDOP = 0 since you have less than 4 cores total.'; PRINT 'This is the default setting, you likely do not need to do'; PRINT 'anything.'; END I realize this is a bit subjective, and can vary based on many things; however I'm attempting to create a tight catch-all piece of code to use as a starting point for a new server. Does anyone have any input on this code?

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.microsoft.com/en-us/kb/322385 So we need to change it to 4. However my question is what is the impact of having maxdop set to 8? So going parallel across two NUMAS ? The reason I ask is that we just had a strange issue where queries were very slow to return and PLE dropped off rapidly. Even when nothing was running against SQL PLE didn't not improve. CXPACKET wait types went up. Then all of a sudden CXPACKET wait type dropped completely and PLE began to rise and now has returned to normal. Throughout this time small queries were being executed against a database, however it was not the case that one query finished causing the CXPACKET wait type to drop off and PLE to rise again - we don't know what causes that. A possible explanation is the incorrect MAXDOP setting. Can anyone explain to me the impact of parallel executions across NUMA nodes, is it just the same as exhausting worker threads and slower access time when using foreign memory ? Thank you

I'm running SQL Server 2022 RC1 setup on an AWS i3.16xlarge with 2 sockets, 2 NUMA nodes, 32 logical processors per node, 64 logical processors altogether. Setup is recommending MAXDOP 8: But if you click on that link for configuring MAXDOP , the recommendations say: Based on that KB article, MAXDOP should be 16, not 8. Sure, technically 8 is less than 16 - but so is 2, or 4, or 15. Where's the 8 coming from? After SQL Server installation finishes and the service starts up, the log shows that SQL Server is automatically implementing Soft-NUMA with 4 nodes, each with 16 logical processors: So again, that indicates that MAXDOP should be 16. Is this a bug, or did I miss something obvious? Is there another unwritten rule somewhere that setup will stop at MAXDOP 8?

I want to change data type of a column of my table in SQL Server 2019. My table has 50,000,000 row and it's very important. I want to do this operation online and also handle maxdop to avoid CPU over usage. I wrote this query but I got syntax error:

Alter table Customers
alter column FirstName char(16) not null
with (online = on, maxdop = 4)

> By default, SQL Server allows a maximum of 32767 concurrent > connections which is the maximum number of users that can > simultaneously log in to the SQL server instance. I am developing 10 web apis that will query the sql server using their own logins (so max 10 logins, plus admin/devs). Expected number of concurrent logins is not beyond 20. However each login (api) can make several (maybe 500) concurrent query requests. So effectively 10 apis * 500 req = 5000 concurrent query requests. There will be times when there are less or no requests. Assuming there is sufficient memory and disk io capability, I am planning the cpu requirements. I understand sql request is assigned to the worker thread and based on number of processors there is a certain number of default worker threads. Presently my dev machine has 24 processors so the default max worker thread is 832. Assuming cost threadhold (of 40) may be exceeded by the queries, which means the sql server may decide to use parallelism (max dop). Assuming MAX DOP = 1 then 832 requests can be handled at a time. Assuming MAX DOP = 4 then 208 requests can be handled at a time. Any query requests beyond this will have to wait until it is allocated a worker thread. So to ensure peak load of 5000 requests can be satisfied, is it correct to estimate that I will require roughly atleast 145 cpus? ((145-4)*32)+512 = 5024

Querying the dm_os_performance_counters shows that I have **2** NUMA nodes: select * from sys.dm_os_performance_counters where object_name = 'SQLServer:Buffer Node' and counter_name = 'Page life expectancy' Querying the **sys.dm_os_sys_info** shows: Numa node count 4 CPU count 24 Softnumae_configuration 1 Socket_count 24 Cores per socket 1 Where as when I right click on **SQL server properties**, click on Processors it shows 2 NUMA nodes, each with 12 CPUs. **SQL error log**: SQL Server detected 24 sockets with 1 cores per socket and 1 logical processors per socket. 24 total logical processors. Automatic soft-NUMA was enabled because SQL server has detected hardware NUMA nodes with greater than 8 physical cores. So what is the final value of NUMA and CPUs? I am asking because it is recommended to have the MAX DOP configured based on the number of NUMA nodes and the processors per node (https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15#Recommendations) . How to decide on the **MAX DOP** when there is discrepancy between processor count reported in **sys.dm_os_sys_info** vs **server properties** vs **SQL error logs**?

During a parallel query execution (whether or not to go parallel is decided by the cost threshold for parallelism) MAXDOP limits the number of tasks per request. Assuming cost threshold for parallelism is a low value, there by causing even smaller queries to work in parallel. When MAXDOP is set to 0, what makes the processors that have done the work wait (resulting in high CXPACKET wait time). Can they not pick up the next work and not just wait? What makes the processor wait once it has done its job? From https://www.brentozar.com/archive/2013/08/what-is-the-cxpacket-wait-type-and-how-do-you-reduce-it/ >This isn’t really a bottleneck per se – the students could go off and do other work – but they like to complain about how they had to wait around for the slow kids. > >That complaining is CXPACKET – Class eXchange Packets. The class is turning in their packets, and complaining about the slow kids.`

Sql server recommendation is to decide max dop based on number of processors in a single Numa node. Given a system with 4 Numa nodes, each having 6 processors, the max dop recommended hence would be 6. Why is max dop related the number of NUMA nodes?

I have sql server with 8 cpus. Max worker threads setting is say 850. Max dop is say 8. And cost thread hold for parallelism is 50. This means sql server will breakdown a query going above cost threshold into 8 threads. Since each threads runs on one cpu, then does this mean no other user will be allowed to run a query until at least one of the currently running 8 threads gets released? So does setting max dop = number of cpu's cause situation where a single query blocks other queries?

I have previous experience of setting MAXDOP settings for on premise OLTP & OLAP servers when you know the number of cores at your disposal. e.g. if I knew I had 16 at my disposal I'd set my MAXDOP to 2 or 4 for my OLTP system. I just joined a project and noted the core production database is set to MAXDOP of 1, prohibiting any parallel operations. It is a dedicated server (so no elastic pool) Premium P11 server so DTUs abstract the exact amount of CPU, memory capability. Without being able to tell how many cores you are dealing with, is there a sensible starting config? finding it a little tricky to port on prem best practices to Azure SQL. The test servers are a different config with a shared elastic pool, so no easy way to test specific execution plans there either (especially as everything is written through Entity framework ORM)

We have a prod system that sometimes gets stuck and nothing we do helps but changing maxdop parameter on live system, after we change that all gets back normal. And it does not even matter if we change maxdop to 0 from 4, or to 2 from 1 , as long as we change maxdop. It has been going on for about a year (and during that time this has happened about 4 or 5 times) and we have not been able to find what is causing it. Since the system is going to be replaced soon and we have sort of workaround for it, I am not asking for help to figure this out, just to understand what happens to existing sessions when maxdop is changed, so my question is: What happens to queries that are in progress when we change maxdop in live system and while they are executing?

We're working with a vendor who is insisting we use MAXDOP = 1 (I know...). They are also emphasizing the importance of striping our data files across different disks. As I understand it, an I/O operation is always paired with a CPU cycle. So if we have tables that were spread over different disks, wouldn't MAXDOP = 1 make reads/writes occur sequentially rather than in parallel?

I have inherited a legacy application that includes a job that creates performance issue at instance level. The DBA told us about this and our team needs to fix the stored procedure that is the most resource intensive. Unfortunately, the logic is quite a mess and I am aiming for quick wins until we manage to rewrite the logic. The job executes three stored procedures that consume a huge amount of CPU and I/O. I have run sp_who2 active while the job was running and noticed that about 8 threads were running for its SPID. I am wondering if I can temporarily reduce the strain on the server by reducing the maximum degree of parallelism. I know about MAXDOP , but it seems to be only applicable at instance, database or query level (through hints). I would like an option to set this at session (SPID) level, to avoid changing all the queries in the computation. The first comment from here indicates that DBCC OPTIMIZER_WHATIF(CPUs, 1) might be an ugly hack that does the trick, but I cannot find if this change applies to session level or instance level (I do not want to mess with anything on the server except for the problematic job).

We recently moved our 2016 sql server mirror into Azure, but it's on an underpowered machine. Our main database server has 16 physical cores with hyper threading and 2 numa nodes. We have set the maxdop to 16 for this machine. Edit: I had previously set it to 8 and saw the same issues. The mirror host has a much weaker cpu, so we had to set the maxdop to 4. When we are running an intense query on the principle database, we sometimes get an error: Unexpected job failure.: System.Data.SqlClient.SqlException (0x80131904): The query processor could not start the necessary thread resources for parallel query execution. I am wondering if could be due to the underpowered mirror server running out of resources while it is trying to commit all the transactions that are being fed across to it from the principal server. I'm not a DBA, and we currently do not have one on staff (so I guess I am the DBA), so a lot of these settings and errors are foreign to me. Edit2: This question is different from the one suggested because I originally followed the directions and scripts in that post to set my MAXDOP to appropriate values.