Recent site activity

Wait Event Docs‎ > ‎

SQL Server: CXPacket




CXPacket is wait caused by a parallel skew. What happens is that SQL runs a query in parallel and, for whatever reason, one of more of the threads lags behind. The ones that finish have to wait for the slower ones to catch up. That wait is the CXpacket wait

You can either reduce the max degree of parallelism for the entire server or, if it's a specific query that's giving the problem, you can add the maxdop hint to that query.

Be careful if you do the former, as it will affect the entire server. If you decide to go that route, I would suggest setting the server's max degree of parallelism to 4 (half the number of cores that you have) and see how it goes from there.
-- Gail Shaw

At least 75% of my clients have multi-cpu servers with insufficient RAM or IO capabilities (or both!), and thus suffer from significant CXPACKET waits on largish queries. It is often optimal in general in situations like that to simply set MAXDOP at the server level to some fraction of the total number of CPUs (1/4 to 1/2 usually). In other cases you can specify the maxdop as an OPTION for an individual query. 

NOTE: if you have hyperthreading enabled it gets MUCH worse. Consider disabling HT if you are seeing logs of CXPACKET waits first, then reevaluating and if necessary adjust MAXDOP. 

-- TheSQLGuru

Probably the BEST thing you can do (if able) is to a) max out the servers RAM (which is hopefully many, many GBs) and b) add more spindles to the IO mix to improve IO performance.

I also recommend (regularly on performance forums such as this) that you get a professional to come in (onsite or remote) and give your systems a performance review. There are a pleathora of things that can be done suboptimally that will hurt performance. :) 

-- The SQLGuru


CXPACKET waits are due to parallelism where the query optimizer decides to break up the IO and processing for a query into multiple threads that run concurrently then it gathers the data back together to provide the necessary output. In pretty much every case, the CPUs wait for IO streams because the IO subsystem is slow as molasses in February, relatively speaking. :D So by providing better IO capabilities you can feed those GHz-speed CPUs with data with fewer waits --> much faster parallel query performance. This is the 10000 foot view, btw. There is just a weeeee bit more to it than this.

-- The SQLGuru


Comments