CXpacket wait and parallelism - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

CXpacket wait and parallelism

Author

Topic

meristema
Starting Member

8 Posts

Posted - 2012-11-27 : 13:27:02

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

38200 Posts

Posted - 2012-11-27 : 14:16:28

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

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-27 : 15:31:07

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

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-11-28 : 01:53:19

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

Elizabeth B. Darcy
Starting Member

39 Posts

Posted - 2012-11-28 : 07:05:56

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.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-11-30 : 07:08:32

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

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

meristema
Starting Member

8 Posts

Posted - 2012-12-02 : 05:23:53

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

chadmat
The Chadinator

1974 Posts

Posted - 2012-12-02 : 14:55:07

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

-Chad

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-12-03 : 01:49:02

@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

meristema
Starting Member

8 Posts

Posted - 2012-12-03 : 10:29:41

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)

meristema
Starting Member

8 Posts

Posted - 2012-12-03 : 12:47:24

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 ?

meristema
Starting Member

8 Posts

Posted - 2012-12-03 : 13:02:36

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.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-12-04 : 07:18:09

@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

meristema
Starting Member

8 Posts

Posted - 2012-12-05 : 12:48:22

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

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-12-06 : 15:37:13

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources