SQL Server表空間碎片化回收的實現

--檢視資料庫中所有索引的碎片資訊
use ${資料庫名}
DBCC SHOWCONTIG WITH ALL_INDEXES 
--檢視指定表的所有索引的碎片資訊
DBCC SHOWCONTIG (${表名}) WITH ALL_INDEXES   
--檢視指定表、指定索引的碎片資訊
DBCC SHOWCONTIG (${表名},${索引名})

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');

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

CREATE INDEX ${index_name} ON T(${index_col})  WITH (DROP_EXISTING = ON)  

-- 重建指定索引
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 (${db_name}, '${schema_name}.${table_name}', ${index_name});  

-- 檢查資料庫檔案空間使用率
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]

USE [${db_name}]
GO
DBCC SHRINKDATABASE(N'${db_name}' )
GO