第一阶段：现状查询
1. 查询数据库物理与实际使用大小
通过此脚本可以明确磁盘占用、纯数据大小以及浪费的空闲空间
SELECT name AS FileName, size*8/1024.0 AS TotalSize_MB, CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)*8/1024.0 AS UsedSize_MB, (size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT))*8/1024.0 AS FreeSize_MB FROM sys.database_files;
2. 查询索引碎片情况
碎片率超过 30% 会严重影响查询性能
SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS indexstatsINNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_idWHERE indexstats.avg_fragmentation_in_percent > 30ORDER BY indexstats.avg_fragmentation_in_percent DESC;
3. 清除无用表
在收缩数据库前，优先删除以 tmp、temp、bak 开头的废弃表，这是最真实的空间释放。
SELECT 'DROP TABLE [' + s.name + '].[' + t.name + '];'FROM sys.tables tJOIN sys.schemas s ON t.schema_id = s.schema_idWHERE t.name LIKE 'tmp%' OR t.name LIKE 'temp%' OR t.name LIKE 'bak%';
建议不要使用全自动收缩，而是手动执行并“留有余地”。因后续在重建索引时，SQL Server 需要额外的空间来存放临时数据，分安全截断和按需截断。
DBCC SHRINKFILE (1, TRUNCATEONLY);
按需收缩（保留 10% 缓冲空间，重组并释放空间）：DBCC SHRINKDATABASE (N'dbname', 10);
修复索引碎片（Rebuild）
收缩操作会导致索引碎片飙升。必须在收缩后（建议业务低峰期）进行重建。
手动重建单个索引
ALTER INDEX [索引名] ON [表名] REBUILD;
建议使用批量生成重建脚本（针对碎片率 >30% 的所有索引）：针对性强：它只针对 avg_fragmentation_in_percent > 30 的索引，不会浪费资源去重建那些本来就很整齐的索引。
自动化：你不需要手动去一个个输入表名和索引名，它通过系统视图 sys.indexes 和 sys.tables 自动帮你完成了拼接工作。
安全可控：因为它只是生成语句，你可以先审查一遍，剔除掉那些你不想处理的表（比如某些 tmp 表），然后再手动执行。
SELECT 'ALTER INDEX [' + i.name + '] ON [' + s.name + '].[' + t.name + '] REBUILD;'FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS avg_fragJOIN sys.indexes AS i ON avg_frag.object_id = i.object_id AND avg_frag.index_id = i.index_idJOIN sys.tables AS t ON i.object_id = t.object_idJOIN sys.schemas AS s ON t.schema_id = s.schema_idWHERE avg_frag.avg_fragmentation_in_percent > 30 AND i.name IS NOT NULL;
核心避坑总结
先清理再收缩：先 DROP 无用表，再执行 SHRINK。
收缩必有碎片：SHRINK 操作会像打乱拼图一样破坏索引物理顺序，导致查询变慢。
重建才是终点：只有完成了 REBUILD，数据库才算真正完成了优化。
低峰期操作：重建索引会锁表并消耗高 I/O，请务必安排在深夜或凌晨执行。
阅读原文：原文链接
该文章在 2026/3/13 11:41:20 编辑过
400 186 1886
