October 13, 2014 at 5:37 am
One of the production server CXPACKET wait percentage continuously using 47.89 % on today, yesterday and day before yesterday ……. This is OLTP environment database.
SELECT COUNT(DISTINCT memory_node_id) AS NUMA_Nodes FROM sys.dm_os_memory_clerks WHERE memory_node_id!=64
--NUMA_Nodes = 2
Get-WmiObject -namespace "root\CIMV2" -class Win32_Processor -Property NumberOfCores | select NumberOfCores
--NumberOfCores
4
4
What could be issues? Should I change the values up-to 8 on MAXTOP? Pls. suggest
The database is as below setting configured.
1. Cost of Threshold for parallelism = 50 (specify the Threshold where SQL server creates and execute the parallel plans.
2. Locks = 0
3. Max Degree of Parallelism = 10 (Limit the number processors to use in parallel plan executions)
4. Query wait= -1
Thanks
ananda
October 13, 2014 at 5:51 am
Why is that a problem?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2014 at 6:02 am
Thanks for reply..
I want to know this values (47%), is it Acceptable value? or can we decrease that value?
October 13, 2014 at 6:16 am
As above output NUMA values is 2. Does it means NUMA is configured in server H/W side? So there are 2 memory slat attached in that server.
From MS site
1.For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
2.For servers that have hyper threading enabled, the MAXDOP value should not exceed the number of physical processors.
3.For servers that have NUMA configured and hyper threading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.
I have doubt,
NUMA – Non uniform memory access
Hyper threading - ? Does it means total number of logical processors (CPU).
I checked one the server by using systeminfo command
System Type: x64-based PC
Processor(s): 2 Processor(s) Installed. / and 16 logical CPU available showing Task manager at performance tab.
As above configuration server. What could be optimum value for MXTOP? I think it should be value 8.
Pls. Help.
October 13, 2014 at 6:50 am
SQL Galaxy (10/13/2014)
I want to know this values (47%), is it Acceptable value? or can we decrease that value?
It's a value. There's not much of acceptable or unacceptable about it. There's normal for your system and not normal. If it's not normal for your system, then you need to identify what changed.
You can also look at tuning inefficient queries and making sure that you have indexes to support them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply