SQL Server 2012 壓縮整個表的列
我想知道 SQL Server 是否支持整個表的一種字典壓縮,而不僅僅是一個頁面。
我正在開發的系統最初並不是為今天處理的大量數據而創建的。我目前遇到的問題如下:
該應用程序允許使用者創建法律契約。這些契約是標準化的,但使用者可以根據自己的意願調整契約的內容(文本)。
為了促進這一點,每份契約都會製作一份標準化契約文本的副本。實際上,我們發現使用者幾乎從不編輯契約文本,因此我們最終得到了一個包含大量重複數據的表格。
通常我會更改數據庫模型以適應案例,但是這是一個遺留系統,這樣的更改非常昂貴。正在研究它的替代品,所以像這樣的投資並不容易完成。
是否可以對整個表進行列字典壓縮,而不僅僅是 1 頁?
我們在本地 SQL 集群中使用 SQL Server 2012。
問題是表大小為 80GB,整個數據庫大小為 180GB。該表佔用了大量空間,並且我們沒有足夠的記憶體,因此 SQL Server 不斷解除安裝數據。
該表的數據用於生成代表契約的 PDF。每次使用者修改合約狀態時,都會生成並儲存一個新的 PDF 用於審計目的,這會在該表上產生大量讀取。
讀取到磁碟(因為 SQL 伺服器不斷從記憶體中解除安裝表)。這對我們的 SAN 造成了巨大的 IO 壓力。
記憶體問題正在解決中,但這需要幾週時間。現在可以說,目前簡單地插入更多記憶體不是一種選擇。
我的想法是:作為一個短期解決方案 - 壓縮數據,這將大大減少表的大小,從而使 SQL Server 可以將表保存在記憶體中,從而減輕 SAN 上的 IO 壓力。
是否可以對整個表進行列字典壓縮,而不僅僅是 1 頁?
對於您所描述的情況,SQL Server 的內置壓縮可能根本不會做任何事情。我假設文件儲存在
NVARCHAR(MAX)
列中以允許非 ASCII 字元並允許超過 4,000 個字元的值。任何 SQL Server 的方法(行壓縮、頁面壓縮或它們附帶的 unicode 壓縮)都不會壓縮頁外值,因此很可能不會觸及所有文件。簡而言之:不,SQL Server 無法自動為您提供幫助。
**可能的選項:(**有點手動和hacky)
如果您使用的是 2016 或更高版本或 Azure SQL$$ 1 $$$$ 2 $$那麼您可以使用
COMPRESS
/DECOMPRESS
函式將數據作為較小的 blob 儲存在 VARBINARY 列中。要以對您的應用程序透明的方式執行此操作,您可以創建一個儲存數據的備份表,將真實表替換為從中選擇並具有INSTEAD OF
觸發器來壓縮傳入數據的視圖。如果您已經擁有大量數據,則可能需要很長時間才能推出。為了消除對壓縮現有內容的多個小時維護視窗的需要,在備份表中同時包含NVARCHAR(MAX)
和VARBINARY(MAX)
列,並讓視圖決定DocumentText = ISNULL(DECOMPRESS(CompressedText), UncompressedText)
然後你可以讓一個程序緩慢地處理表,一次壓縮一批行,保持批次足夠小以避免鎖定問題,這樣你就可以讓程序在生產中順利進行。當然,如果您不僅可以影響應用程序的數據層,那麼在此處實施文件壓縮會更有效,因為您可以節省網路傳輸以及數據庫伺服器上的儲存和記憶體。
如果您的許多文件確實相同,那麼您可能會從重複數據刪除中看到比單獨壓縮更大的收益,您可以使用類似的技術來做到這一點:將文件儲存在自己的表中,並將它們的雜湊作為鍵/索引並儲存在主表中使用 backing-table-plus-view-and-trigger 方法或應用程序數據操作層中的其他位置對它們的引用。在 2016/Azure(或者如果使用 CLR 是一個選項)中,您也可以壓縮儲存的文件以節省額外的空間。
如果您的許多文件幾乎相同,因為它們是從模板建構的,那麼另一個選擇是儲存模板和差異,儘管這可能需要在應用程序層或通過 CLR 實現,因為我無法想像在其中進行字元串差異和更新檔TSQL 將足夠高效。這僅在文件以未壓縮形式(即 RTF 文件、舊辦公格式、markdown 文本;不是最近的 MS Office 格式或 PDF)儲存時才有效,因為壓縮格式在第一次更改後可能會有所不同。
*這些想法增加了複雜性並導致寫入性能下降,因此您需要考慮這些負面因素來判斷它們。*我自己也在考慮類似的情況,我們在遺留應用程序中以文本格式收集大量pickle數據(總共幾十 Gb,每個項目平均幾千個字元)。我會看看我是否可以在完成後獲得分享結果的許可。
–
$$ 1 $$後來更新了這個問題,指出在他們的情況下正在使用 SQL2012 - 重複數據刪除方法仍然有效,我在答案中保留了壓縮方法,因為它可能對其他有類似情況的人有所幫助。事實上,在 SQL Server 2012中可以使用自定義 CLR 模組來實現壓縮,http://aboutsqlserver.com/2015/04/07/compressing-lob-xml-data-in-the-database/是快速搜尋的第一個好看的例子,但還有更多。
$$ 2 $$正如 Paul 在下面的評論中所指出的,隨著 2017 年 LOB 的支持將出現在壓縮列儲存索引中,因此對於使用最新版本的 SQL Server 的人們來說,這將是另一種研究途徑。