LATCH_EX and CXPACKET waitstates are too high

Question

LATCH_EX and CXPACKET waitstates are too high

aprathour.89

Default port

Points: 1495
More actions
February 24, 2015 at 7:31 am

#314956

Hi Everyone,
i have two problems:
1)
i have set maxdop=8 to my sql instance , but when i am looking through sp_who2 some query sessions are generating up 17 parellel threads.what may be the causes of this issue.
2) LATCH_EX and CXPACKET waitstates are too high , does high LATCH_EX and CXPACKET waitstates can cause to CPU spikes and how to troubleshoot what is causing to high LATCH_EX and CXPACKET.
-----------------
Aditya Rathour
SQL DBA
Not Everything that is faced can be changed,
but nothing can be changed until it is faced.

Viewing 4 posts - 1 through 3 (of 3 total)

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

lmarkum SSCertifiable Points: 7989 More actions · Answer 1

You also need to review the setting for cost threshold for parallelism. This is the cost that a query must reach before it can go parallel. The default is woefully low. I would encourage you to look at Paul Randall's blogs on these topics to get additional advice on how to proceed. To get the best help here, it would probably be good to run Paul's wait Stat query and post the results here.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Maxdop sets the limit for how many threads may be *running* at one time, not how many threads in total there are. So of those 17, at most 8 will be executing.

Generally, for 'too high' a CXPacket (for whatever too high means), you want to increase cost threshold for parallelism (30's a better starting point than 5) and then identify the queries running in parallel (especially look for parallel table scans as that's possibly the case of the latch_ex) and optimise them.

That said, CXPacket is not a problem in and of itself. It tells you that queries are running in parallel. Any time there's parallelism, there will be CXPacket waits and it's not an immediate sign of trouble.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aprathour.89 Default port Points: 1495 More actions · Answer 3

Thanks for your valuable reply,

I googled found Parallelism in SQL Server is for Operators involved( actually its bit more but for practical purpose note this) not for the query, thats why i am seeing more sessions than specified in MAXDOP.

I found following article that describes it in detail:

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/01/05/why-do-i-see-more-threads-per-spid-in-sysprocesses-than-maxdop.aspx

-----------------
Aditya Rathour
SQL DBA

Not Everything that is faced can be changed,
but nothing can be changed until it is faced.