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