SQL?Server表空間碎片化回收怎么實現

這篇文章主要介紹了SQL Server表空間碎片化回收怎么實現的相關知識，內容詳細易懂，操作簡單快捷，具有一定借鑒價值，相信大家閱讀完這篇SQL Server表空間碎片化回收怎么實現文章都會有所收獲，下面我們一起來看看吧。

1 鎖片化的產生

1.1 產生碎片化的原因

1、在B-tree索引中，表數據按照聚集索引的排序進行物理存儲，若聚集索引離散化比較嚴重，那么可能會出現較為嚴重的碎片化問題；

2、隨著業務的DML操作，會伴隨著數據頁分裂的情況，這種情況下也會導致表空間碎片化問題;

3、大表通過delete清理無效歷史數據，delete產生碎片化空間；

1.2 碎片化的影響

表空間碎片化越嚴重越容易影響對該表的查詢效率，這是因為當表碎片化比較嚴重時，數據庫根據執行計劃掃描滿足需求的數據頁會掃描較多“無效頁面”，導致查詢操作需要更多的IO消耗。

1.3 定位碎片化

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

重點關注：

• avg_fragment_size_in_pages ： 該參數值越大，范圍掃描的性能越好

• avg_fragmentation_in_percent ：對于heap表，該參數表示區碎片百分比；對于index，該參數表示邏輯碎片；該參數越大表示表的碎片化越嚴重，需要通過 Reorganize or Rebuild Indexes 來進行碎片化回收

• avg_page_space_used_in_percent ： 該參數表示數據頁的填充程度，一般小于100%，但是該參數越小，表示數據頁面碎片化情況越嚴重。若想要數據頁使用率的問題，必須進行索引重建操作

• fragment_count : 碎片化數據頁數

• page_count : 掃描數據頁數

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

2 碎片化處理

由于表數據是根據聚集索引排序進行物理存儲，所以當表碎片化比較嚴重時，可以通過對聚集索引的重新組織來進行碎片化空間回收，重建索引的方式也有比較多方式，主要如下：

2.1 刪除并重建聚集索引

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

• 執行刪除聚集索引后，會影響該表有關利用該索引進行查詢的SQL執行效率

• 執行刪除聚集索引，也會導致該表相關的非聚集索引重建

• 在重建聚集索引期間，會獲取相應的Sch-M鎖，阻塞業務正常讀寫操作，且創建聚集索引后也會導致相應的非聚集索引重建

• 該方式會將整張表數據進行重新組織，可回收最大限度的碎片化空間

2.2 DROP_EXISTING

使用DROP_EXISTING進行重建索引，也是對聚集索引的刪除重建，但是該方式在方法一的基礎上做了一些優化：

• 刪除聚集索引時，會保留主鍵索引的鍵值，避免了刪除、重建聚集索引時對非聚集索引的重建

• 執行DROP_EXISTING重建索引期間，仍然會對正常業務讀寫操作造成阻塞

• 該方式會將整張表數據進行重新組織，可回收最大限度的碎片化空間

基本語法：

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

2.3 DBCC DBREINDEX

DBCC DBREINDEX也是通過對索引的刪除以及重建來實現碎片化回收。根據數據庫版本（企業版or非企業版）以及索引類型（非聚集or聚集），該操作是可以實現在線或者離線操作。

• 在企業版數據引擎中，對于非聚集索引的索引重建可以通過在線的方式進行操作

• 在線索引重建期間，雖然不阻塞正常業務讀寫操作，但還是對應的DML操作執行效率還是會有所下降

• 離線索引重建期間，阻塞業務讀寫

• 對于在線索引重建，可以進行暫?；蛘呓K止。但是暫停期間應用會影響該表的DML執行效率，如果后續不繼續索引的重建操作，請直接終止而不是暫停

• 該方式會將整張表數據進行重新組織，可回收最大限度的碎片化空間

基本語法：

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

該方式的實現邏輯與以上三種大有不同，DBCC INDEXDEFRAG并非完全重新組織整張表的b-tree結構：

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

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

3 空間回收

需要注意的是，在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

關于“SQL Server表空間碎片化回收怎么實現”這篇文章的內容就介紹到這里，感謝各位的閱讀！相信大家對“SQL Server表空間碎片化回收怎么實現”知識都有一定的了解，大家如果還想學習更多知識，歡迎關注億速云行業資訊頻道。