查找數據庫中所有表的未壓縮大小

在 Dynamics AX 中有一種記憶體機制，可以將表配置為載入到記憶體中並進行記憶體。此記憶體限制為一定數量的 KB 以防止記憶體問題。我正在談論的設置被呼叫entiretablecache，並在請求單個記錄後立即將整個表載入到記憶體中。

直到最近，我們依靠一些腳本來驗證具有此設置的表的大小，以查看表大小是否超過此限制。

然而現在，壓縮開始發揮作用，像sp_spaceused或sys.allocation_units這樣的東西似乎報告了壓縮數據實際使用的空間。

顯然，應用程序伺服器正在處理未壓縮的數據，因此 SQL Server 中磁碟上的數據大小無關緊要。我需要未壓縮數據的實際大小。

我知道sp_estimate_data_compression_savings但顧名思義，這只是一個估計值。

我希望尺寸盡可能正確。

我能想到的唯一方法是一些複雜的動態 SQL 創建與壓縮表具有相同結構的未壓縮表，將壓縮數據插入該影子表中，然後檢查該影子表的大小。

不用說，這有點乏味，並且需要一段時間才能在數百 GB 的數據庫上執行。

Powershell 可能是一個選項，但我不想遍歷所有表以select *對它們執行 a 以檢查腳本中的大小，因為這只會淹沒記憶體並且可能也需要很長時間。

簡而言之，如果可能的話，我需要一種方法來獲取每個表的大小，因為它一旦被解壓縮並且在呈現給應用程序的等式中會出現碎片。我對不同的方法持開放態度，首選 T-SQL，但我不反對 Powershell 或其他創造性方法。

假設應用程序中的緩衝區是數據的大小。bigint 始終是 bigint 的大小，字元數據類型是每個字元 2 個字節（unicode）。BLOB 數據也採用數據的大小，列舉基本上是一個 int，數字數據是 numeric(38,12)，datetime 是 datetime 的大小。此外，沒有NULL值，它們要麼儲存為空字元串，要麼儲存1900-01-01為零。

沒有關於如何實現的文件，但假設是基於一些測試和 PFE 和支持團隊使用的腳本（顯然也忽略了壓縮，因為檢查是內置在應用程序中的，應用程序無法分辨如果基礎數據被壓縮），它還會檢查表大小。例如，此連結指出：

避免對大表使用 EntireTable 記憶體（在 AX 2009 中超過 128 KB 或 16 頁，在 AX 2012 中超過“整個表記憶體大小”應用程序設置

$$ default: 32KB, or 4 pages $$) – 改為使用記錄記憶體。

我需要未壓縮數據的實際大小。

…

我希望尺寸盡可能正確。

雖然對這些資訊的渴望當然是可以理解的，但由於錯誤的假設，獲取這些資訊，尤其是在“盡可能正確”的情況下，比每個人預期的要棘手。無論是做問題中提到的未壓縮影子表的想法，還是@sp_BlitzErik 在評論中關於恢復數據庫和解壓縮檢查的建議，都不應假設未壓縮表的大小 == 記憶體中所述數據的大小在應用伺服器上：

1. 表中的所有行都被記憶體了嗎？還是只是在一個範圍內？這裡的假設是一切，這可能是正確的，但我認為至少應該提到這可能不是這種情況（除非文件另有說明，但無論如何這是一個小問題，只是不想它不被提及）。

問題已更新為狀態：是的，所有行都被記憶體。 2. 結構成本

1. 在數據庫方面：數據庫方面的

頁面和行成本：頁面上適合多少行取決於許多可能會導致估算的因素。即使 aFILLFACTOR為 100（或 0），頁面上仍有可能剩餘一些未使用的空間，因為它不足以容納整行。這是對頁眉的補充。此外，如果啟用了任何快照隔離功能，我相信版本號會佔用每行額外的 13 個字節，這將導致估計值下降。還有其他與行的實際大小相關的細節（NULL 點陣圖、可變長度列等），但到目前為止提到的項目應該單獨說明這一點。 2. 在應用伺服器端：

使用什麼類型的集合來儲存記憶體的結果？我假設這是一個 .NET 應用程序，那麼它是一個 .NET 應用程序DataTable嗎？通用列表？排序字典？每種類型的集合都有不同數量的偷聽。我不希望任何選項必然反映數據庫端的 Page 和 Row 成本，特別是在規模上（我確信少量的行可能沒有足夠的變化，但你不是在尋找差異以數百字節或僅幾 kB 為單位）。 3. 數據類型

1. 在 DB 端：

CHAR/VARCHAR數據以每個字元 1 個字節儲存（暫時忽略雙字節字元）。XML被優化為不會佔用幾乎與文本表示所暗示的一樣多的空間。此數據類型創建一個元素和屬性名稱的字典，並用它們各自的 ID 替換文件中對它們的實際引用（實際上還不錯）。否則，字元串值都是 UTF-16（每個“字元”2 或 4 個字節），就像NCHAR/一樣NVARCHAR。DATETIME2介於 6 到 8 個字節之間。DECIMAL介於 5 到 17 個字節之間（取決於精度）。 2. 在應用伺服器端：

字元串（再次假設 .NET）始終為 UTF-16。沒有對 8 位字元串進行優化，例如什麼是有效VARCHAR的。但是，字元串也可以是“interned”，這是一個可以多次引用的共享副本（但我不知道這是否適用於集合中的字元串，或者如果是，是否適用於所有類型的集合）。XML可能會或可能不會以相同的方式儲存在記憶體中（我將不得不查找）。DateTime總是 8 個字節（像 T-SQL DATETIME，但不像DATE, TIME, or DATETIME2）。Decimal始終為16 個字節。

綜上所述：在數據庫端幾乎沒有什麼可以在應用伺服器端獲得相當準確的記憶體佔用大小。在載入特定表後，您需要找到一種方法來詢問應用程序伺服器本身，因此要知道它有多大。而且我不確定調試器是否會讓您看到已填充集合的執行時大小。如果不是，那麼接近的唯一方法是遍歷表的所有行，將每列乘以適當的**.NET**大小（例如INT= * 4、VARCHAR= DATALENGTH() * 2、NVARCHAR= DATALENGTH()、XML= 🙃 等），但這仍然留下了問題集合的成本加上集合的每個元素。

給定問題中的一些新定義，人們可能會執行以下查詢以獲得相當接近的結果。表是否被壓縮並不重要，儘管由每個人來確定在生產環境中掃描所有行是否合適（可能從恢復或非高峰時間進行）：

SELECT
  SUM( DATALENGTH([NVarcharColumn_1]) + DATALENGTH([NVarcharColumn_N]) ) + 
  SUM( (DATALENGTH([VarcharColumn_1]) + DATALENGTH([VarcharColumn_N])) * 2 ) + 
  SUM(4 * [number_of_INT_columns]) +
  SUM(8 * [number_of_BIGINT_and_DATETIME_columns]) +
  SUM(16 * [number_of_DECIMAL/NUMERIC_and_UNIQUEIDENTIFIER_columns]) +
  etc..
FROM [SchemaName].[TableName] WITH (NOLOCK) -- assuming no Snapshot Isolation

但請記住，這並沒有考慮到集合或集合元素的成本。並且不確定我們是否可以在沒有調試器的情況下獲得該值（或者可能像 ILSpy 之類的東西，但我不建議這樣做，因為它可能違反 EULA，具體取決於當地法律）。

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