Repository navigation
Query Store replica attribution on Managed Instance: still gated off on an unverified basis after #1872 #1886
Description
Spun off from #1872, which flipped Query Store replica attribution ON for Azure SQL Database on live GP + Hyperscale evidence and deliberately left Managed Instance alone.
The state after #1872
QueryStoreCollector.BuildPayloadBody:
bool hasReplicaAttribution = productVersion >= 16 || context.Target.IsAzureSqlDb;IsAzureSqlDb is false for Managed Instance, so MI falls through to the pure version gate — and MI under-reports PRODUCTVERSION the same way Azure SQL DB does (major 12), so in practice MI never attributes replica role at all, on any instance, regardless of what its engine actually supports. Every MI row stores the nvarchar(1) NULL placeholder.
That is unchanged behavior, not a regression — but #1872 makes it the odd one out, and the comment it left behind now names a probe nobody has scheduled:
IsAzureSqlDb is false for MI, so MI falls through to productVersion >= 16 and stays off until a probe on MI says otherwise.
This issue is that probe.
Why MI was excluded rather than swept in
The reasoning is #1844's and it is still sound — this is not an oversight to be "fixed" by pattern-matching on the Azure change:
- MI is not evergreen. Azure SQL DB is; MI's feature set follows a per-instance update policy, so "Azure means 2022+" is a claim about the fleet that does not transfer to a specific instance. An MI on an older update policy genuinely may not have the catalog.
- MI has no per-database path to verify on. Azure SQL DB's collector connects per database (Query Store collector never collects on Azure SQL Database #1836), so a bind failure there was at least observable per database. MI takes the on-prem enumeration path through
[db].sys.sp_executesql. - The failure mode is still asymmetric. A column that does not bind is not a NULL — it fails the whole payload for that database. Guessing wrong on MI trades one missing column for lost collection.
What needs doing
- Run the same two-value probe Release gate: live Azure SQL DB smoke for the #1833 + #1836 collector fixes (includes two unverified edges) #1848/Query Store replica attribution on Azure SQL DB: General Purpose probe now passes; needs one Hyperscale probe to lift the gate #1872 ran, on a real Managed Instance:
SELECT OBJECT_ID('sys.query_store_replicas'), COL_LENGTH('sys.query_store_runtime_stats', 'replica_group_id')
Non-NULL on both is the green light. Worth capturing SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('EngineEdition') and the instance's update policy in the same run, since the update policy is the whole reason MI is treated differently.
-
If it binds, decide between two shapes rather than assuming the Azure one:
|| context.Target.IsAzureManagedInstance, matching what Azure SQL DB now gets — only defensible if the catalog is present on the oldest update policy still in support, not just on whatever instance was to hand.- A real capability probe (the collector already runs a live
PRODUCTVERSIONprobe per cycle; extending it to answerCOL_LENGTH(...)would make the gate evidence-based per target instead of edition-based). This is the more honest fix for a non-evergreen platform and would also retire the version guess for good, but it is a bigger change than Query Store replica attribution on Azure SQL DB: General Purpose probe now passes; needs one Hyperscale probe to lift the gate #1872 was — hence a separate issue.
-
If it does not bind, record that in the gate comment so nobody re-runs this, exactly as Query Store replica attribution on Azure SQL DB: General Purpose probe now passes; needs one Hyperscale probe to lift the gate #1872 asked for the Hyperscale branch.
Note hasPlanType has the identical MI carve-out one line above and would be answerable by the same session — worth probing sys.query_store_plan.plan_type_desc at the same time rather than provisioning an MI twice.
Priority
Low, and the same reason #1872 was low applies: current behavior is correct and safe, this only recovers an attribution column, and a single MI is not the AG-blending scenario the feature was built for. Worth doing opportunistically the next time a Managed Instance is available — MI is the most expensive Azure SQL target to provision, so this should ride along with other MI work rather than justify its own spin-up.
🤖 Generated with Claude Code
Activity
erikdarlingdata commentedon Jul 31, 2026
Probe run on a real Managed Instance, 2026-07-31. Instance: GPv2 Gen5 4 vCores, westus3, provisioned fresh for this run and torn down after. Evidence verbatim.
Master context:
User-database context, through the collector's actual MI mechanism ([db].sys.sp_executesql, msdb standing in since catalog views are per-database):
Replicas catalog enumeration on a standalone MI:
What this settles:
Next step per the issue's own criteria: the simple gate shape (IsAzureManagedInstance joining the Azure SQL DB branch) is now defensible on evidence. The capability-probe alternative remains the more general design but is no longer required to unblock this.
Merge pull request #1935 from erikdarlingdata/fix/1886-mi-query-store…
erikdarlingdata commentedon Jul 31, 2026
Shipped in #1935, on the live evidence above. Both MI carve-outs flipped - hasReplicaAttribution and hasPlanType now include IsAzureManagedInstance (EngineEdition 8, the member the isNew gate already used) alongside IsAzureSqlDb. The issue's own bar for the simple shape was met by the probe: the catalog binds on a CU-policy instance, i.e. the SQL Server 2022 update policy, the most conservative one offered, and it binds through the collector's actual [db].sys.sp_executesql mechanism, not just in master. The gate comments carry the evidence inline so nobody re-runs the provisioning, including the fact that a standalone MI's sys.query_store_replicas is an EMPTY enumeration (harmless through the LEFT JOIN) where Azure SQL DB's is a static 4-row roles table. Watched-red pins discriminate per gate - each flip reddens a different assertion, so one pin cannot cover for the other - plus the counterfactual that box SQL Server below 2022 still gets neither column. The capability-probe redesign the issue sketched is preserved as #1934, best revisited before the next version-gated Query Store column.
Managed Instance joins the Query Store plan-type and replica-attribut…