MDP/CTP values

Question

MDP/CTP values

ramana3327

SSCoach

Points: 19700
More actions
October 7, 2022 at 7:16 pm

#4096735

Hi All,
We have a server with 8 cpus. Some times Thread count is higher than 576(Max worker threads). Noticed some deadlock issue at the same time.
Most of the wait types are CX packets (Above 35%). CTP is set to 5 & MDP is 0. Best recommended values to change MDP and/or CTP? Planning to change CTP from 5 to 25. Is it best approach to change MDP at the same time from 0 to 4?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

If by MDP, you mean MAXDOP, I'd change it to 2. You've got a fairly heavy load to be giving folks half the total CPU power for their queries.

If by CTP, you mean Cost Threshold for Parallelism, I'd change that (right away) to 50 and then adjust from there if needed.

When you say you have "8 CPUs", do you mean 8 CORE? If you look at "System Information" next to the "Processor Entry" (1oth entry from the top on my box), you should see an entry like the following...

Processor Intel(R) Core(TM) i7-8750H CPU @ 2.20GHz, 2208 Mhz, 6 Core(s), 12 Logical Processor(s)

What does yours say? I'm particularly interested in the number of core and logical processors.

Also, how much RAM does your box say it has? You'll also find that under "System Information" and it'll look like this...

Installed Physical Memory (RAM) 32.0 GB

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramana3327 SSCoach Points: 19700 More actions · Answer 2

Processor: Intel(R) Xeon((R) CPU E5-2667 v3 @ 3.20GHZ ((2 processors)

Installed memory: 16 GB

64 bit Operating System, x64- based processor

SQL server Always on is configured on this with just 1 database & one more DB without always on group.

This reply was modified 1 year, 6 months ago by ramana3327.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

ramana3327 wrote:

Processor: Intel(R) Xeon((R) CPU E5-2667 v3 @ 3.20GHZ ((2 processors)
Installed memory: 16 GB
64 bit Operating System, x64- based processor
SQL server Always on is configured on this with just 1 database & one more DB without always on group.

Now... that's interesting... According to the spec for that processor ( https://www.intel.com/content/www/us/en/products/sku/83361/intel-xeon-processor-e52667-v3-20m-cache-3-20-ghz/specifications.html ), each processor has 8 Core and is hyper-thread capable of 16 threads for each processor. According to the output above, you have two such processors and that would seem to mean that you SHOULD have 16 total core capable of 32 logical core. You should do an investigation about why you're only seeing 8... unless... you have more than one instance of SQL Server running on this box.

And, yet... you folks have only given it 16GB of RAM and the operating system is going to need a minimum of 4 and would do much better with 8.

I recommend you bump this bad boy up to at least 128GB of RAM and setup the maximum memory to 115,000MB. It won't hurt if you go over the 128GB if you have the Standard Edition... it just means that Windows will have a bit more headroom. If you have the Enterprise Edition, I'd jump to at least 256GB.

Memory is relatively cheap and requires no additional licensing costs. Don't starve your server. Load up on some RAM! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramana3327 SSCoach Points: 19700 More actions · Answer 4

From task manager under CPU

Sockets: 2

Virtual processors: 8

From Resource Monitor under CPU showing CPU0-CPU7 (only8). Same no 8 cpus from SQL Server Properties. The Max memory is 8gb.

From SQL server configuration manager it’s showing only one default instance is running.

This reply was modified 1 year, 6 months ago by ramana3327.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

ramana3327 wrote:

From task manager under CPU
Sockets: 2
Virtual processors: 8
From Resource Monitor under CPU showing CPU0-CPU7 (only8). Same no 8 cpus from SQL Server Properties. The Max memory is 8gb.
From SQL server configuration manager it’s showing only one default instance is running.

If that's all true, then you've allowed 1GB per "CPU". I don't know what your workload is but that seems to qualify as an emaciated horse to me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16008 More actions · Answer 6

can you give us the output of these queries

select @@version

select cpu_count
     , hyperthread_ratio
     , physical_memory_kb
     , visible_target_kb
     , stack_size_in_bytes
     , affinity_type_desc
     , virtual_machine_type_desc
     , softnuma_configuration_desc
     , process_physical_affinity
     , sql_memory_model_desc
     , socket_count
     , cores_per_socket
     , numa_node_count
from sys.dm_os_sys_info

select *
from sys.dm_os_memory_nodes

select scheduler_address
     , parent_node_id
     , scheduler_id
     , cpu_id
     , status
     , is_online
from sys.dm_os_schedulers

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 7

I am going to take a guess here - the OP is confusing the host with a guest OS either in Hyper-V or VMWare. That is the only way I can see for the discrepancy.

Regardless - 8 CPUs and 16GB of memory to support 2 databases might be enough depending on the actual load. However, adding an AG or BAG to the mix (with clustering overhead) and that memory is not going to be enough. The fact that you are running out of worker threads clearly indicates the configuration is not optimal for that workload.

Someone else recommended 128GB or even 256GB memory - which will not solve the problem with worker threads, however - it can only help to increase the memory (how much memory will depend on the actual workload, the size of the database(s), etc.). If you are running out of worker threads then you need more processors. Or you need to modify the applications to reduce the load on the instance.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ramana3327 SSCoach Points: 19700 More actions · Answer 8

Select @@ version: SQL 2017 ( RTM -CU22) standard edition on Windows 2016 standard.

From sys.dm_os_sys_info

cpu_count = 8, hyperthread_ratio=4, physical _memory_kb = 16776624, virtual _memory_kb = 137438953344, visible _target_kb = 8388608, stack_size_bytes=2093056, affinity _type_desc=Auto, virtual _machine_type_desc=Hypervisor, softnuma_confuguration_desc=off, process _physical_affinity={{o,ff}}, sql_memory_model_desc=conventional, socket _count=2, cores_per_socket=4, numa_ code_count=1

From sys.dm_os_memory_nodes give me 3 records

memory_node_id = 0,1,64 Virtual _address_space_reserved_kb= 74231228,0,0 Virtual_address_space_commited_kb=8393368,36,36 locked_page_allocations_kb=0,0,0 pages_kb=7424112,0,0 shared_memory_reserved_kb=192,0,0 shared_memory_committed_kb=192,0,0 cpu_affinity_mask=255,0,0 online _schedular_mask=255,0,255 processor_group=0,1,0 foreign _committed_kb=0,0,0 target_kb=8388528,40,0

From sys.dm_os_schedulars gave 31 records

parent_no_id =0 for all except for DAC. Scheduler_id & cpu_id values are 1-7 for those status is showing visible online. For few records status is hidden online. Is_online=1 for all of them is _idle=0 for 3 records & 1 for all of them

This reply was modified 1 year, 6 months ago by ramana3327.

frederico_fonseca SSCoach Points: 16008 More actions · Answer 9

you are as suspected running on a VM machine - while your config may be ok for your workload you should monitor your usage to see if you need more memory on the server.

assuming you only have SQL running on that server (without any SSIS/SSRS running as well) I would increase the max memory on the SQL Server to between 10 and 12 GB.

changing the max dop to 2 and Cost Threshold for Parallelism to 50 (as suggested above) should alleviate some of your issue.

after this really its up to check which queries are still "misbehaving" and identify possible code/table (indexes) changes - this is normally the culprit of issues.