SQL Server 2008中的數據表壓縮功能詳細介紹
SQL Server 2005 SP2為我們帶來了vardecimal功能,當時針對decimail和numeric數據類型推出了新的存儲格式--vardecimal。vardecimal存儲格式允許 decimal和numeric數據類型的存儲作為一個可變長度列。 這項功能使得原來定長的decimal數據在數據文件中以可變長的格式存儲,據稱這項功能可以為典型的數據倉庫節(jié)省30%的空間,而SQL Server 2008在這一基礎上又進一步增強了數據壓縮功能。SQL Server 2008現(xiàn)在支持行壓縮和頁面壓縮兩種選項,數據壓縮選項可以在以下對象上啟用:
1.未創(chuàng)建聚簇索引的表
2.創(chuàng)建聚簇索引的表
3.非聚簇索引(對表設置壓縮選項不會影響到該表上的非聚簇索引,因此聚簇索引的壓縮需要單獨設置)
4.索引視圖
5.分區(qū)表和分區(qū)索引中的單個分區(qū)
使用數據壓縮的意義:
首先讓我們來看看,為什么在存儲成本不斷降低的今天,微軟還要煞費苦心地在SQL Server中實現(xiàn)并且不斷改進數據壓縮技術呢?
盡管存儲成本已經不再是傳統(tǒng)意義上的首要考慮因素,但是這并不代表數據庫尺寸不是一個問題,因為數據庫尺寸除了會影響到存儲成本之外,還極大地關聯(lián)到管理成本和性能問題。
致于為什么會有管理成本的問題?因為數據庫需要備份,數據庫的尺寸越大,那么備份時間就會越長,當然另外一點就是消耗的備份硬件成本也會隨之提高(包括需要的備份介質成本和為了滿足備份窗口而需要更高級的備份設備帶來的采購成本),還有一種管理成本就是數據庫的維護成本,例如我們經常需要完成的 DBCC任務,數據庫尺寸越大,我們就需要更多的時間來完成這些任務。
接著我們再看看性能問題。SQL Server在掃描磁盤讀取數據的時候都是按照數據頁為單位進行讀取的,因此如果一張數據頁中包含的數據行數越多,SQL Server在一次數據頁IO中獲得的數據就會越多,這樣也就帶來了性能的提升。
最后考慮存儲的成本,按照原先SQL Server 2005 SP2中vardecimal的壓縮數據為例,30%的空間節(jié)省也就意味著30%的存儲成本,而按照SQL Server 2008當前放出的測試數據,采用新的數據壓縮技術可以達到2X-7X的存儲率,再加上如果企業(yè)要考慮容災而增加的存儲空間,這樣節(jié)省的存儲硬件成本也將是想當可觀的。
使用數據壓縮方法:
SQL Server 2008中的壓縮選項可以在創(chuàng)建表或索引時通過Option進行設置,例如:
如果需要改變一個分區(qū)的壓縮選項,則可以用以下語句:
如果需要為分區(qū)表的各個分區(qū)設置不同的壓縮選項,可以使用以下的語句:(SQL Server 2008可以對不同的分區(qū)使用不同的壓縮選項,這一點對于數據倉庫應用是非常重要的,因為數據倉庫的事實表通常都會有一個或數個熱分區(qū),這些分區(qū)中的數據經常需要更新,為了避免數據壓縮給這些分區(qū)上的數據更新帶來額外的處理載荷,可以對這些分區(qū)關閉壓縮選項)
ON PS1 (col1)
WITH (
DATA_COMPRESSION = ROW ON PARTITIONS(1),
DATA_COMPRESSION = PAGE ON PARTITION(2 TO 4));
如果是為某個索引設置壓縮選項的話,可以使用:
CREATE INDEX IX_TestTable_Col1 ON TestTable (Col1) WITH (DATA_COMPRESSION = ROW);
如果是修改某個索引的壓縮選項,可以使用:
SQL Server 2008同時還提供了一個名為sp_estimate_data_compression_savings存儲過程幫助DBA估計激活壓縮選項后對象尺寸。
使用數據壓縮的原理:
對于行壓縮,SQL Server 2008采用以下三種方法來節(jié)省存儲空間:
減少了與記錄相關聯(lián)的元數據開銷。此元數據為有關列、列長度和偏移量的信息。在某些情況下,元數據開銷可能大于舊的存儲格式。
它對于數值類型(例如,integer、decimal和float)和基于數值的類型(例如,datetime和money)使用可變長度存儲格式。
它通過使用不存儲空字符的可變長度格式來存儲定長字符串。
對于頁面壓縮,SQL Server 2008則是在一張數據頁面上依次采用:
1.行壓縮
2.前綴壓縮
3.字典壓縮
使用數據壓縮注意事項:
盡管SQL Server 2008的數據壓縮功能非常有價值,但是仍然需要注意一些問題:
數據壓縮功能僅在企業(yè)版和開發(fā)版中可用
數據壓縮可以讓一張數據頁存儲更多的數據行,但是并不能改變單行數據最長8060字節(jié)這一限制。
在一張已經設置了數據壓縮的表上創(chuàng)建聚簇索引時,聚簇索引默認繼承原表上的壓縮選項
在未設置聚簇索引的表上設置頁面壓縮時,只有以下情況才會獲得頁面壓縮的實際效果:
1.數據使用BULK INSERT語法添加到表中
2.數據使用INSERT INTO ... WITH (TABLOCK)語法添加到表中
3.執(zhí)行帶有頁面壓縮選項的ALTER TABLE ... REBUILD命令
在未設置聚簇索引的表上更改壓縮選項,會導致該表上所有非聚簇索引都需要重建,因為這些非聚簇索引指向的數據行地址已經都發(fā)生了改變。
在改變壓縮選項時所需要的臨時空間大小與創(chuàng)建索引是所需要的空間是一樣的,因此對于分區(qū)表,我們可以逐個分區(qū)設置壓縮選項來減少臨時空間的需求壓力。
由于SQL Server 2008中數據壓縮技術其實是SQL Server 2005 SP2中vardecimal技術的一個超集,因此設置了數據壓縮后就沒有必要保留vardecimal了。當然SQL Server 2008為了保持向后兼容性,在當前版本中仍然保留了vardecimal,但是SQL Server 2008的下一個版本及可能就會棄用vardecimal選項,因此做了這些設置的數據庫應該盡早改變到數據壓縮設置下。
SQL Server 2008的壓縮選項是工作在存儲引擎層的,對于SQL Server的其他部件來說這一特性是透明的,因此當我們用BULK LOAD的方式將外面的數據導入SQL Server時,會顯著的增加CPU的工作載荷,同時將以壓縮的數據表導出到外部文件時,可能會消耗比原來多很多的空間。
下面是其他網友的補充
SQL Server 2008中的數據壓縮
SQL Server 2008中引入了數據壓縮的功能,允許在表、索引和分區(qū)中執(zhí)行數據壓縮。這樣不僅可以大大節(jié)省磁盤的占用空間,還允許將更多數據頁裝入內存中,從而降低磁 盤IO,提升查詢的性能。當然,凡事有利有弊,在啟用數據壓縮后,數據庫服務器就需要額外的CPU資源來進行壓縮處理。一般說來,數據庫服務器的CPU占 用率不會太高,而磁盤IO容易成為瓶頸,所以在大多數情況下對大數據庫特別是數據倉庫啟用該項功能還是利大于弊。
SQL Server 2008的數據壓縮分為行壓縮和頁壓縮兩種。行壓縮主要是通過將固定長度類型存儲為可變長度類型來實現(xiàn),同時還減少了與記錄相關聯(lián)的元數據開銷。頁壓縮在行壓縮的基礎上又增加了前綴壓縮和字典壓縮,能獲得更大的壓縮率。
要 啟用數據庫壓縮只需在建表語句后加入WITH (DATA_COMPRESSION = ROW)或是WITH (DATA_COMPRESSION = PAGE)即可。如需將現(xiàn)有的索引修改為啟用壓縮,可通過ALTER INDEX index ON Table REBUILD WITH (DATA_C0MPRESSION=ROW)或ALTER INDEX index ON Table REBUILD WITH (DATA_C0MPRESSION=PAGE)實現(xiàn)。
最后提供一段簡單的用以判斷是否需要壓縮數據表的腳本,并自動生成壓縮腳本供系統(tǒng)管理員執(zhí) 行。這里用到未公開的存儲過程sp_MSforeachtable。在這段腳本中@precommand參數用于執(zhí)行command指令執(zhí)行前的SQL命 令,建立一張臨時表用于保存數據表的信息,@command1參數表示需要執(zhí)行的SQL命令,對每一張表都利用sp_spaceused存儲過程獲取表的 磁盤占用信息并保存到建立的臨時表中,@postcommand參數用于執(zhí)行command指令后的SQL命令,將之前建立的臨時表與系統(tǒng)關聯(lián),根據設置 的條件(數據表占用空間大于10G)生成數據表壓縮腳本。
exec sp_MSforeachtable @precommand=N' create table ##( id int identity, name sysname, rows int, reserved Nvarchar(50), data varchar(50), indexdata varchar(50), unused varchar(50))', @command1=N'insert into ##(name,rows,reserved,data,indexdata,unused) exec sp_spaceused ''?'' update ## set data=SUBSTRING(data, 1, LEN(data) - 2) where id=scope_identity() AND LEN(data) >=2', @postcommand=N'SELECT ''ALTER TABLE '' + TABLENAME + '' REBUILD WITH ( DATA_COMPRESSION = PAGE )'' FROM sys.tables A JOIN (SELECT C.name + ''.'' + A.name AS TABLENAME, object_id FROM ## A JOIN sys.objects B ON A.name = B.name JOIN sys.schemas C ON B.schema_id = C.schema_id WHERE CAST(data AS int) > 10000000 AND object_id IN (SELECT object_id FROM sys.tables)) B ON A.object_id = B.object_id AND type = ''U'';drop table ##'
相信到了這里,朋友們對于SQL Server 2008中的數據壓縮技術有了一個較為全面的了解。
相關文章
SQL Server2008 Order by在union子句不可直接使用的原因詳解
這篇文章主要介紹了SQL Server2008 Order by在union子句不可直接使用的原因詳解,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2020-07-07使用Sqlserver事務發(fā)布實現(xiàn)數據同步(sql2008)
事務的功能在sqlserver中由來已久,因為最近在做一個數據同步方案,所以有機會再次研究一下它以及快照等,發(fā)現(xiàn)還是有很多不錯的功能和改進的。這里以sqlserver2008的事務發(fā)布功能為例,對發(fā)布訂閱的方式簡要介紹一下操作流程,一方面做個總結備份,一方面與大家進行一下分享和交流2013-03-03清理SQL Server 2008日志文件Cannot shrink log file 2 的解決方案
SQL 2008日志文件占了23G硬盤空間,而事務日志已經截斷(Truncate),實際日志內容很小,1G都不到,想要釋放日志文件霸占的多余空間2013-04-04SQLServer 2008中SQL增強之一:Values新用途
SQL Server 2008中新增功能:可以使用單個Insert命令插入多行2011-05-05SQL Server 2008 清空刪除日志文件(瞬間日志變幾M)
sql 在使用中每次查詢都會生成日志,但是如果你長久不去清理,可能整個硬都堆滿哦,筆者就遇到這樣的情況,直接網站后臺都進不去了。下面我們一起來學習一下如何清理這個日志吧2013-05-05SQL Server 2008 安裝和配置圖解教程(附官方下載地址)
最近很多朋友選用sqlserver2008,據說SQL Server 2008的性能與功能上比2005更好,這里就將sql server 2008的安裝與配置分享下,希望能幫到需要的朋友2012-05-05