I tried to reproduce what you're experiencing and couldn't make it happen, unfortunately (see the second part of this answer). If this was a bugfix between versions, it was pretty well undocumented: running this through Google produced absolutely nothing.
I suspect the problem is that the analysis query you're running is joining sys.dm_db_index_usage_stats
to sys.tables
. Sounds reasonable, except XML indexes don't have the same relationship to their base table as regular clustered and nonclustered indexes do.
The way XML indexes work, the indexes aren't created against the base table at all. Creating a primary XML index materializes (i.e., creates a clustered index of) the node table that gets generated from the XML data; secondary indexes are nonclustered indexes on the node table. Okay, fine, so what? The catch is that the materialized node table is an internal table, and does not appear in sys.tables
(have a look in sys.internal_tables
instead). So you can see why you won't get rows back for those indexes at all unless all the rows from the DMV are getting returned with an OUTER JOIN
in the query (and even then, you won't directly get a name for the table in that row).
To be honest, I only recently learned how XML indexes work, so I actually have to go back and revisit my own index analysis scripts as well (I suspect they have this exact flaw). Here's a quick base script I whipped together that should correct the problem:
SELECT
OBJECT_NAME(COALESCE(t.object_id, t2.object_id)) AS TableName,
i.name AS IndexName,
us.*
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.indexes i ON i.object_id = us.object_id AND i.index_id = us.index_id
LEFT OUTER JOIN sys.tables t ON t.object_id = us.object_id
LEFT OUTER JOIN sys.internal_tables it ON it.object_id = us.object_id
LEFT OUTER JOIN sys.tables t2 ON t2.object_id = it.parent_object_id
This was run against my local instance (64-bit Developer edition, 10.50.1617):
CREATE TABLE XmlIndexTest
(
Id int IDENTITY(1, 1) NOT NULL,
Data xml NOT NULL,
CONSTRAINT PK_XmlIndexTest
PRIMARY KEY CLUSTERED(Id)
)
GO
INSERT INTO XmlIndexTest(Data)
VALUES('<Invoice><LineItem Id="1" /></Invoice>')
CREATE PRIMARY XML INDEX IX_PrimaryXml ON XmlIndexTest(Data)
SELECT *
FROM XmlIndexTest
WHERE Data.exist('/Invoice/LineItem[@Id = 1]') = 1
SELECT i.name, us.*
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.indexes i ON i.object_id = us.object_id AND i.index_id = us.index_id
CREATE XML INDEX IX_SecondaryXml ON XmlIndexTest(Data)
USING XML INDEX IX_PrimaryXml FOR PATH
SELECT *
FROM XmlIndexTest
WHERE Data.exist('/Invoice/LineItem[@Id = 1]') = 1
SELECT i.name, us.*
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.indexes i ON i.object_id = us.object_id AND i.index_id = us.index_id
and got these results:

Note how the object_id
of the primary and secondary XML indexes is different than the object_id
of the table's clustered index.