SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 CXpacket wait and parallelism
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

meristema
Starting Member

Italy
8 Posts

Posted - 11/27/2012 :  13:27:02  Show Profile  Reply with Quote
Hello guys,
I'm try to isolate top waits for server instance on Sql Server 2008 and query returns this:

wait_type wait_time_s pct running_pct
CXPACKET 2047777.86 34.42 34.42
ASYNC_NETWORK_IO 1585911.24 26.66 61.08
PREEMPTIVE_OS_WAITFORSINGLEOBJECT 506923.31 8.52 69.60

CXPACKET seems excessive (you think?), on database the value "max degree of parallelism" is set to 0 and related cost is 5.
Maybe I have to increase Parallelism to 1 for reduce CXPACKET value?

Thanks for advice
Andrea

tkizer
Almighty SQL Goddess

USA
36648 Posts

Posted - 11/27/2012 :  14:16:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
Have you read through this? http://sqldoctor.idera.com/wait-stats/parallelism-hurry-up-and-wait/

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/27/2012 :  15:31:07  Show Profile  Visit chadmat's Homepage  Reply with Quote
How many Procs do you have (Cores)? Is your server on NUMA hardware? Is Hyperthreading enabled? In general, like the article Tara referenced, CXPACET waits are nothing to worry about. It just means you have parallel queries. But you should have MAXDOP configured properly, and 0 is too high (It means all available procs) under certain circumstances.

-Chad
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2008 Posts

Posted - 11/28/2012 :  01:53:19  Show Profile  Visit jackv's Homepage  Reply with Quote
Check which queries are running with parallelism and then determine why? For example are they frequent queries ? Are they long running datawarehouse queries? As mentioned , CXPACKET is not necessarily a negative wait stat.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Elizabeth B. Darcy
Starting Member

United Kingdom
39 Posts

Posted - 11/28/2012 :  07:05:56  Show Profile  Reply with Quote
I want to add couple of thoughts to what others have said:

1. While BOL suggests lowering the degree of parallelism to alleviate CXPACKET waits, setting it to 1 may be not be the right thing to do. Degree of parallelism set to 0 indictes no restrictions on parallelism (meaning, parallelize it as much as possible upto 64, subject to the cost threshold). When you set it to 1, you are swinging to the other extreme; i.e., instructing the server to use no parallelism at all. That can have a rather substantial negative impact on the overall performance.


2. Even more interesting to me in the data that you posted is the second and third highest wait types - ASYNC_NETWORK_IO and PREEMPTIVE_OS_WAITFORSINGLEOBJECT. That suggests that there are clients who are not processing the data fast enough. That in turn may be causing the CXPACKET waits. But, without more data and evidence to back it up, I am venturing into speculative terrority at this point.

3. As an alternative to changing degrees of parallelism at the server level, you could consider using the query hint MAXDOP to set a lower level of parallelism for queries that you suspect are causing the CXPACKET waits. If this question came up because of poor performance, inspect the execution plans of poorly performing queries for parallelism and add the MAXDOP hint.


________________________________________
-- Yes, I am indeed a fictional character.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2008 Posts

Posted - 11/30/2012 :  07:08:32  Show Profile  Visit jackv's Homepage  Reply with Quote
meristema - you haven't yet explained the nature of the queries - long running or short queries?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

meristema
Starting Member

Italy
8 Posts

Posted - 12/02/2012 :  05:23:53  Show Profile  Reply with Quote
quote:
Originally posted by jackv

meristema - you haven't yet explained the nature of the queries - long running or short queries?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com




Sorry for my delay, however the server have eight cores (dual CPU with 4 core) and does not have HT enable.

I don't know very well what kind of query are running if long, short or parallelism (how I can find this info?) , of course is a OLTP database for about 50 users with many read data (query) rather then write data.

NUMA is not active because this select return "0":
SELECT DISTINCT memory_node_id FROM sys.dm_os_memory_clerks

Vmware ESX 5 on HP Proliant DL380 G8 I'll try to find if NUMA is supported.

thanks very much
Andrea
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 12/02/2012 :  14:55:07  Show Profile  Visit chadmat's Homepage  Reply with Quote
With an OLTP workload, you should probably lower it to 4 or maybe even 2.

-Chad
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2008 Posts

Posted - 12/03/2012 :  01:49:02  Show Profile  Visit jackv's Homepage  Reply with Quote
@meristema - To find out the type of queries , create a server side trace and capture queries. Analyse these queries . To improve response time - as well as parallelism look at : 1)Data types – such as CLOBs in the table 2) Sorting 3) Memory 4)SQL Statistics



Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

meristema
Starting Member

Italy
8 Posts

Posted - 12/03/2012 :  10:29:41  Show Profile  Reply with Quote
quote:
Originally posted by chadmat

With an OLTP workload, you should probably lower it to 4 or maybe even 2.

-Chad



Now I've change it from 0 to 8 (the real cores number)
Go to Top of Page

meristema
Starting Member

Italy
8 Posts

Posted - 12/03/2012 :  12:47:24  Show Profile  Reply with Quote
quote:
Originally posted by jackv

@meristema - To find out the type of queries , create a server side trace and capture queries. Analyse these queries . To improve response time - as well as parallelism look at : 1)Data types – such as CLOBs in the table 2) Sorting 3) Memory 4)SQL Statistics

Jack Vamvas
--------------------
http://www.sqlserver-dba.com



thank you Jack, but I'm worried to enable trace on server for capture query because I think to create negative impacts on performance.

Could be enough check query statistics through DMV ?

Go to Top of Page

meristema
Starting Member

Italy
8 Posts

Posted - 12/03/2012 :  13:02:36  Show Profile  Reply with Quote
quote:
Originally posted by Elizabeth B. Darcy

I want to add couple of thoughts to what others have said:

2. Even more interesting to me in the data that you posted is the second and third highest wait types - ASYNC_NETWORK_IO and PREEMPTIVE_OS_WAITFORSINGLEOBJECT. That suggests that there are clients who are not processing the data fast enough. That in turn may be causing the CXPACKET waits. But, without more data and evidence to back it up, I am venturing into speculative terrority at this point.




@Elizabeth I guess that ASYNC_NETWORK_IO and PREEMPTIVE_OS_WAITFORSINGLEOBJECT are caused by a remote connection, because there is a remote Server (connected on VPN site) that query my DB every day. So it could make a network delay in async statistics.

Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2008 Posts

Posted - 12/04/2012 :  07:18:09  Show Profile  Visit jackv's Homepage  Reply with Quote
@meristema - you can get some clues by analysing queries spending long time in CPU. http://www.sqlserver-dba.com/2012/11/sql-server-performance-top-queries-by-total-cpu-time.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

meristema
Starting Member

Italy
8 Posts

Posted - 12/05/2012 :  12:48:22  Show Profile  Reply with Quote
quote:
Originally posted by jackv

@meristema - you can get some clues by analysing queries spending long time in CPU. http://www.sqlserver-dba.com/2012/11/sql-server-performance-top-queries-by-total-cpu-time.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com



Thanks Jack, I've execute query and get result on CVS file but I don't know how interpreter it, can I upload result to somewhere for your help?

thanks again


Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2008 Posts

Posted - 12/06/2012 :  15:37:13  Show Profile  Visit jackv's Homepage  Reply with Quote
Without actually viewing the list - one suggestion I can make is to start from the top query - Open in SSMS and analyse the queries for Hash Joins, Sorts, Filters. Try and improve the efficiency of the queries
As mentioned earlier - waits are normal .It's about understanding the context

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000