Deducing from Wait Stats

  • 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

  • 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
  • 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

  • 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.

  • 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

  • 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.

  • 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

  • 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
  • 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 ..

  • 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