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