How to get the size of a spatial index?

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.

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''
  AND i.name = N'';

StackExchange Database Administrators Q#130805, answer score: 4