How to Improve query by reducing CXPACKET WAIT type with simple option?

 

Dear All,

I am sure you have faced this problem that when you running query like Aggregation , grouping etc then due to default setting the query uses all the CPU available to machine and this case is parallelism.

Now you are thinking its very good your query will be faster if it distributed amount the multiple processor.

but sometime this is not the case due to this distribution suppose one process is processing slowly then  overall your combine result have to wait this wait is “CXPACKET WAIT”

Now you are wondering how to handle this. so no worries Microsoft provided option to tweak this setting with MAXDOP (Maximum degree of Parallelism ) option.

You can change overall SQL Server setting or for particular query.

see below screen from where you can change MAXDOP settings.

Now the default value is “0” . It means can use all the CPU.

Now this is not we generally prefer instead of this we can use MAXDOP option in query option.

But before doing this you just need to cross check whether you require it or not ?

if your CXPACKET wait type  if too much then you can use this option

SELECT ISNULL(MAX(EnrollmentHistoryId),0) AS maxenrmollmentHistoryId ,     
           se.StudentId      
       FROM dbo.StudentEnrollment se WITH (NOLOCK)       
       INNER JOIN dbo.student s WITH (NOLOCK) ON s.studentId = sh.studentId       
                             AND s. YearId = @YearID
       WHERE sh.Statusid = @StatusActivateId 
       GROUP BY sh.StudentID
    OPTION (MAXDOP 1)

in the above query we used MAXDOP 1 which means query use only once CPU.

With this option the CXPACKET will reduce but it may be possible your query may take time so be careful.

But this is good option. It helped me to reduce the time.

Lets try if this can help you.

Enjoy  learning :)

Thanks & Best Regards,

Rajat Jaiswal