原因/如何修復具有過多保留頁的表

我在 azure PaaS sql server 中有幾個帶有聚集索引的表，這些表已經爆炸到比它們的數據大得多，保留的頁數比表中的實際數據量大幾倍。這些表只是插入（沒有刪除或更新），儘管有時會插入行，然後由於 PK 違規而導致插入失敗。

1. 這是一個問題嗎？- azure 報告此數據庫中的所有“保留”空間實際上都在使用中（我對保留的含義有所了解）
2. 這種井噴有已知的原因嗎？- 一個 10gb 的表佔用 70gb 的空間有點過分。
3. 有沒有辦法清理這個？
4. 有沒有我可以採用的策略來防止這種情況再次發生？

一個例子

partition_id    object_id   index_id    partition_number    in_row_data_page_count  in_row_used_page_count  in_row_reserved_page_count  lob_used_page_count lob_reserved_page_count row_overflow_used_page_count    row_overflow_reserved_page_count    used_page_count reserved_page_count row_count
72057594050904064   274100017   1   1   1082295 1086782 5527998 0   0   0   0   1086782 5527998 61559096

這是一個已知極端碎片（> 90％）的對象，但是我個人的期望是磁碟上的錯誤佈局不應導致大量空間浪費，而只是由於最小化連續讀取而導致的過度尋軌延遲。

此外，假設隨機插入下的各個頁面填滿，然後分成兩頁半滿，那麼每個頁面應該在 50% 到 100%（平均 75%）之間波動，導致高度碎片化的表的儲存成本約為 25%/指數。

請使用以下查詢檢查這些索引的碎片級別。索引碎片可能是罪魁禍首。

SELECT
DB_NAME() AS DBName
,OBJECT_NAME(ps.object_id) AS TableName
,i.name AS IndexName
,ips.index_type_desc
,ips.avg_fragmentation_in_percent
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
ORDER BY ps.object_id, ps.index_id

如果您重建索引，您將回收額外的空間。您將減小這些索引的大小和整個數據庫的大小。使用以下腳本執行索引維護：

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
(
SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName]
FROM INFORMATION_SCHEMA.TABLES IST
WHERE IST.TABLE_TYPE = 'BASE TABLE'
)

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN
PRINT('Rebuilding Indexes on ' + @TableName)
Begin Try
EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)')
End Try
Begin Catch
PRINT('Cannot do rebuild with Online=On option, taking table ' + @TableName+' down for douing rebuild')
EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
End Catch
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

創建 Azure Runbook 以定期對索引進行碎片整理並避免使用更多儲存空間。

引用自：https://dba.stackexchange.com/questions/218472