时间：2022-09-28 09:39:36 | 栏目：MsSql | 点击：次
1、在B-tree索引中，表数据按照聚集索引的排序进行物理存储，若聚集索引离散化比较严重，那么可能会出现较为严重的碎片化问题；
2、随着业务的DML操作，会伴随着数据页分裂的情况，这种情况下也会导致表空间碎片化问题;
3、大表通过delete清理无效历史数据，delete产生碎片化空间；
表空间碎片化越严重越容易影响对该表的查询效率，这是因为当表碎片化比较严重时，数据库根据执行计划扫描满足需求的数据页会扫描较多“无效页面”，导致查询操作需要更多的IO消耗。
1、在SQL Server中，可以通过DBCC SHOWCONTIG的方式查看表空间碎片化的一些统计信息，具体语法如下：
--查看数据库中所有索引的碎片信息
use ${数据库名}
DBCC SHOWCONTIG WITH ALL_INDEXES
--查看指定表的所有索引的碎片信息
DBCC SHOWCONTIG (${表名}) WITH ALL_INDEXES
--查看指定表、指定索引的碎片信息
DBCC SHOWCONTIG (${表名},${索引名})
2、通过sys.dm_db_index_physical_stats()查看索引碎片化
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'db1'), OBJECT_ID(N'db1.dbo.users'), NULL, NULL , 'LIMITED'); SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'db1'), OBJECT_ID(N'db1.dbo.users'), NULL, NULL , 'DETAILED');
重点关注：
3、通过统计信息查看数据库碎片化空间Top表信息
SELECT
db_name() as DbName,
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间MB,
SUM(a.used_pages) * 8 AS 总使用空间KB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总使用空间MB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS 碎片化空间KB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS 碎片化空间MB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.is_ms_shipped = 0
AND i.OBJECT_ID > 0
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
总共占用空间MB desc
由于表数据是根据聚集索引排序进行物理存储，所以当表碎片化比较严重时，可以通过对聚集索引的重新组织来进行碎片化空间回收，重建索引的方式也有比较多方式，主要如下：
该方式其实就是将碎片化比较严重的表，先通过drop index删除其聚集索引，然后通过create index或者alter table重建聚集索引。该方式的特点是：
使用DROP_EXISTING进行重建索引，也是对聚集索引的删除重建，但是该方式在方法一的基础上做了一些优化：
基本语法：
CREATE INDEX ${index_name} ON T(${index_col}) WITH (DROP_EXISTING = ON)
DBCC DBREINDEX也是通过对索引的删除以及重建来实现碎片化回收。根据数据库版本（企业版or非企业版）以及索引类型（非聚集or聚集），该操作是可以实现在线或者离线操作。
基本语法：
-- 重建指定索引
USE ${db_name};
GO
DBCC DBREINDEX ('${schema_name}.${table_name}', ${index_name},80);
GO
-- 重建指定表全部索引
USE ${db_name};
GO
DBCC DBREINDEX ('${schema_name}.${table_name}', ' ', 70);
GO
该方式的实现逻辑与以上三种大有不同，DBCC INDEXDEFRAG并非完全重新组织整张表的b-tree结构：
DBCC INDEXDEFRAG按照索引键的逻辑顺序，通过压缩索引页里的行然后删除那些由此产生的不必要的碎片化数据页、删除完全碎片化数据页面的方式来进行碎片化空间的回收
该方式执行期间不阻塞业务读写操作
该方式下可回收的碎片化空间效果可能不如以上三种索引重建的方式
基本语法：
DBCC INDEXDEFRAG (${db_name}, '${schema_name}.${table_name}', ${index_name});
需要注意的是，在SQL Server数据库，我们对表空间数据进行碎片化处理、或者truncate清空无效历史数据，这些释放出来的空间只是空出来，当有新数据写入时，优先使用这些空出来的数据页，而不是再向OS申请新的数据空间扩展。所以这部分并不会直接释放给OS，如果我们想要达到降低整个OS的磁盘空间使用率的话，还需要对数据库的数据文件进行收缩。
1、检查数据文件空间使用率
-- 检查数据库文件空间使用率
SELECT a.name [文件名称] ,cast(a.[size]*1.0/128 as decimal(12,1)) AS [文件设置大小(MB)] ,
CAST( fileproperty(s.name,'SpaceUsed')/(8*16.0) AS DECIMAL(12,1)) AS [文件所占空间(MB)] ,
CAST( (fileproperty(s.name,'SpaceUsed')/(8*16.0))/(s.size/(8*16.0))*100.0 AS DECIMAL(12,1)) AS [所占空间率%] ,
CASE WHEN A.growth =0 THEN '文件大小固定，不会增长' ELSE '文件将自动增长' end [增长模式] ,CASE WHEN A.growth > 0 AND is_percent_growth = 0
THEN '增量为固定大小' WHEN A.growth > 0 AND is_percent_growth = 1 THEN '增量将用整数百分比表示' ELSE '文件大小固定，不会增长' END AS [增量模式] ,
CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN cast(cast(a.growth*1.0/128as decimal(12,0)) AS VARCHAR)+'MB'
WHEN A.growth > 0 AND is_percent_growth = 1 THEN cast(cast(a.growth AS decimal(12,0)) AS VARCHAR)+'%' ELSE '文件大小固定，不会增长' end AS [增长值(%或MB)] ,
a.physical_name AS [文件所在目录] ,a.type_desc AS [文件类型]
FROM sys.database_files a
INNER JOIN sys.sysfiles AS s ON a.[file_id]=s.fileid
LEFT JOIN sys.dm_db_file_space_usage b ON a.[file_id]=b.[file_id] ORDER BY a.[type]
2、收缩数据文件
USE [${db_name}]
GO
DBCC SHRINKDATABASE(N'${db_name}' )
GO