How to get the size of a spatial index?

Question

I want to see the size of a spacial index. I am usually using one of the following code blocks to get the indexes sizes of a given table, but none of them is working for a spatial index.

DECLARE @DataSource TABLE
(
    [TableName] SYSNAME
   ,[IndexName] SYSNAME
);

INSERT INTO @DataSource ([TableName], [IndexName])
VALUES ('[dbo].[Table]', 'Index');

SELECT OBJECT_NAME(INX.[object_id]) AS TableName
      ,INX.[name] AS IndexName
      ,INX.[index_id] AS IndexID
      ,8 * SUM(AU.[used_pages]) AS 'Indexsize(KB)'
FROM [sys].[indexes] AS INX
INNER JOIN [sys].[partitions] AS PAR
    ON PAR.[object_id] = INX.[object_id] 
    AND PAR.[index_id] = INX.[index_id]
INNER JOIN [sys].[allocation_units] AS AU 
    ON AU.[container_id] = PAR.[partition_id]
INNER JOIN @DataSource DS
    ON INX.[object_id] = OBJECT_ID(DS.[TableName])
    AND INX.[name]  = DS.[IndexName]
GROUP BY INX.[object_id]
        ,INX.[index_id]
        ,INX.[name]
ORDER BY OBJECT_NAME(INX.[object_id])
        ,INX.[index_id]

OR

CREATE TABLE #CompressionResults
(
     [object_name] SYSNAME
    ,[schema_name] SYSNAME
    ,[index_id] INT
    ,[partition_number] INT
    ,[size_with_current_compression_setting (KB)] BIGINT
    ,[size_with_requested_compression_setting (KB)] BIGINT
    ,[sample_size_with_current_compression_setting (KB)] BIGINT
    ,[sample_size_with_requested_compression_setting (KB)] BIGINT
)

INSERT INTO #CompressionResults
EXEC sp_estimate_data_compression_savings 'dbo', 'A', NULL, NULL, 'ROW' ;

SELECT CR.[object_name]
      ,IDX.[name]
      ,[size_with_current_compression_setting (KB)] / 1024  AS [current size (MB)]
      ,[size_with_requested_compression_setting (KB)] / 1024  AS [size after compression (MB)]
      ,([size_with_current_compression_setting (KB)] - [size_with_requested_compression_setting (KB)]) / 1024 AS [saved size (MB)]
FROM #CompressionResults CR
INNER JOIN [sys].[indexes] IDX
    ON CR.[index_id] = IDX.[index_id]
    AND OBJECT_ID(CR.[object_name]) = IDX.[object_id]
UNION ALL
SELECT CR.[object_name]
      ,'ALL'
      ,SUM([size_with_current_compression_setting (KB)]) / 1024  AS [current size (MB)]
      ,SUM([size_with_requested_compression_setting (KB)]) / 1024  AS [size after compression (MB)]
      ,SUM([size_with_current_compression_setting (KB)] - [size_with_requested_compression_setting (KB)]) / 1024 AS [saved size (MB)]
FROM #CompressionResults CR
GROUP BY CR.[object_name]

DROP TABLE #CompressionResults

Answer 1

There are a few types of indexes that exist differently than the main Clustered and NonClustered types: XML, Full Text, and Spatial (not sure about any others). These types of indexes are not simple re-orderings of the existing values, but different representations of complex data. SQL Server stores this data in internal tables ( sys.internal_tables ) that cannot be accessed directly (not through SELECT, DML, or DDL operations), presumably because the data is complex and not the standard b-tree type which is what Clustered and NonClustered indexes are.

Another fun aspect of XML and Spatial indexes is that they have two entries in sys.indexes, both with the same name, but:

• one entry is associated with the table that you created the index on and appears to be mostly just a place-holder record for what was declared. This record has no matching entry in sys.partitions for the same object_id and index_id. Spatial indexes are type 4 and XML indexes are type 3. The index_id of this entry matches the parent_minor_id column of sys.internal_tables.
• and the other entry is associated with the internal table and does have a matching entry in sys.partitions. This record is the Clustered Index for the internal table.

So, the reason why most queries out there fail to account for these special indexes is that instead of there being a single record that relates to sys.objects and sys.partitions / sys.dm_db_partition_stats, there are two records: one of them relating to sys.objects (expected) but nothing else (not expected), and the other relating to sys.partitions / sys.dm_db_partition_stats (expected) and sys.internal_tables (not expected).

I posted a query in this answer (here on DBA.SE) that should account for all index types. It aggregates the row counts and space usage across partitions and provides the space metrics in both KB and GB:

space usage on sys.allocation_units and sp_spaceused

The row count will show as 0 because the actual row count is taken from index_id of 0 (heap) or 1 (clustered), not to mention that it will also be a non-intuitive row count: my test table with a GEOGRAPHY column and 2 rows shows a row_count of 24 for the Spatial Index.

I just updated that query to return a few additional fields that give better insight into the "special" index types (Full Text, XML, Spatial, etc).

Answer 2

You need a slightly different query, since spatial indexes are tied to internal tables, not the base object. This does not aggregate or account for partitions, nor did I tweak it to drop into your current code; just meant to point you in the right direction. You can leave off either where clause for discovery obviously. And the joins to indexes and objects are only necessary if you need to output the table name, or index name, or individual rows per index - if you just want a sum of row counts or other data from the partition-related DMVs for all special indexes for a table - which will include FTS and XML indexes - you can leave out both joins).

SELECT
  [internal_table_name] = N'sys.' + t.name,
  table_name = o.name,
  index_name = i.name, 
  i.index_id, 
  p.*
FROM sys.internal_tables AS t
INNER JOIN sys.objects AS o
ON t.parent_object_id = o.[object_id]
INNER JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
INNER JOIN sys.partitions AS p -- dm_db_partition_stats probably more useful
ON t.[object_id] = p.[object_id]
AND i.index_id = p.index_id
WHERE o.name = N'<Name of table with spatial column(s)>'
  AND i.name = N'<Name of spatial index>';