The benefit for external and preemptive waits is far too low (MEMORY_ALLOCATION_EXT, PREEMPTIVE_*) #4517

Part of #4511, the sync of plan analysis changes from PerformanceStudio (PS) to PerformanceMonitor (PM). Port together with #4516.

Summary

ScoreWaitStats (BenefitScorer.cs:450) scores each wait from each thread's elapsed time minus its CPU time. External and preemptive waits keep the thread busy on CPU in the kernel. For those threads, elapsed time is about equal to CPU time, so these waits barely show in that math. The affected waits are MEMORY_ALLOCATION_EXT, RESERVED_MEMORY_ALLOCATION_EXT and the PREEMPTIVE_* waits.

PerformanceStudio

erikdarlingdata/PerformanceStudio@6b61d79 adds IsExternalWait for the MEMORY_ALLOCATION* and PREEMPTIVE_* prefixes. It scores these waits with a separate formula. The formula multiplies the wait's share of total CPU time by the sum of each operator's maximum per-thread CPU time. It then divides by the elapsed time. In the worked example in that commit, the formula gives 48.3%.

Port

PM has no IsExternalWait, no formula for external waits, and no helper for the maximum per-thread CPU time of an operator. PS added that helper, GetOperatorMaxThreadOwnCpuMs, next to GetOperatorOwnElapsedMs (PlanAnalyzer.cs:1387 in PM). Size M.

At 1899737f. dev has no later change to PerformanceMonitor.PlanAnalysis.