本文深入探讨了SQL查询优化策略，并详细介绍了如何通过查询分析得出的统计数据来重建索引，以提高数据库性能。重点展示了使用系统动态管理视图（sys.dm_db_index_physical_stats）获取数据碎片百分比并按此排序，从而识别需要优化的索引。同时，通过ALTER INDEX语句执行索引重建操作，进一步提升查询效率。
SELECT a.index_id,name,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'iWOMTrackData'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id order by avg_fragmentation_in_percent desc;
GO
ALTER INDEX IX_Item_Title_BID_State_Item_ID ON SEC_Item REBUILD;
go
FROM sys.dm_db_index_physical_stats (DB_ID(N'iWOMTrackData'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id order by avg_fragmentation_in_percent desc;
GO
ALTER INDEX IX_Item_Title_BID_State_Item_ID ON SEC_Item REBUILD;
go
扫一扫
77
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
