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


Deducing from Wait Stats


Deducing from Wait Stats

Author
Message
Arsh
Arsh
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4389 Visits: 888
Hi Everybody,
The wait stats on the database I am assigned for performance tuning , shows that the PAGEIOLATCH_SH and CXPACKETS share about 60% of the whole lot( ASYNC_NETWORK_IO too jumps sometimes but I checked it's due to the backups running at that time). Is this usual ? I mean is there any guideline which suggests certain threshold should be considered as not normal and looked-into ?
Basing on the findings in conjunction with the IO STALLS, I listed disk bottlenecks as an issue at the storage too as large number of 'pending-status' appears as 1 (pending) for the same files and for same sessions. For the CXPACKETS , parallelism reduction is also under consideration for some SPs . Any comments on this ?

Arshad
GilaMonster
GilaMonster
SSC Guru
SSC Guru (842K reputation)SSC Guru (842K reputation)SSC Guru (842K reputation)SSC Guru (842K reputation)SSC Guru (842K reputation)SSC Guru (842K reputation)SSC Guru (842K reputation)SSC Guru (842K reputation)

Group: General Forum Members
Points: 842642 Visits: 48504
Arsh - Monday, August 14, 2017 9:05 AM
I mean is there any guideline which suggests certain threshold should be considered as not normal and looked-into ?

No. Because what's normal for your server may indicate a severe problem on mine.

For the CXPACKETS , parallelism reduction is also under consideration for some SPs


Excessive parallelism is usually due to inefficient queries or poor indexing. Focus on that first. Especially since a common cause of PageIOLatch waits is inefficient queries that access too much data and hence churn the buffer pool.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)

Group: General Forum Members
Points: 122504 Visits: 18761
CXPACKET just means you have parallelism. It doesn't on its own mean you have a problem with parallelism. Please don't blindly reduce parallelism just for the sake of it. How many processors do you have and what are your max degree of parallelism and cost threshold for parallelism? Are you seeing any memory pressure? Do you have anything going on that involves lots of data churn - batch processes or index maintenance, for example?

I recommend you have a look at Paul Randal's posts on wait types if you're new to this stuff.

John
Arsh
Arsh
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4389 Visits: 888
GilaMonster - Monday, August 14, 2017 9:15 AM
Arsh - Monday, August 14, 2017 9:05 AM
I mean is there any guideline which suggests certain threshold should be considered as not normal and looked-into ?

No. Because what's normal for your server may indicate a severe problem on mine.

For the CXPACKETS , parallelism reduction is also under consideration for some SPs


Excessive parallelism is usually due to inefficient queries or poor indexing. Focus on that first. Especially since a common cause of PageIOLatch waits is inefficient queries that access too much data and hence churn the buffer pool.

Did a test on one of the objects that had indexes with lot a fragmentation . Defragmenting them and replacing a costly index scan along with some code modification decreased the IO and helped improve response time . Thanks Gail.

Arsh
Arsh
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4389 Visits: 888
John Mitchell-245523 - Monday, August 14, 2017 9:17 AM
CXPACKET just means you have parallelism. It doesn't on its own mean you have a problem with parallelism. Please don't blindly reduce parallelism just for the sake of it. How many processors do you have and what are your max degree of parallelism and cost threshold for parallelism? Are you seeing any memory pressure? Do you have anything going on that involves lots of data churn - batch processes or index maintenance, for example?

I recommend you have a look at Paul Randal's posts on wait types if you're new to this stuff.

John

Yeah I agree John . Had read the article by Paul Randal sometime back. Considering only those SP's / Queries for reduction in parallelism that have got impacted due to it. Kendra Little's article is also helpful . Also any comment on Microsoft article on recommending to keep MAXDOP of 8 for server with 1 NUMA and more than 8 processors. Ours is 1 NUMA (the default) and 20 processors ,64 GB Mem and 1 TB disk .

Arshad

Arsh
Arsh
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4389 Visits: 888
John Mitchell-245523 - Monday, August 14, 2017 9:17 AM
CXPACKET just means you have parallelism. It doesn't on its own mean you have a problem with parallelism. Please don't blindly reduce parallelism just for the sake of it. How many processors do you have and what are your max degree of parallelism and cost threshold for parallelism? Are you seeing any memory pressure? Do you have anything going on that involves lots of data churn - batch processes or index maintenance, for example?

I recommend you have a look at Paul Randal's posts on wait types if you're new to this stuff.

John

Current MAXDOP is the default and the cost threshold for parallelism is also the default at 5 (considering increasing this). Some batch process and some BI reports with each reports scanning through average 20 million rows. I just inherited this mischievous monkey. Index maintenance hardly done it seems , as I see 30 % of the tables with over 90 % fragmentation. Out of the 64 GB memory , the max is at 54 GB and stats show SQL actually used about 45 GB. Thank you.

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)

Group: General Forum Members
Points: 122504 Visits: 18761
Do you have a link to the article, please? Our standard is as follows:
· For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
· For servers that have hyperthreading enabled, the MAXDOP value should not exceed the number of physical processors.
· For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.

I would recommend you increase your cost threshold for parallelism to something between 30 and 50. The default of 5 is from a long time ago and based on very different hardware from what is available now

John
GilaMonster
GilaMonster
SSC Guru
SSC Guru (842K reputation)SSC Guru (842K reputation)SSC Guru (842K reputation)SSC Guru (842K reputation)SSC Guru (842K reputation)SSC Guru (842K reputation)SSC Guru (842K reputation)SSC Guru (842K reputation)

Group: General Forum Members
Points: 842642 Visits: 48504
How have you got 20 cores in one NUMA node?
Can you post the NUMA configuration from the error log?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Arsh
Arsh
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4389 Visits: 888
John Mitchell-245523 - Tuesday, August 15, 2017 4:40 AM
Do you have a link to the article, please? Our standard is as follows:
· For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
· For servers that have hyperthreading enabled, the MAXDOP value should not exceed the number of physical processors.
· For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.

I would recommend you increase your cost threshold for parallelism to something between 30 and 50. The default of 5 is from a long time ago and based on very different hardware from what is available now

John

John , this is the link https://support.microsoft.com/en-in/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi
The NUMA config is left to its default as I checked ..

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)

Group: General Forum Members
Points: 122504 Visits: 18761
I think I'd be happy to go with the recommendations therein. Don't be afraid to tweak the settings if necessary to improve performance, though.

John
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