1 鎖片化的產(chǎn)生

1.1 產(chǎn)生碎片化的原因

1、在B-tree索引中，表數(shù)據(jù)按照聚集索引的排序進(jìn)行物理存儲(chǔ)，若聚集索引離散化比較嚴(yán)重，那么可能會(huì)出現(xiàn)較為嚴(yán)重的碎片化問題；

2、隨著業(yè)務(wù)的DML操作，會(huì)伴隨著數(shù)據(jù)頁分裂的情況，這種情況下也會(huì)導(dǎo)致表空間碎片化問題;

3、大表通過delete清理無效歷史數(shù)據(jù)，delete產(chǎn)生碎片化空間；

1.2 碎片化的影響

表空間碎片化越嚴(yán)重越容易影響對(duì)該表的查詢效率，這是因?yàn)楫?dāng)表碎片化比較嚴(yán)重時(shí)，數(shù)據(jù)庫(kù)根據(jù)執(zhí)行計(jì)劃掃描滿足需求的數(shù)據(jù)頁會(huì)掃描較多“無效頁面”，導(dǎo)致查詢操作需要更多的IO消耗。

1.3 定位碎片化

1、在SQL Server中，可以通過DBCC SHOWCONTIG的方式查看表空間碎片化的一些統(tǒng)計(jì)信息，具體語法如下：

--查看數(shù)據(jù)庫(kù)中所有索引的碎片信息
use ${數(shù)據(jù)庫(kù)名}
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');

重點(diǎn)關(guān)注：

• avg_fragment_size_in_pages ： 該參數(shù)值越大，范圍掃描的性能越好
• avg_fragmentation_in_percent ：對(duì)于heap表，該參數(shù)表示區(qū)碎片百分比；對(duì)于index，該參數(shù)表示邏輯碎片；該參數(shù)越大表示表的碎片化越嚴(yán)重，需要通過 Reorganize or Rebuild Indexes 來進(jìn)行碎片化回收
• avg_page_space_used_in_percent ： 該參數(shù)表示數(shù)據(jù)頁的填充程度，一般小于100%，但是該參數(shù)越小，表示數(shù)據(jù)頁面碎片化情況越嚴(yán)重。若想要數(shù)據(jù)頁使用率的問題，必須進(jìn)行索引重建操作
• fragment_count : 碎片化數(shù)據(jù)頁數(shù)
• page_count : 掃描數(shù)據(jù)頁數(shù)

3、通過統(tǒng)計(jì)信息查看數(shù)據(jù)庫(kù)碎片化空間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

2 碎片化處理

由于表數(shù)據(jù)是根據(jù)聚集索引排序進(jìn)行物理存儲(chǔ)，所以當(dāng)表碎片化比較嚴(yán)重時(shí)，可以通過對(duì)聚集索引的重新組織來進(jìn)行碎片化空間回收，重建索引的方式也有比較多方式，主要如下：

2.1 刪除并重建聚集索引

該方式其實(shí)就是將碎片化比較嚴(yán)重的表，先通過drop index刪除其聚集索引，然后通過create index或者alter table重建聚集索引。該方式的特點(diǎn)是：

• 執(zhí)行刪除聚集索引后，會(huì)影響該表有關(guān)利用該索引進(jìn)行查詢的SQL執(zhí)行效率
• 執(zhí)行刪除聚集索引，也會(huì)導(dǎo)致該表相關(guān)的非聚集索引重建
• 在重建聚集索引期間，會(huì)獲取相應(yīng)的Sch-M鎖，阻塞業(yè)務(wù)正常讀寫操作，且創(chuàng)建聚集索引后也會(huì)導(dǎo)致相應(yīng)的非聚集索引重建
• 該方式會(huì)將整張表數(shù)據(jù)進(jìn)行重新組織，可回收最大限度的碎片化空間

2.2 DROP_EXISTING

使用DROP_EXISTING進(jìn)行重建索引，也是對(duì)聚集索引的刪除重建，但是該方式在方法一的基礎(chǔ)上做了一些優(yōu)化：

• 刪除聚集索引時(shí)，會(huì)保留主鍵索引的鍵值，避免了刪除、重建聚集索引時(shí)對(duì)非聚集索引的重建
• 執(zhí)行DROP_EXISTING重建索引期間，仍然會(huì)對(duì)正常業(yè)務(wù)讀寫操作造成阻塞
• 該方式會(huì)將整張表數(shù)據(jù)進(jìn)行重新組織，可回收最大限度的碎片化空間

基本語法：

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

2.3 DBCC DBREINDEX

DBCC DBREINDEX也是通過對(duì)索引的刪除以及重建來實(shí)現(xiàn)碎片化回收。根據(jù)數(shù)據(jù)庫(kù)版本（企業(yè)版or非企業(yè)版）以及索引類型（非聚集or聚集），該操作是可以實(shí)現(xiàn)在線或者離線操作。

• 在企業(yè)版數(shù)據(jù)引擎中，對(duì)于非聚集索引的索引重建可以通過在線的方式進(jìn)行操作
• 在線索引重建期間，雖然不阻塞正常業(yè)務(wù)讀寫操作，但還是對(duì)應(yīng)的DML操作執(zhí)行效率還是會(huì)有所下降
• 離線索引重建期間，阻塞業(yè)務(wù)讀寫
• 對(duì)于在線索引重建，可以進(jìn)行暫?；蛘呓K止。但是暫停期間應(yīng)用會(huì)影響該表的DML執(zhí)行效率，如果后續(xù)不繼續(xù)索引的重建操作，請(qǐng)直接終止而不是暫停
• 該方式會(huì)將整張表數(shù)據(jù)進(jìn)行重新組織，可回收最大限度的碎片化空間

基本語法：

-- 重建指定索引
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

2.4 DBCC INDEXDEFRAG

該方式的實(shí)現(xiàn)邏輯與以上三種大有不同，DBCC INDEXDEFRAG并非完全重新組織整張表的b-tree結(jié)構(gòu)：

DBCC INDEXDEFRAG按照索引鍵的邏輯順序，通過壓縮索引頁里的行然后刪除那些由此產(chǎn)生的不必要的碎片化數(shù)據(jù)頁、刪除完全碎片化數(shù)據(jù)頁面的方式來進(jìn)行碎片化空間的回收
該方式執(zhí)行期間不阻塞業(yè)務(wù)讀寫操作
該方式下可回收的碎片化空間效果可能不如以上三種索引重建的方式
基本語法：

DBCC INDEXDEFRAG (${db_name}, '${schema_name}.${table_name}', ${index_name});  

3 空間回收

需要注意的是，在SQL Server數(shù)據(jù)庫(kù)，我們對(duì)表空間數(shù)據(jù)進(jìn)行碎片化處理、或者truncate清空無效歷史數(shù)據(jù)，這些釋放出來的空間只是空出來，當(dāng)有新數(shù)據(jù)寫入時(shí)，優(yōu)先使用這些空出來的數(shù)據(jù)頁，而不是再向OS申請(qǐng)新的數(shù)據(jù)空間擴(kuò)展。所以這部分并不會(huì)直接釋放給OS，如果我們想要達(dá)到降低整個(gè)OS的磁盤空間使用率的話，還需要對(duì)數(shù)據(jù)庫(kù)的數(shù)據(jù)文件進(jìn)行收縮。

1、檢查數(shù)據(jù)文件空間使用率

-- 檢查數(shù)據(jù)庫(kù)文件空間使用率
SELECT a.name [文件名稱] ,cast(a.[size]*1.0/128 as decimal(12,1)) AS [文件設(shè)置大小(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 '文件大小固定，不會(huì)增長(zhǎng)' ELSE '文件將自動(dòng)增長(zhǎng)' end [增長(zhǎng)模式] ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 
    THEN '增量為固定大小' WHEN A.growth > 0 AND is_percent_growth = 1 THEN '增量將用整數(shù)百分比表示' ELSE '文件大小固定，不會(huì)增長(zhǎng)' 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 '文件大小固定，不會(huì)增長(zhǎng)' end AS [增長(zhǎng)值(%或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、收縮數(shù)據(jù)文件

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

參考鏈接：

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15

到此這篇關(guān)于SQL Server表空間碎片化回收的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)SQL Server表空間碎片化回收內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論