SQL Server資料庫最佳化實戰（三），sqlserver

SQL Server資料庫最佳化實戰（三），sqlserver

前言：

本章主要來介紹一下收縮日誌和表的壓縮。

收縮記錄檔

--利用

exec sp_spaceused

語句查看資料庫大小

--右鍵資料庫屬性

--查看選項

--將復原模式設定成簡單

--右鍵資料庫-任務-收縮-檔案

--檔案類型選擇日誌

--查看收縮後資料庫大小

--右鍵資料庫屬性-選項

--將復原模式設定成完整

--注意：此時需要進行一次資料庫完整備份

表壓縮

--SQL Server 2005及以上版本支援表分區

表分區具體操作詳見以下網址：

http://blog.csdn.net/yole_grise/article/details/18658949

-- SQL Server 2008及以上版本支援表壓縮

（標準版不可以進行表壓縮）

--對於我們的主流客戶來說，隨著時間的積累、經營規模的擴大，總部資料庫越來越大，產生很多負面影響。

例如，一個100家門店的連鎖公司，三年下來資料庫可能會達到100G至200G。

由於資料庫增大導致的主要影響如下：

１、 導致磁碟儲存成本增高；200G的資料庫加上備份需要，至少得1T磁碟才勉強夠用。

２、 資料庫效能下降。

３、 資料庫出故障的潛在風險增加。（雖然沒有直接的證據，但簡單的推理就可得出該結論）

４、 備份一次資料庫的時間太長。備份檔案的複製和還原比較麻煩。

像現在我們有很多客戶資料庫都在100G以上了。在這種背景下，“表壓縮”閃亮登場。

操作方法：

--右鍵表-儲存-管理壓縮

--壓縮前表大小：

--查看一下表大小：

--表大小由81344KB壓縮到21328KB。

壓縮後的表大小一般會是原來表大小的1/4左右

--索引壓縮也是同樣的道理

--右鍵索引-儲存-管理壓縮

現在我們對於資料庫應用的瓶頸，主要還是在磁碟IO上面。也就是讀寫磁碟的效率。

而通過表壓縮，恰好能減輕磁碟的負擔，所以通過實際應用來看，表壓縮的效果非常明顯。

當資料庫超過100G時，通過壓縮最大的幾個表（和索引），可以將資料庫減至30G左右，

速度明顯提升，管理起來也更方便。

但另一方面，壓縮也有負作用。

壓縮會增加CPU的開銷，因為要不斷地進行壓縮演算法和解壓演算法。

問題來了：

什麼樣的表需要進行壓縮？

--查看錶大小IF OBJECT_ID('tempdb..#TB_TEMP_SPACE') IS NOT NULL DROP TABLE #TB_TEMP_SPACEGOCREATE TABLE #TB_TEMP_SPACE(NAME VARCHAR(500),ROWS INT,RESERVED VARCHAR(50),DATA VARCHAR(50),INDEX_SIZE VARCHAR(50),UNUSED VARCHAR(50))GOSP_MSFOREACHTABLE 'INSERT INTO #TB_TEMP_SPACE exec sp_spaceused ''?'''GOSELECT *,'ALTER TABLE [dbo].['+NAME+'] REBUILD PARTITION = ALLWITH (DATA_COMPRESSION = PAGE)' as sqlFROM #TB_TEMP_SPACEORDER BY REPLACE(DATA,'KB','')+0 DESC

答案：

1、查詢頻率小的。

2、佔用空間大的

這樣的表需要進行表壓縮。

典型代表表：u_sale_c

一般來說，表大於1G或索引大於1G的，都是需要壓縮的。

註：叢集索引是不需要進行壓縮的。因為叢集索引本身是不佔用空間的。

壓縮和收縮的區別：

壓縮，是指通過演算法和規則，減少資料庫的資料大小。

而收縮，是指將資料庫的可用空間釋放出來，變成作業系統的可用空間。

例子：

一個資料庫有100G，我們進行表壓縮，壓縮後只有40G了；但此時mdf檔案仍然是100G。

執行sp_spaceused會發現， unallocated space中有60G，這個就是未配置的空間。

所以，我們必須通過收縮資料庫，才能將這60G空間釋放掉。

SO，表壓縮操作與收縮資料庫是緊密相連的兩個操作；只有這樣才能達到我們預期的效果。

---------------------------華麗的分割線-----------------------------------

本章結束