來自 sys.allocation_units 的 DATALENGTH 的總和與表大小不匹配
我的印像是，如果我
DATALENGTH()將表中所有記錄的所有欄位相加，我將得到表的總大小。我弄錯了嗎？SELECT SUM(DATALENGTH(Field1)) + SUM(DATALENGTH(Field2)) + SUM(DATALENGTH(Field3)) TotalSizeInBytes FROM SomeTable WHERE X, Y, and Z are true我在下面使用了這個查詢（我從網上獲取表大小，僅聚集索引，因此它不包括 NC 索引）來獲取我的數據庫中特定表的大小。出於計費目的（我們按部門使用的空間量向部門收費），我需要計算出此表中每個部門使用了多少空間。我有一個查詢來標識表中的每個組。我只需要弄清楚每個組佔用了多少空間。
由於表中的欄位，每行的空間可能會大幅波動
VARCHAR(MAX)，所以我不能只取平均大小 * 部門的行數。當我使用上述DATALENGTH()方法時，我只得到下面查詢中使用的總空間的 85%。想法？SELECT s.Name AS SchemaName, t.NAME AS TableName, p.rows AS RowCounts, (SUM(a.total_pages) * 8)/1024 AS TotalSpaceMB, (SUM(a.used_pages) * 8)/1024 AS UsedSpaceMB, ((SUM(a.total_pages) - SUM(a.used_pages)) * 8)/1024 AS UnusedSpaceMB FROM sys.tables t with (nolock) INNER JOIN sys.schemas s with (nolock) ON s.schema_id = t.schema_id INNER JOIN sys.indexes i with (nolock) ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p with (nolock) ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a with (nolock) ON p.partition_id = a.container_id WHERE t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 AND i.type_desc = 'Clustered' GROUP BY t.Name, s.Name, p.Rows ORDER BY TotalSpaceMB desc有人建議我為每個部門創建一個過濾索引或對錶進行分區，這樣我就可以直接查詢每個索引使用的空間。過濾索引可以通過程式創建（並在維護視窗期間或當我需要執行定期計費時再次刪除），而不是一直使用空間（在這方面分區會更好）。
我喜歡這個建議，並且通常會這樣做。但老實說，我以“每個部門”為例來解釋我為什麼需要這個，但老實說，這並不是真正的原因。由於保密原因，我無法解釋我需要這些數據的確切原因，但它類似於不同的部門。
關於這張表上的非聚集索引：如果我能得到 NC 索引的大小，那就太好了。但是，NC 索引佔聚集索引大小的比例不到 1%，因此我們可以不包括這些索引。但是，無論如何我們將如何包含 NC 索引？我什至無法獲得聚集索引的準確大小:)
Please note that the following info is not intended to be a comprehensive description of how data pages are laid out, such that one can calculate the number of bytes used per any set of rows, as that is very complicated.數據並不是唯一佔用 8k 數據頁空間的東西：
有預留空間。您只能使用 8192 個字節中的 8060 個（這 132 個字節一開始就不是您的）：
頁頭：正好是 96 個字節。
插槽數組：這是每行 2 個字節，表示每行在頁面上的起始位置的偏移量。該數組的大小不限於剩餘的 36 個字節 (132 - 96 = 36)，否則您將被有效地限制為最多只能在數據頁上放置 18 行。這意味著每一行比你想像的要大 2 個字節。此值不包含在 報告的“記錄大小”中
DBCC PAGE，這就是為什麼它在此處單獨保存，而不是包含在下面的每行資訊中。每行元數據（包括但不限於）：
- 大小取決於表定義（即列數、可變長度或固定長度等）。資訊取自 @PaulWhite 和 @Aaron 的評論，可在與此答案和測試相關的討論中找到。
- Row-header：4個字節，其中2個表示記錄類型，另外兩個是NULL Bitmap的偏移量
- 列數：2字節
- NULL 點陣圖：目前有哪些列
NULL。每組 8 列 1 個字節。對於所有列，甚至是NOT NULL那些列。因此，最少 1 個字節。- 可變長度列偏移數組：最少 4 個字節。2 個字節來保存可變長度列的數量，然後每個可變長度列 2 個字節來保存它開始的偏移量。
ALLOW_SNAPSHOT_ISOLATION ON版本資訊：14 字節（如果您的數據庫設置為或，則會出現此資訊READ_COMMITTED_SNAPSHOT ON）。有關此問題的更多詳細資訊，請參閱以下問題和答案：插槽陣列和總頁面大小
請參閱 Paul Randall 的以下部落格文章，其中包含有關數據頁面佈局方式的一些有趣細節：Poking about with DBCC PAGE (Part 1 of ?)
未儲存在行中的數據的 LOB 指針。所以這將佔
DATALENGTH+pointer_size。但這些都不是標準尺寸。有關此復雜主題的詳細資訊，請參閱以下部落格文章：對於 Varchar、Varbinary 等 (MAX) 類型，LOB 指針的大小是多少？. 在連結的文章和我已經完成的一些額外測試之間，（預設）規則應如下所示：
從 SQL Server 2005 開始，任何人都不應再使用的舊版/已棄用 LOB 類型（
TEXT、NTEXT和IMAGE）：
預設情況下，始終將其數據儲存在 LOB 頁面上，並始終使用 16 字節指針指向 LOB 儲存。
如果 使用 sp_tableoption設置
text in row選項，則：
- 如果頁面上有空間儲存值，並且值不大於最大行內大小（可配置範圍為 24 - 7000 字節，預設為 256），則將其儲存在行內，
- 否則它將是一個 16 字節的指針。
對於 SQL Server 2005 中引入的較新 LOB 類型（
VARCHAR(MAX)、NVARCHAR(MAX)和VARBINARY(MAX)）：
預設：
- 如果該值不大於 8000 字節，並且頁面上有空間，則將其儲存在行中。
- Inline Root — 對於 8001 到 40,000（實際上是 42,000）字節之間的數據，如果空間允許，在 ROW 中將有 1 到 5 個指針（24 - 72 字節）直接指向 LOB 頁。初始 8k LOB 頁面為 24 字節，每個額外的 8k 頁面為 12 字節，最多可再增加四個 8k 頁面。
- TEXT_TREE — 對於超過 42,000 字節的數據，或者如果 1 到 5 個指針不能容納在行中，那麼將只有一個 24 字節指針指向 LOB 頁指針列表的起始頁（即“text_tree “ 頁）。
如果 使用 sp_tableoption設置
large value types out of row選項，則始終使用指向 LOB 儲存的 16 字節指針。我說“預設”規則是因為我沒有針對某些功能（例如數據壓縮、列級加密、透明數據加密、始終加密等）的影響測試行內值。
LOB 溢出頁：如果值為 10k，則需要 1 個完整的 8k 頁溢出，然後是第 2 頁的一部分。如果沒有其他數據可以佔用剩餘空間（或者甚至被允許，我不確定該規則），那麼您在第二個 LOB 溢出數據頁上有大約 6kb 的“浪費”空間。
未使用空間：一個 8k 的數據頁就是：8192 字節。它的大小沒有變化。然而，放置在上面的數據和元數據並不總是很好地適合所有 8192 字節。並且行不能拆分到多個數據頁上。因此，如果您有 100 個字節剩餘但沒有行（或沒有適合該位置的行，取決於幾個因素）可以容納在那裡，則數據頁仍佔用 8192 個字節，並且您的第二個查詢僅計算數據頁。你可以在兩個地方找到這個值（請記住，這個值的一部分是保留空間的一部分）：
DBCC PAGE( db_name, file_id, page_id ) WITH TABLERESULTS;尋找ParentObject= “PAGE HEADER:” 和Field= “m_freeCnt”。該Value欄位是未使用的字節數。SELECT buff.free_space_in_bytes FROM sys.dm_os_buffer_descriptors buff WHERE buff.[database_id] = DB_ID(N'db_name') AND buff.[page_id] = page_id;這與“m_freeCnt”報告的值相同。這比 DBCC 更容易，因為它可以獲取許多頁面，但也需要首先將頁面讀入緩衝池。< 100保留的空間
FILLFACTOR。新創建的頁面不遵守FILLFACTOR設置，但執行 REBUILD 將在每個數據頁面上保留該空間。保留空間背後的想法是，它將被非順序插入和/或更新使用，這些插入和/或更新已經擴展了頁面上的行大小，因為可變長度列被更新的數據稍多（但不足以導致分頁）。但是您可以輕鬆地在數據頁上保留空間，這些空間自然不會獲得新行，也不會更新現有行，或者至少不會以會增加行大小的方式進行更新。Page-Splits（碎片化）：需要將行添加到沒有空間容納該行的位置會導致頁面拆分。在這種情況下，大約 50% 的現有數據被移動到新頁面，新行被添加到 2 個頁面之一。但是您現在有更多的可用空間，
DATALENGTH計算中沒有考慮到這些空間。標記為刪除的行。當您刪除行時，它們並不總是立即從數據頁中刪除。如果不能立即刪除它們，它們將被“標記為死亡”（Steven Segal 參考），稍後將通過幽靈清理過程物理刪除（我相信這就是名稱）。但是，這些可能與該特定問題無關。
鬼頁？不確定這是否是正確的術語，但有時數據頁在聚集索引的重建完成之前不會被刪除。這也
DATALENGTH將佔比加起來更多的頁面。這通常不應該發生，但幾年前我遇到過一次。稀疏列：稀疏列在大部分行
NULL用於一列或多列的表中節省空間（主要用於固定長度數據類型）。該SPARSE選項使NULL值類型增加 0 字節（而不是正常的固定長度量，例如 4 字節INT），但是，非 NULL 值每個固定長度類型占用額外的 4 個字節，可變數量變長類型。這裡的問題是DATALENGTH不包括 SPARSE 列中非 NULL 值的額外 4 個字節，因此需要重新添加這 4 個字節。您可以SPARSE通過以下方式檢查是否有任何列：SELECT OBJECT_SCHEMA_NAME(sc.[object_id]) AS [SchemaName], OBJECT_NAME(sc.[object_id]) AS [TableName], sc.name AS [ColumnName] FROM sys.columns sc WHERE sc.is_sparse = 1;然後對於每一
SPARSE列，更新原始查詢以使用：SUM(DATALENGTH(FieldN) + 4)請注意，上面添加標準 4 字節的計算有點簡單，因為它僅適用於固定長度類型。並且，每行還有額外的元數據（據我所知），這減少了可用於數據的空間，只需至少有一個 SPARSE 列。有關更多詳細資訊，請參閱使用稀疏列的 MSDN 頁面。
- 索引和其他（例如 IAM、PFS、GAM、SGAM 等）頁面：就使用者數據而言，這些不是“數據”頁面。這些將擴大表格的總大小。如果使用 SQL Server 2012 或更新版本，可以使用
sys.dm_db_database_page_allocations動態管理功能 (DMF) 查看頁麵類型（SQL Server 早期版本可以使用DBCC IND(0, N'dbo.table_name', 0);）：SELECT * FROM sys.dm_db_database_page_allocations( DB_ID(), OBJECT_ID(N'dbo.table_name'), 1, NULL, N'DETAILED' ) WHERE page_type = 1; -- DATA_PAGEnor （帶有那個 WHERE 子句）都
DBCC IND不會sys.dm_db_database_page_allocations報告任何索引頁面，只有DBCC IND會報告至少一個 IAM 頁面。
- DATA_COMPRESSION：如果您在聚集索引或堆上啟用
ROW或PAGE壓縮，那麼您可以忘記到目前為止提到的大部分內容。96 字節的頁頭、每行 2 字節的插槽陣列和每行 14 字節的版本資訊仍然存在，但數據的物理表示變得非常複雜（比壓縮時已經提到的要復雜得多）沒有被使用）。例如，使用行壓縮，SQL Server 嘗試使用盡可能小的容器來適應每一列、每一行。所以如果你有一個BIGINT列，否則（假設SPARSE也沒有啟用）總是佔用 8 個字節，如果值在 -128 和 127 之間（即有符號的 8 位整數），那麼它將只使用 1 個字節，如果值可能適合SMALLINT, 它只佔用 2 個字節。整數類型要麼是要麼NULL不0佔用空間，並且在映射列的數組中簡單地表示為是NULL或“空”（即）。0還有很多很多其他的規則。有 Unicode 數據（NCHAR,NVARCHAR(1 - 4000)，但沒有NVARCHAR(MAX)，即使儲存在行中）？SQL Server 2008 R2 中添加了 Unicode 壓縮，但鑑於規則的複雜性，如果不進行實際壓縮，則無法預測所有情況下“壓縮”值的結果。所以實際上，您的第二個查詢雖然在磁碟上佔用的總物理空間方面更準確，但只有在執行
REBUILD聚集索引時才真正準確。在那之後，您仍然需要考慮任何FILLFACTOR低於 100 的設置。即便如此，總是有頁眉，並且通常有足夠多的“浪費”空間，由於太小而無法容納其中的任何行，因此根本無法填充表，或者至少是邏輯上應該進入該槽的行。關於第二次查詢在確定“數據使用”方面的準確性，將頁面標題字節回退似乎是最公平的，因為它們不是數據使用：它們是業務成本成本。如果數據頁上有 1 行並且該行只是 a
TINYINT，那麼 1 字節仍然需要數據頁存在，因此需要 96 個字節的標題。該 1 個部門是否應該為整個數據頁收費？如果該數據頁隨後被部門#2 填滿，他們會平均分配“間接”成本還是按比例支付？似乎最容易將其退出。在這種情況下，使用8乘以的值number of pages太高。怎麼樣：-- 8192 byte data page - 96 byte header = 8096 (approx) usable bytes. SELECT 8060.0 / 1024 -- 7.906250因此，使用類似的東西：
(SUM(a.total_pages) * 7.91) / 1024 AS [TotalSpaceMB]針對“number_of_pages”列的所有計算。
AND，考慮到使用
DATALENGTH每個欄位不能返回每行元數據，應將其添加到您的每個表查詢中，您可以在其中獲取DATALENGTH每個欄位，過濾每個“部門”：
- 記錄類型和偏移到 NULL 點陣圖：4 字節
- 列數：2 個字節
- Slot Array：2字節（不包括在“記錄大小”中但仍需要考慮）
- NULL 點陣圖：每 8 列 1 個字節（對於所有列）
- 行版本控制：14 字節（如果數據庫有
ALLOW_SNAPSHOT_ISOLATION或READ_COMMITTED_SNAPSHOT設置為ON）- 可變長度列偏移數組：如果所有列都是固定長度的，則為 0 字節。如果任何列是可變長度的，那麼 2 個字節，加上每個可變長度列的 2 個字節。
- LOB 指針：這部分非常不精確，因為如果值為 ，則不會有指針
NULL，如果該值適合該行，則它可以比指針小得多或大得多，並且如果該值儲存在外-行，那麼指針的大小可能取決於有多少數據。但是，由於我們只需要一個估計值（即“swag”），因此 24 字節似乎是一個很好的使用值（嗯，和其他任何東西一樣好;-)。這是每個MAX欄位。因此，使用類似的東西：
- 通常（行標題 + 列數 + 槽數組 + NULL 點陣圖）：
([RowCount] * (( 4 + 2 + 2 + (1 + (({NumColumns} - 1) / 8) ))
- 一般來說（自動檢測是否存在“版本資訊”）：
+ (SELECT CASE WHEN snapshot_isolation_state = 1 OR is_read_committed_snapshot_on = 1 THEN 14 ELSE 0 END FROM sys.databases WHERE [database_id] = DB_ID())
- 如果有任何可變長度列，則添加：
+ 2 + (2 * {NumVariableLengthColumns})
- 如果有任何
MAX/ LOB 列，則添加：+ (24 * {NumLobColumns})
- 一般來說：
)) AS [MetaDataBytes]這並不准確，如果您在堆或聚集索引上啟用了行或頁面壓縮，這將再次不起作用，但絕對應該讓您更接近。
關於 15% 差異之謎的更新
我們（包括我自己）非常專注於思考數據頁面的佈局以及如何
DATALENGTH解釋我們沒有花很多時間查看第二個查詢的事情。我針對單個表執行該查詢，然後將這些值與報告的值進行比較sys.dm_db_database_page_allocations，它們與頁數的值不同。憑直覺，我刪除了聚合函式 和GROUP BY，並將SELECT列表替換為a.*, '---' AS [---], p.*。然後就很清楚了：人們必須小心在這些陰暗的網際網路上他們從哪裡獲取資訊和腳本;-)。問題中發布的第二個查詢並不完全正確，尤其是對於這個特定的問題。
- 小問題：除此之外
GROUP BY rows（並且在聚合函式中沒有該列）沒有多大意義，之間的 JOIN 在sys.allocation_units技術上sys.partitions並不正確。分配單元有 3 種類型，其中一種應該 JOIN 到不同的欄位。很多時候partition_id都是hobt_id一樣的，所以可能永遠不會有問題，但有時這兩個欄位確實有不同的值。- 主要問題：查詢使用該
used_pages欄位。該欄位涵蓋所有類型的頁面：數據、索引、IAM 等。當只關注實際數據時，還有另一個更合適的欄位：data_pages.我在考慮到上述項目的情況下調整了問題中的第二個查詢，並使用了支持頁面標題的數據頁面大小。我還刪除了兩個不必要的 JOIN：（
sys.schemas替換為對 的呼叫SCHEMA_NAME()）和sys.indexes（聚集索引始終是index_id = 1並且我們index_id在 中sys.partitions）。SELECT SCHEMA_NAME(st.[schema_id]) AS [SchemaName], st.[name] AS [TableName], SUM(sp.[rows]) AS [RowCount], (SUM(sau.[total_pages]) * 8.0) / 1024 AS [TotalSpaceMB], (SUM(CASE sau.[type] WHEN 1 THEN sau.[data_pages] ELSE (sau.[used_pages] - 1) -- back out the IAM page END) * 7.91) / 1024 AS [TotalActualDataMB] FROM sys.tables st INNER JOIN sys.partitions sp ON sp.[object_id] = st.[object_id] INNER JOIN sys.allocation_units sau ON ( sau.[type] = 1 AND sau.[container_id] = sp.[partition_id]) -- IN_ROW_DATA OR ( sau.[type] = 2 AND sau.[container_id] = sp.[hobt_id]) -- LOB_DATA OR ( sau.[type] = 3 AND sau.[container_id] = sp.[partition_id]) -- ROW_OVERFLOW_DATA WHERE st.is_ms_shipped = 0 --AND sp.[object_id] = OBJECT_ID(N'dbo.table_name') AND sp.[index_id] < 2 -- 1 = Clustered Index; 0 = Heap GROUP BY SCHEMA_NAME(st.[schema_id]), st.[name] ORDER BY [TotalSpaceMB] DESC;