Skip to Content
Product Information

Maddening name changes for the same statistics between optdiag and sp_showoptstats

Just ranting here.  In Adaptive Server Enterprise (ASE) version 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:

<derivedStats>
  <clusterRatio>0.0000000000000000</clusterRatio>
  <indClusterRatio>0.9988941688270366</indClusterRatio>
  <dataClusterRatio>0.9995511552728524</dataClusterRatio>
  <spaceUtil>0.9984938994744494</spaceUtil>
  <IOEfficiency>0.4961593208933978</IOEfficiency>
</derivedStats>

 

Making the changes easier to read:

optdiag name change sp_showoptstats name
Data page cluster ratio -Data & -page clusterRatio
Index page cluster ratio -page indClusterRatio
Data row cluster ratio -row dataClusterRatio

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).  Also, the changes make the names sound like they conceptually overlap.

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

Be the first to leave a comment
You must be Logged on to comment or reply to a post.