| Author |
 Topic  |
|
|
meristema
Starting Member
Italy
8 Posts |
 Posted - 11/27/2012 : 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
USA
35007 Posts |
|
|
chadmat
The Chadinator
USA
1957 Posts |
Posted - 11/27/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1769 Posts |
Posted - 11/28/2012 : 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
United Kingdom
39 Posts |
Posted - 11/28/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1769 Posts |
Posted - 11/30/2012 : 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
Italy
8 Posts |
Posted - 12/02/2012 : 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
USA
1957 Posts |
Posted - 12/02/2012 : 14:55:07
|
With an OLTP workload, you should probably lower it to 4 or maybe even 2.
-Chad |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1769 Posts |
Posted - 12/03/2012 : 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
Italy
8 Posts |
Posted - 12/03/2012 : 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
Italy
8 Posts |
Posted - 12/03/2012 : 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
Italy
8 Posts |
Posted - 12/03/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1769 Posts |
|
|
meristema
Starting Member
Italy
8 Posts |
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1769 Posts |
Posted - 12/06/2012 : 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 |
|
|
| |
Topic |
|
CXpacket wait and parallelism
