Ewald Cress discusses superlatch promotion:
There are quite a few pieces of machinery that are involved in our little drama. First, I’ll introduce some instance-global settings:
- A flag that controls whether latch promotion is enabled at all. Although I don’t have any information about this, let’s assume that it will be enabled on any system that “warrants it”.
- A flag that controls whether cycle-based promotion is enabled. Again, I can’t currently tell you what determines this setting.
- sm_promotionThreshold, the current calculated cycle-based promotion threshold described in Part 3.
- sm_promotionUpperBoundCpuTicks, used as a ceiling value to prevent outliers from skewing stats. As described in Part 3, this is simply sm_promotionThreshold * 5.
- Trace flag 844, which lowers the threshold for non-cycle-based promotions.
- Trace flag 827, which causes each latch promotion to be noted in the SQL Server log (“Latch promotion, page %u:%u in database %u, objid %u.”)
Assume that the first flag is set on our system of interest, otherwise promotions won’t happen and we have nothing to talk about.
Read the whole thing.