Sql-Server
嘗試回收未使用空間導致 SQL Server 中已用空間顯著增加
我在生產數據庫中有一個大小為 525 GB 的表，其中 383 GB 未使用：
我想回收其中的一些空間，但是，在弄亂生產數據庫之前，我正在測試數據庫中的相同表上測試一些策略，但數據較少。這張表也有類似的問題：
關於表的一些資訊：
- 填充因子設置為 0
- 大約有30列
- 其中一列是圖像類型的 LOB，它儲存大小從幾 KB 到幾百 MB 不等的文件
- 該表沒有任何與之關聯的假設索引
伺服器正在執行 SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64)。數據庫正在使用
SIMPLE恢復模式。我嘗試過的一些事情：
- 重建索引：
ALTER INDEX ALL ON dbo.MyTable REBUILD. 這影響微乎其微。- 重新組織索引：
ALTER INDEX ALL ON dbo.MyTable REORGANIZE WITH(LOB_COMPACTION = ON). 這影響微乎其微。- 將 LOB 列複製到另一個表，刪除列，重新創建列，然後將數據複製回來（如本文所述：釋放未使用的空間 SQL Server 表）。這減少了未使用的空間，但似乎只是將其轉換為已用空間：
- 使用 bcp 實用程序導出表、截斷表並重新載入（如本文所述：如何釋放表的未使用空間）。這也減少了未使用的空間，並將使用的空間增加到與上圖相似的程度。
- 儘管不推薦，但我嘗試了 DBCC SHRINKFILE 和 DBCC SHRINKDATABASE 命令，但它們對未使用的空間沒有任何影響。
- 跑步
DBCC CLEANTABLE('myDB', 'dbo.myTable')並沒有什麼不同- 在保持圖像和文本數據類型以及將數據類型更改為 varbinary(max) 和 varchar(max) 之後，我已經嘗試了上述所有方法。
- 我嘗試將數據導入到新數據庫中的新表中，這也只是將未使用的空間轉換為已用空間。我在這篇文章中概述了這次嘗試的細節。
如果這些是我可以預期的結果，我不想在生產數據庫上進行這些嘗試，所以：
- 為什麼在這些嘗試之後，未使用的空間只是被轉換為已使用的空間？我覺得我對幕後發生的事情沒有很好的了解。
- 我還能做些什麼來減少未使用的空間而不增加已用空間？
編輯：這是表的磁碟使用情況報告和腳本：
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[MyTable]( [Column1] [int] NOT NULL, [Column2] [int] NOT NULL, [Column3] [int] NOT NULL, [Column4] [bit] NOT NULL, [Column5] [tinyint] NOT NULL, [Column6] [datetime] NULL, [Column7] [int] NOT NULL, [Column8] [varchar](100) NULL, [Column9] [varchar](256) NULL, [Column10] [int] NULL, [Column11] [image] NULL, [Column12] [text] NULL, [Column13] [varchar](100) NULL, [Column14] [varchar](6) NULL, [Column15] [int] NOT NULL, [Column16] [bit] NOT NULL, [Column17] [datetime] NULL, [Column18] [varchar](50) NULL, [Column19] [varchar](50) NULL, [Column20] [varchar](60) NULL, [Column21] [varchar](20) NULL, [Column22] [varchar](120) NULL, [Column23] [varchar](4) NULL, [Column24] [varchar](75) NULL, [Column25] [char](1) NULL, [Column26] [varchar](50) NULL, [Column27] [varchar](128) NULL, [Column28] [varchar](50) NULL, [Column29] [int] NULL, [Column30] [text] NULL, CONSTRAINT [PK] PRIMARY KEY CLUSTERED ( [Column1] ASC, [Column2] ASC, [Column3] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column4] DEFAULT (0) FOR [Column4] GO ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column5] DEFAULT (0) FOR [Column5] GO ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column15] DEFAULT (0) FOR [Column15] GO ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column16] DEFAULT (0) FOR [Column16] GO以下是執行漢娜弗農回答中的命令的結果：
╔════════════╦═══════════╦════════════╦═════════════════╦══════════════════════╦════════════════════╗ ║ TotalBytes ║ FreeBytes ║ TotalPages ║ TotalEmptyPages ║ PageBytesFreePercent ║ UnusedPagesPercent ║ ╠════════════╬═══════════╬════════════╬═════════════════╬══════════════════════╬════════════════════╣ ║ 9014280192║ 8653594624║ 1100376║ 997178 ║ 95.998700 ║ 90.621500 ║ ╚════════════╩═══════════╩════════════╩═════════════════╩══════════════════════╩════════════════════╝╔═════════════╦═══════════════════╦════════════════════╗ ║ ObjectName ║ ReservedPageCount ║ UsedPageCount ║ ╠═════════════╬═══════════════════╬════════════════════╣ ║ dbo.MyTable ║ 5109090 ║ 2850245 ║ ╚═════════════╩═══════════════════╩════════════════════╝更新：
我按照 Hannah Vernon 的建議執行了以下命令：
DBCC UPDATEUSAGE (N'<database_name>', N'<table_name>');這是輸出：
DBCC UPDATEUSAGE: Usage counts updated for table 'MyTable' (index 'PK_MyTable', partition 1): USED pages (LOB Data): changed from (568025) to (1019641) pages. RSVD pages (LOB Data): changed from (1019761) to (1019763) pages.這更新了表的磁碟使用情況：
以及整體磁碟使用情況：
因此，問題似乎在於 SQL Server 跟踪的磁碟使用情況與實際磁碟使用情況嚴重不同步。我會認為這個問題已經解決，但我很想知道為什麼會發生這種情況！
作為第一步，我會針對錶執行DBCC UPDATEUSAGE ，因為症狀顯示空間使用情況不一致。
DBCC UPDATEUSAGE 更正表或索引中每個分區的行、已用頁、保留頁、葉頁和數據頁計數。如果系統表中沒有錯誤，則 DBCC UPDATEUSAGE 不返回任何數據。如果發現並糾正了不准確之處並且未使用 WITH NO_INFOMSGS，則 DBCC UPDATEUSAGE 將返回系統表中正在更新的行和列。
語法是：
DBCC UPDATEUSAGE (N'<database_name>', N'<table_name>');在你執行之後，我會
EXEC sys.sp_spaceused在桌子上執行：EXEC sys.sp_spaceused @objname = N'dbo.MyTable' , @updateusage = 'false' --true or false , @mode = 'ALL' --ALL, LOCAL_ONLY, REMOTE_ONLY , @oneresultset = 1;上面的命令可以選擇更新使用情況，但是由於您
DBCC UPDATEUSAGE首先手動執行，所以只需將該設置設置為 false。手動執行DBCC UPDATEUSAGE可讓您查看是否已更正任何內容。以下查詢應顯示表中空閒字節的百分比和表中空閒頁的百分比。由於查詢使用了未記錄的功能，因此指望結果是不明智的，但與 , 的輸出相比，它似乎是準確
sys.sp_spaceused的。如果空閒字節的百分比明顯高於空閒頁面的百分比，那麼您有很多部分為空的頁面。
部分空白頁面可能源於多種原因，包括：
- 頁面拆分，必須拆分頁面以適應聚集索引中的新插入
- 由於列大小，無法用列填充頁面。
該查詢使用未記錄的
sys.dm_db_database_page_allocations動態管理功能：;WITH dpa AS ( SELECT dpa.* , page_free_space_percent_corrected = CASE COALESCE(dpa.page_type_desc, N'') WHEN N'TEXT_MIX_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100) WHEN N'TEXT_TREE_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100) ELSE COALESCE(dpa.page_free_space_percent, 100) END FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.MyTable'), NULL, NULL, 'DETAILED') dpa ) , src AS ( SELECT TotalKB = COUNT_BIG(1) * 8192 / 1024 , FreeKB = SUM((dpa.page_free_space_percent_corrected / 100) * CONVERT(bigint, 8192)) / 1024 , TotalPages = COUNT_BIG(1) , TotalEmptyPages = SUM(CASE WHEN dpa.page_free_space_percent_corrected = 100 THEN 1 ELSE 0 END) --completely empty pages FROM dpa ) SELECT * , BytesFreePercent = (CONVERT(decimal(38,2), src.FreeKB) / src.TotalKB) * 100 , UnusedPagesPercent = (CONVERT(decimal(38,2), src.TotalEmptyPages) / src.TotalPages) * 100 FROM src輸出如下所示：
╔═════════╦════════╦════════════╦═════════════════╦══════════════════╦════════════════════╗ ║ TotalKB ║ FreeKB ║ TotalPages ║ TotalEmptyPages ║ BytesFreePercent ║ UnusedPagesPercent ║ ╠═════════╬════════╬════════════╬═════════════════╬══════════════════╬════════════════════╣ ║ 208 ║ 96 ║ 26 ║ 12 ║ 46.153800 ║ 46.153800 ║ ╚═════════╩════════╩════════════╩═════════════════╩══════════════════╩════════════════════╝我在這裡寫了一篇描述該功能的部落格文章。
在您的場景中，由於您已經執行
ALTER TABLE ... REBUILD了 ，您應該會看到 非常低的數字TotalEmptyPages，但我猜您仍然會有大約 72% 的BytesFreePercent.我已使用您的
CREATE TABLE腳本嘗試重新創建您的場景。這是我正在使用的MCVE ：
DROP TABLE IF EXISTS dbo.MyTable; CREATE TABLE [dbo].[MyTable]( [Column1] [int] NOT NULL IDENTITY(1,1), [Column2] [int] NOT NULL, [Column3] [int] NOT NULL, [Column4] [bit] NOT NULL, [Column5] [tinyint] NOT NULL, [Column6] [datetime] NULL, [Column7] [int] NOT NULL, [Column8] [varchar](100) NULL, [Column9] [varchar](256) NULL, [Column10] [int] NULL, [Column11] [image] NULL, [Column12] [text] NULL, [Column13] [varchar](100) NULL, [Column14] [varchar](6) NULL, [Column15] [int] NOT NULL, [Column16] [bit] NOT NULL, [Column17] [datetime] NULL, [Column18] [varchar](50) NULL, [Column19] [varchar](50) NULL, [Column20] [varchar](60) NULL, [Column21] [varchar](20) NULL, [Column22] [varchar](120) NULL, [Column23] [varchar](4) NULL, [Column24] [varchar](75) NULL, [Column25] [char](1) NULL, [Column26] [varchar](50) NULL, [Column27] [varchar](128) NULL, [Column28] [varchar](50) NULL, [Column29] [int] NULL, [Column30] [text] NULL, CONSTRAINT [PK] PRIMARY KEY CLUSTERED ( [Column1] ASC, [Column2] ASC, [Column3] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column4] DEFAULT (0) FOR [Column4] ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column5] DEFAULT (0) FOR [Column5] ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column15] DEFAULT (0) FOR [Column15] ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column16] DEFAULT (0) FOR [Column16] GO INSERT INTO dbo.MyTable ( Column2 , Column3 , Column4 , Column5 , Column6 , Column7 , Column8 , Column9 , Column10 , Column11 , Column12 , Column13 , Column14 , Column15 , Column16 , Column17 , Column18 , Column19 , Column20 , Column21 , Column22 , Column23 , Column24 , Column25 , Column26 , Column27 , Column28 , Column29 , Column30 ) VALUES ( 0 , 0 , 0 , 0 , '2019-07-09 00:00:00' , 1 , REPLICATE('A', 50) , REPLICATE('B', 128) , 0 , REPLICATE(CONVERT(varchar(max), 'a'), 1) , REPLICATE(CONVERT(varchar(max), 'b'), 9000) , REPLICATE('C', 50) , REPLICATE('D', 3) , 0 , 0 , '2019-07-10 00:00:00' , REPLICATE('E', 25) , REPLICATE('F', 25) , REPLICATE('G', 30) , REPLICATE('H', 10) , REPLICATE('I', 120) , REPLICATE('J', 4) , REPLICATE('K', 75) , 'L' , REPLICATE('M', 50) , REPLICATE('N', 128) , REPLICATE('O', 50) , 0 , REPLICATE(CONVERT(varchar(max), 'c'), 90000) ); --GO 100 ;WITH dpa AS ( SELECT dpa.* , page_free_space_percent_corrected = CASE COALESCE(dpa.page_type_desc, N'') WHEN N'TEXT_MIX_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100) WHEN N'TEXT_TREE_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100) ELSE COALESCE(dpa.page_free_space_percent, 100) END FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.MyTable'), NULL, NULL, 'DETAILED') dpa ) , src AS ( SELECT TotalKB = COUNT_BIG(1) * 8192 / 1024 , FreeKB = SUM((dpa.page_free_space_percent_corrected / 100) * CONVERT(bigint, 8192)) / 1024 , TotalPages = COUNT_BIG(1) , TotalEmptyPages = SUM(CASE WHEN dpa.page_free_space_percent_corrected = 100 THEN 1 ELSE 0 END) --completely empty pages FROM dpa ) SELECT * , BytesFreePercent = (CONVERT(decimal(38,2), src.FreeKB) / src.TotalKB) * 100 , UnusedPagesPercent = (CONVERT(decimal(38,2), src.TotalEmptyPages) / src.TotalPages) * 100 FROM src以下查詢顯示分配給表的每個頁面的一行，並使用相同的未記錄 DMV：
SELECT DatabaseName = d.name , ObjectName = o.name , IndexName = i.name , PartitionID = dpa.partition_id , dpa.allocation_unit_type_desc , dpa.allocated_page_file_id , dpa.allocated_page_page_id , dpa.is_allocated , dpa.page_free_space_percent --this seems unreliable , page_free_space_percent_corrected = CASE COALESCE(dpa.page_type_desc, N'') WHEN N'TEXT_MIX_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100) WHEN N'TEXT_TREE_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100) ELSE COALESCE(dpa.page_free_space_percent, 100) END , dpa.page_type_desc , dpa.is_page_compressed , dpa.has_ghost_records FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.MyTable'), NULL, NULL, 'DETAILED') dpa LEFT JOIN sys.databases d ON dpa.database_id = d.database_id LEFT JOIN sys.objects o ON dpa.object_id = o.object_id LEFT JOIN sys.indexes i ON dpa.object_id = i.object_id AND dpa.index_id = i.index_id WHERE dpa.database_id = DB_ID() --sanity check for sys.objects and sys.indexes如果您在測試環境中對真實表執行它，輸出將顯示很多行，但它可能會讓您看到問題出在哪裡。
您可以執行以下腳本並將結果發佈到您的問題中嗎？我只是想確保我們在同一頁面上。
SELECT ObjectName = s.name + N'.' + o.name , ReservedPageCount = SUM(dps.reserved_page_count) , UsePageCount = SUM(dps.used_page_count) FROM sys.schemas s INNER JOIN sys.objects o ON s.schema_id = o.schema_id INNER JOIN sys.partitions p ON o.object_id = p.object_id INNER JOIN sys.dm_db_partition_stats dps ON p.object_id = dps.object_id WHERE s.name = N'dbo' AND o.name = N'MyTable' GROUP BY s.name + N'.' + o.name;