🗄️ The XML Index That’s Sitting There Unused
SQL Server’s secondary XML indexes only get used by the optimizer when a query’s XQuery expression matches the specific pattern the index was built for. A PATH secondary index is optimized for queries that filter on an exact, fully-specified element path, but if the actual predicate uses a wildcard step, a relative path, or a different axis than expected, SQL Server silently falls back to shredding every row’s XML blob at query time instead of using the index at all, with no error and a query plan that just shows an expensive XML reader operator instead of an index seek.
🔎 The Problem
-- Table with a typed XML column and a PATH secondary index built
-- specifically for queries filtering on exact order status paths:
CREATE XML INDEX IX_Orders_XML_Primary ON Orders(OrderData);
CREATE XML INDEX IX_Orders_XML_Path ON Orders(OrderData)
USING XML INDEX IX_Orders_XML_Primary FOR PATH;
-- The query the PATH index was actually built to accelerate:
SELECT OrderId FROM Orders
WHERE OrderData.exist('/Order/Status[. = "Shipped"]') = 1;
-- Uses the PATH index correctly - fast index seek in the plan.
-- A seemingly equivalent query someone writes later, using a
-- descendant axis instead of the exact expected path:
SELECT OrderId FROM Orders
WHERE OrderData.exist('//Status[. = "Shipped"]') = 1;
-- Returns the exact same logical result, but the descendant axis
-- doesn't match the specific path pattern the PATH secondary index
-- was optimized for. The optimizer silently falls back to shredding
-- every row's full XML blob to evaluate the predicate - no error, no
-- warning, just a plan with an expensive XML Reader operator where
-- an index seek used to be, and a query that gets steadily slower as
-- the table grows.
✅ Fix: Match the XQuery Pattern to What the Index Actually Supports
- Write XQuery predicates using the exact, fully-qualified element path the PATH index was built against, rather than a descendant axis, since the PATH secondary index is specifically optimized for exact path matches, not for logically equivalent but differently-expressed queries.
- Check the actual execution plan, not just query correctness, for any query against an XML column that’s supposed to be indexed – look specifically for an XML Reader or table scan operator instead of an index seek, since that’s the concrete sign the index isn’t being used even though the query still returns correct results.
- If queries genuinely need to search with varying or unpredictable path patterns, consider promoting frequently-queried XML values into their own relational, indexed columns instead of relying on XQuery pattern-matching against the XML index to cover every possible way the data might be queried.
🚨 Why This Is Dangerous
- The query returns completely correct results either way, so there’s nothing about its output that signals the index isn’t being used – the only visible symptom is performance degrading as the table grows, which is easy to attribute to the table just getting bigger instead of this specific query never using its index at all.
- A small, seemingly stylistic rewrite of an XQuery expression – swapping an axis, adding a wildcard step, restructuring a predicate – can silently cross from matching the PATH index to not matching it, with nothing in SQL Server flagging the change as consequential, so a performance regression can trace back to a one-line query edit that looked purely cosmetic.
An XML PATH index doesn’t understand that two XQuery expressions mean the same thing – it only recognizes the one shape it was actually built for, and anything else gets the same full scan as having no index at all.

