數據庫大小 - MDF 太大？

我正在維護一個 SQL Server 2005 數據庫，該數據庫託管大約 2.9Tb 的數據（2 x 1.45Tb - 我有一個 RAW 模式和一個 ANALYSIS 模式，所以基本上攝取了兩個數據副本）。恢復模型很簡單，並且.ldf是 6Gb。

無論出於何種原因，它.mdf都是 7.5Tb。現在，在 ANALYSIS 表中可能只有 2-3 個額外的列並且沒有多少NVARCHAR(MAX)列，從我（可能錯誤地理解 - 如果我錯了請糾正我）可能會導致額外的空間分配。那是在剛剛縮小數據庫之後——在那之前它大約是 9Tb。有什麼想法嗎？

如果您還有其他問題，請告訴我 - 我對數據庫管理和優化工作非常陌生（我通常不做這方面的工作:)）。

非常感謝！

安德魯

在您的大小估計中，您是否考慮過索引佔用的空間量？此外，如果您有設置為多字節（N[VAR]CHAR而不是[VAR]CHAR）的文本欄位，並且輸入文件是 UTF-8 或普通的每個字元一個字節，那麼這會將您的儲存要求提高兩倍。此外請記住，如果您在表上有一個聚集鍵/索引，那麼它的大小會影響表上的所有其他索引，因為它們包括每一行的聚集鍵值（如果表有一個 NCHAR(10 ) INT 將執行的鍵，這是您的聚集鍵/索引，您不僅在數據頁中每行使用了額外的 16 個字節，還在該表上的每個其他索引中每行浪費了 16 個字節）。

此外，將分配一些空間但未使用，因為數據庫引擎在刪除後留下了一些分配的空間，以便可以快速再次用於該表中的新數據，或者因為插入和刪除的模式只留下了許多頁面的一部分滿的。

你可以執行：

SELECT o.name
    , SUM(ps.reserved_page_count)/128.0 AS ReservedMB
    , SUM(ps.used_page_count)/128.0 AS UsedMB
    , SUM(ps.reserved_page_count-ps.used_page_count)/128.0 AS DiffMB
FROM sys.objects o  
JOIN sys.dm_db_partition_stats ps ON o.object_id = ps.object_id  
WHERE OBJECTPROPERTYEX(o.object_id, 'IsMSShipped') = 0  
GROUP BY o.name  
ORDER BY SUM(ps.reserved_page_count) DESC

快速查看哪些表佔用了空間。

在該數據庫中執行也EXEC sp_spaceused將返回兩個結果集。第一個列出了在文件系統中為數據文件分配的總空間以及其中有多少未分配，第二個列出了分配的空間中有多少用於數據頁、索引頁或目前未使用。

sp_spaceused也會返回給定對象使用的空間，所以你可以循環它來建構一個表進行分析：

-- TEMP TABLES FOR ANALYSIS
CREATE TABLE #tTables (sName NVARCHAR(MAX), iRows BIGINT, iReservedKB BIGINT, iDataKB BIGINT, iIndexKB BIGINT, iUnusedKB BIGINT)
CREATE TABLE #tTmp (sName NVARCHAR(MAX), iRows BIGINT, sReservedKB NVARCHAR(MAX), sDataKB NVARCHAR(MAX), sIndexKB NVARCHAR(MAX), sUnusedKB NVARCHAR(MAX))
-- COLLECT SPACE USE PER TABLE
EXEC sp_msforeachtable 'INSERT #tTmp EXEC sp_spaceused [?];'
-- CONVERT NUMBER-AS-TEXT COLUMNS TO NUMBER TYPES FOR EASIER ANALYSIS
INSERT #tTables SELECT sName, iRows
                    , CAST(REPLACE(sReservedKB, ' KB', '') AS BIGINT)
                    , CAST(REPLACE(sDataKB    , ' KB', '') AS BIGINT)
                    , CAST(REPLACE(sIndexKB   , ' KB', '') AS BIGINT)
                    , CAST(REPLACE(sUnusedKB  , ' KB', '') AS BIGINT) 
               FROM #tTmp
DROP TABLE #tTmp 
-- DO SOME ANALYSIS 
SELECT sName='TOTALS', iRows=SUM(iRows), iReservedKB=SUM(iReservedKB), iDataKB=SUM(iDataKB),  iIndexKB=SUM(iIndexKB), iUnusedKB=SUM(iUnusedKB) FROM #tTables ORDER BY sName
SELECT * FROM #tTables ORDER BY iReservedKB DESC
-- CLEAN UP
DROP TABLE #tTables

上面的程式碼將在一個列表中輸出所有表格大小，以及總計的單行。如果需要，您可以使用各種系統視圖（如上面第一個查詢中使用的，請參閱sys.objectshttp://technet.microsoft.com/en-us/library/ms177862.aspx[了解](http://technet.microsoft.com/en-us/library/ms177862.aspx)更多詳細資訊）來獲取更多詳細資訊，例如每個索引使用的空間。sys.dm_db_partition_stats

---

數據文件中的未使用空間分為三類：

1. 沒有分配給任何東西（這顯示在第一個結果集中sp_spaceused，沒有指定對象）
2. 分配給對象（保留）但目前未使用的對象（這顯示在sp_spaceused的輸出中的“未使用”計數中。
3. 鎖定在部分使用的頁面中（這看起來會被使用，因為所有內容都分配在單頁塊中，一頁長 8,192 字節）。這更難檢測/計算。這是由於兩個因素的混合：

• 分頁。隨著數據的添加，您通常會得到部分空頁面（儲存引擎總是可以規範頁面內容，但這將非常低效），並且隨著行被刪除，頁面內容不會自動打包（同樣可以，但額外的I/O 負載通常不值得）。
• 儲存引擎不會將一行拆分為多個頁面（這與每行 8,192 字節限制來自的頁面大小一起）。如果您的行是固定大小並且每行佔用 1,100 字節，那麼您將“浪費”分配給該表的每個數據塊的至少 492 字節（7 行佔用 7,700 字節，第 8 行不適合，因此剩餘字節不會不能使用）。行越寬，情況可能越糟。具有可變長度行的表/索引（比完全固定長度的行更常見）通常更好（但不太容易計算問題）。

這裡的另一個警告是大型對象（TEXT列，[N]VARCHAR(MAX)超過一定大小的值等等），因為它們確實被放置在頁面外，只在主行數據中佔用 8 個字節來保存指向其他地方數據的指針），因此可以打破每行 8,192 字節的限制。

---

**tl;dr：**估計預期的數據庫大小可能比最初假設的要復雜得多。

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