Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix an XML Index Query That Falls Back to a Full Table Scan Because the XQuery Expression Doesn’t Match a PATH Index

- 21.10.26 - ErcanOPAK

🗄️ 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.

— Database Engineer

Related posts:

SQL Server: Fix a Parameterized Query Using a Bad Cached Execution Plan for Every Caller Because the...

SQL Server: Fix a Parameterized Query Using a Bad Cached Execution Plan for Every Caller Because the...

SQL: Making Complex Queries Readable with Common Table Expressions (CTE)

SQL: Making Complex Queries Readable with Common Table Expressions (CTE)

SQL Server — LEFT JOIN + WHERE Turns Into INNER JOIN

SQL Server — LEFT JOIN + WHERE Turns Into INNER JOIN

Views: 4

Post navigation

Photoshop: Fix Select and Mask’s Decontaminate Colors Leaving a Faint Color Fringe Around Cut-Out Hair
The AI Prompt That Turns Birthday Party RSVPs, Allergies, and Gift Ideas Into One Planning Checklist

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com