August 14, 2017 at 9:05 am
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
August 14, 2017 at 9:15 am
Arsh - Monday, August 14, 2017 9:05 AMI 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
August 14, 2017 at 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
August 15, 2017 at 4:24 am
GilaMonster - Monday, August 14, 2017 9:15 AMArsh - Monday, August 14, 2017 9:05 AMI 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.
August 15, 2017 at 4:31 am
John Mitchell-245523 - Monday, August 14, 2017 9:17 AMCXPACKET 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
August 15, 2017 at 4:39 am
John Mitchell-245523 - Monday, August 14, 2017 9:17 AMCXPACKET 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.
August 15, 2017 at 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
August 15, 2017 at 6:17 am
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
August 16, 2017 at 4:54 am
John Mitchell-245523 - Tuesday, August 15, 2017 4:40 AMDo 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 ..
August 16, 2017 at 5:01 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply