SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CXPACKET wait type


CXPACKET wait type

Author
Message
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12250 Visits: 8542
GilaMonster (1/4/2010)
Please post new questions in a new thread in future.

If you look while the query is running, there will be at least one thread that doesn't have a cxpacket, that has a different wait type. Whatever that is is what's holding the query up. What is it?

I usually recommend generic performance tuning (tune the query, tune the indexes) before panicking over CXPackets. It's quit common to see them on non-optimised queries.


A perfect example of this is this statement: " but in some cases I could greatly reduce query execution time (and CXPACKET waits) by using the OPTION LOOP JOIN"

that is a clear indication of a suboptimal query.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
jswong05
jswong05
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 476
chileu17 (10/21/2008)
Sorry SqlGuru Hehe I think I couldn't explain to you my question. I do understand your point, BUT how do I implement what you are talking about?More indexes?new transactions?I know that the most probable answer is that it depends on every case right lol well I have a dedicated server with no other apps running on it. It runs on Windows server 2003 with a HD of 150 gb with a redundant SAN. We have installed a RAID 5 on it Sad please any suggestions are welcome. Thanks in advance again.


Did you check the IO of this query? using set statistics io on, or any DBA tools, or just DMV -- use Hilary Cotter's blog he published a bunch I used all the time that I don't have to repeat here.
You can break the query into smaller transactions, better indexes to get better plan (you will see IO reduced). Increase RAM, replace with faster logical or physical drives, such as change RAID 5 to RAID 10, it depends how much overhaul you want? how much money you want to spend?

Jason
http://dbace.us
:-P
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12250 Visits: 8542
jswong05 (4/9/2010)
chileu17 (10/21/2008)
Sorry SqlGuru Hehe I think I couldn't explain to you my question. I do understand your point, BUT how do I implement what you are talking about?More indexes?new transactions?I know that the most probable answer is that it depends on every case right lol well I have a dedicated server with no other apps running on it. It runs on Windows server 2003 with a HD of 150 gb with a redundant SAN. We have installed a RAID 5 on it Sad please any suggestions are welcome. Thanks in advance again.


Did you check the IO of this query? using set statistics io on, or any DBA tools, or just DMV -- use Hilary Cotter's blog he published a bunch I used all the time that I don't have to repeat here.
You can break the query into smaller transactions, better indexes to get better plan (you will see IO reduced). Increase RAM, replace with faster logical or physical drives, such as change RAID 5 to RAID 10, it depends how much overhaul you want? how much money you want to spend?


jswong05, please note the last post date on threads when you are replying to them. this one hasn't been touched in months. Smile

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
jswong05
jswong05
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 476
flyme4ual (10/14/2008)
Hi guys,

I'm seeing CXPACKET wait types in one of my database running SQL2005 which causing the CPU hit 100%. The server is 64 bit Windows 2003 with 8 CPU. Several tables contain over 10 million rows. Does anyone encounter this issue before and what was done to fix the problem?

Thanx.

When you started this question, you didn't provide what you have looked at, such as is your disk fragmented? index fragmented?
Did you query DMV for the top CPU sessions like so? (What do you see?) Once picked up the CPU culpit, did you interrigate the query efficiency with index scheme? I can go on and on ..... You have to check into a specific culpit, we can then advise you a solution.

WITH QPLAN AS
(
SELECT TOP 10 SUM(QS.TOTAL_WORKER_TIME) AS
TOTAL_CPU_TIME, SUM(QS.EXECUTION_COUNT) AS
TOTAL_EXECUTION_COUNT, SUM(QS.TOTAL_WORKER_TIME)/SUM(QS.EXECUTION_COUNT) AS EACHEXECUTION, COUNT(*) AS
NUMBER_OF_STATEMENTS, SQL_TEXT.TEXT,
QS.PLAN_HANDLE FROM SYS.DM_EXEC_QUERY_STATS
QS CROSS APPLY
SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) AS SQL_TEXT
GROUP BY SQL_TEXT.TEXT,QS.PLAN_HANDLE
ORDER BY SUM(QS.TOTAL_WORKER_TIME) DESC
)
SELECT * from QPLAN CROSS APPLY sys.dm_exec_query_plan(QPLAN.PLAN_HANDLE)

Jason
http://dbace.us
:-P
jswong05
jswong05
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 476
Yes, you are right. I have not come in here for months, just reading as new. I did not see his comments that he resolved the problem.
My point is people tends to post "my system does not work" missing the precise description or what thye have tried to resolve the problem. Is he using SP2 (no mentioned)? That is why skyview answers were provided to him.

Jason
http://dbace.us
:-P
Mani-584606
Mani-584606
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 1998
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.


where to verify whether hyperthreading is enabled or not?

We have Windows 2003 R2 enterprise edition x64 and CPU is PIII Xeon with speed 2834 MHZ

Thanks
Gail Wanabee
Gail Wanabee
Right there with Babe
Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)

Group: General Forum Members
Points: 726 Visits: 1339
(I know the last post on this thread is over 2 months old but no one answered the last question posted.)

You will most likely find the enabling or disabling of hyperthreading to be in your server's BIOS.

LC
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3127 Visits: 836
For me, CXPACKET was fixed by not allowing SQL to have all the processors and all the memory. I removed one of the processors (Properties | Processor of the server - do not "Automatically set processor affinity mask for all processors") and then allocating some of the memory for the system. We had reporting services eating up the memory and gave it 8 Gig of the 32 Gig available and this straightened up most of the difficulty.

Jamie
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12250 Visits: 8542
Jaimie, please don't post to threads that were started 2.5 years ago . . . :-)

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
SKYBVI
SKYBVI
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1563 Visits: 3239
aNY SOLUTION?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search