Add Max Degree of Parallelism to the SQL Assessment Intelligence Pack
Many organizations find themselves with a functioning Operations team without the advantage of a strong DBA group. While the existing recommendations/checks in the SQL Assessment Intelligence Pack are great, this setting out of box is always set to 0. The default setting of 0 is never the best practice setting based on server resources.
Is there a way to add check for this, based on the actual resources of the machine the DB is running on? Or at a minimum, alert if still set to the default of 0.
It is already there:
Type=SQLAssessmentRecommendation Recommendation:“Align the Max Degree of Parallelism option to the number of logical processors.”
I'll ask the content owner to describe how the check works and what it takes into account.
From a conversation today, we just don’t fire the recommendation because you set it to 0. The rule looks, based on the number of logical processors attached to SQL process, if you have set it to a too-high number. If zero is still a good fit for your machine, the recommendation doesn't get raised.
Christopher Mank commented
Great, thanks. I guess I didn't see it. Does it take into account if NUMA is enabled? If NUMA is enabled, this value should align to the number of CPUs per NUMA and not total number of logical CPUs.