如何查看SQL Server 2022中空间索引的使用情况

针对SQL Server中空间索引无法通过sys.dm_db_index_usage_stats查看使用情况的问题，可通过以下方法跟踪其扫描、更新等操作的触发频率：

1. 扩展事件（Extended Events） #

创建扩展事件会话，专门捕获空间索引的相关操作事件，这是SQL Server推荐的轻量级跟踪方案：

CREATE EVENT SESSION [TrackSpatialIndexUsage] ON SERVER 
ADD EVENT sqlserver.spatial_index_seek(
    ACTION(sqlserver.database_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.spatial_index_scan(
    ACTION(sqlserver.database_id,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.index_update(
    WHERE ([object_type]='INDEX' AND [index_type]='SPATIAL'))
ADD TARGET package0.event_file(SET filename=N'TrackSpatialIndexUsage.xel')
WITH (STARTUP_STATE=OFF)

启动会话后，可通过以下脚本读取事件文件，统计各类操作的触发次数：

SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text,
    event_data.value('(event/action[@name="username"]/value)[1]', 'varchar(100)') AS username,
    COUNT(*) AS occurrence_count
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('TrackSpatialIndexUsage*.xel', NULL, NULL, NULL)
) AS x
GROUP BY 
    event_data.value('(event/@name)[1]', 'varchar(50)'),
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)'),
    event_data.value('(event/action[@name="username"]/value)[1]', 'varchar(100)')
ORDER BY occurrence_count DESC

2. 查询sys.dm_db_index_operational_stats #

该动态管理视图可返回索引的操作统计数据，间接反映空间索引的使用情况：

SELECT 
    i.name AS index_name,
    o.name AS table_name,
    ios.leaf_insert_count,
    ios.leaf_delete_count,
    ios.leaf_update_count,
    ios.range_scan_count
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
JOIN sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios 
    ON i.object_id = ios.object_id AND i.index_id = ios.index_id
WHERE i.type_desc = 'SPATIAL' AND o.type <> 'S'
ORDER BY o.name, i.name

其中range_scan_count对应空间索引的扫描次数，leaf_insert/update/delete_count对应索引的更新操作次数。

3. SQL Server Profiler（临时场景使用） #

虽已被扩展事件取代，但临时快速跟踪可使用Profiler，选择以下事件并筛选空间索引：

• Spatial Index Seek
• Spatial Index Scan
• Index Update

内容的提问来源于stack exchange，提问作者Robert Sievers