2

Can someone tell me if there is any sys table where I can find information about the size of XML indexes (both primary and secondary) in SQL Server?

So far I've been using this script to check for the size of an index:

SELECT
  t.name AS TableName
, i.name as IndexName
, SUM(a.total_pages) * 8 / 1024.00 as SizeInMB
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
JOIN sys.database_files df on df.data_space_id=a.data_space_id
GROUP BY t.name, i.name

but it seems that xml indexes don't have a partition_id allocated on creation.

And I also haven't found anything regarding a sys table or dmv for this particular information on msdn.

Sergiu
  • 432
  • 6
  • 18
  • 2
    Maybe this is of help: http://dba.stackexchange.com/questions/3957/how-do-i-track-the-usage-of-xml-indexes – rene Jun 13 '14 at 13:10
  • Based on the link you provided, replacing in the above query the `sys.tables` with the `sys.internal_tables` yielded the wanted result! Thank you so much once again! – Sergiu Jun 13 '14 at 13:25
  • 1
    But boy what a difference there is!! If the clustered index of the table has 23 GB, the primary XML index has 165 GB !! That's 7x bigger! I wonder if I place 3 more xml secondary indexes on it (for path, value and property), how much would that take? – Sergiu Jun 13 '14 at 13:30
  • You can post an answer your self as you soved it yourself (sort of) with attribution to the question and answer on dba.se although with <100 reputation you have to wait 8 hours before that is allowed. – rene Jun 13 '14 at 13:31
  • I just read the link you gave me and adapted accordingly. It's your finding so you should post the answer :) I will be more than happy to mark it as the correct answer. – Sergiu Jun 13 '14 at 13:37

0 Answers0