Skip to Content
Feb 10, 2020 at 10:33 PM

statistic name changes from optdiag to sp_showoptstats

55 Views Last edit Feb 10, 2020 at 10:33 PM 2 rev

This is really isn't a question, as much a rant. Specifically, in 16.0 SP03, in the optdiag output, derived statistics for a non-clustered index look like this (for example):

  Derived statistics:                                                           
     Data page cluster ratio:    0.0000000000000000
     Index page cluster ratio:   0.9988941688270365
     Data row cluster ratio:     0.9995511552728523
     Space utilization:          0.9984938994744493
     Large I/O efficiency:       0.9923186417867956

But in the sp_showoptstats stored proc output has ambigously renamed the columns like so:


It's maddening to have the names slightly changed, and in ways that make it really hard to map back to the documentation (the documentation uses the original optdiag names).

It's basically the same problem for all-pages-locked tables with clustered indexes, except that Data page cluster ratio is the only cluster ratio that's filled in (non-zero)

Also the new definition for "Data row cluster ratio" is:

The data row cluster ratio is used to estimate the number of pages that need to be read while using the index to access the data pages.

But they meant to say "the number of data pages that need to be read". Ie., they're not talking about index fragmentation. The old help page for this was clearer.

Also, for others reading this, the Data page cluster ratio (aka "clusterRatio") is zero for non-clustered indexes on all-pages-locked tables. Ie., it's not a bug in this output.

And so it goes...