index_object_stats: stage the per-index definition metadata instead of correlating against the live catalog per index #3508

Summary

The index_object_stats collector's per-database body stages the three stats DMVs (dm_db_partition_stats, dm_db_index_usage_stats, dm_db_index_operational_stats) into #temps with one scan each — the #1135 fix — but it builds the per-index definition metadata in the final SELECT as correlated subqueries against the live catalog, evaluated per index row:

• key_columns — correlated FOR XML PATH over sys.index_columns ⋈ sys.columns
• included_columns — same again
• is_foreign_key / is_foreign_key_reference — two correlated EXISTS over sys.foreign_key_columns
• data_compression_desc — correlated TOP (1) over sys.partitions

That's ~5 correlated catalog probes per index. On a wide schema the cost scales with index count, which is the body-cost half of #3477: on that reporter's 54K-index instance the per-database pass averages ~28 s, versus ~1.1 s on a small schema.

sp_IndexCleanup — same delimited STUFF/FOR XML representation, but fast — does not correlate against the live catalog. It stages sys.index_columns/sys.columns into a #index_details temp once, then builds the key/include strings by correlating against that temp. The collector applied the staging technique to the stats DMVs but not to the definition reads.

Fix

Extend the collector's existing #temp staging to the definition metadata: stage sys.index_columns (+ sys.columns names), sys.foreign_key_columns, and per-index partition compression into #temps up front, then join/correlate to those in the final SELECT — structurally what sp_IndexCleanup already does. The emitted strings and flags are byte-identical, so the Stage-2 duplicate/subset dedupe and the FK-protection logic are unaffected; it is purely a plan-shape change.

Relationship to #3477

Separate lever, and they compound: the #3477 per-collector database scope cuts the width (dead databases), this cuts the per-database body (the correlated-catalog cost each database pays). #3477 predicted this ("if a per-database command is the outlier, there may also be a body-cost fix worth having regardless of scope").

Verification

Byte-for-byte output parity against the current body (same rows, identical key_columns/included_columns/flags), plus a before/after timing on a wide synthetic schema. IndexObjectStatsCollector is shared by Lite and Darling, so the change lands in both at once.