June 23, 2015 at 7:00 am
Hi experts,
when I run Glenn berry's query 'sys.dm_os_wait_stats' I get:
(1) CXPACKET 89% AS Wait_Percentage
(2) Latch_EX 8% AS Wait_Percentage.
And then when I run sys.dm_OS_latch_stats I get:
(1) ACCESS_METHOD_DATABASE_PARENT 'wait_time_ms' = ' 4356235789'.
If I were to start trouble shooting this, where do I have to start? The server has:
MAXDOP setting is default (0)
'cost_threshold for parallelism' = 25.
Processor = 2 CPU 10 core each with 20 logical processors with NUMA enabled and hyperthreading.
Any suggestions please?
June 23, 2015 at 7:20 am
Dataset parent latch is taken when SQL's doing a parallel table scan. you're probably either missing indexes or have queries which are written so that they can't use indexes.
To be clear, the problem is not parallelism, it's inefficient queries. Identify your high CPU using queries, see if you're missing indexes, see if the queries are written so that they can't use indexes. Rectify identified problem. Repeat.
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
June 23, 2015 at 7:29 am
In addition to what Gail has said, I always recommend setting MAXDOP at the server level to something other than 0. I point people to this KB article, https://support.microsoft.com/en-us/kb/2806535, as a starting point. The main reason is that SQL will use ALL CPU's when executing a parallel query even if it using 2 would be more efficient. By using more CPU's it means the parent thread has more child threads to wait for and gather streams in order to complete the query.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 23, 2015 at 7:38 am
Thank you guys!!!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply