With our effort to talk about SQL Waits we come across another common wait called CXPacket. Last week we reviewed SOS_Scheduler_Yield.
If you see CXPacket waits on your supported system you can safely assume you have execution plans running parallel. Any time you have queries running parallel you will see CXPacket waits. CXPacket waits do not necessarily mean you have a problem with a query, you must dig further to determine that.
Most of the time having queries running parallel increases performance and speed of the execution. However there are times parallel execution is unexpected. If you have unexpected parallel execution there are some simple things that can be done to reduce the CXPacket waits. We will review these methods below.
Reducing Unexpected Parallel Execution:
There are a few ways to reduce the parallel execution.
- Ensure you instance max degree of parallelism setting is appropriate for the instance workload.
- You can find more info on this in my post here.
- Review the execution plan for the query. Table and index scans can cause a spike in unexpected cxpacket waits.
- Missing indexes can cause table and index scans, review your index strategy and add an index where needed.
- Stats on the tables and indexes could be old. These stats help the engine pick the most optimized path to retrieve data. Stats are used to develop estimated execution plans and are used by the engine.
Three methods above are some of the quickest ways to address the unwanted CXPacket waits. I just want to preach that seeing CXPacket waits on a system does not outright mean you have negative things happening. Parallelism in general is a good thing and helps performance in most cases.
Written by: Justin Figg a Sr. SQL Server DBA with over 10 years of IT experience with Windows OS, Windows Server and Microsoft SQL Server. Subscribe to my blog to receive notifications of newly posted blog entries.
Support VitaminDBA.comThe post Let’s talk SQL Server Waits: Topic 2 – CXPacket appeared first on VitaminDBA.
