《SQL Server企業級平台管理實踐》讀書筆記——SQL Server中收縮資料庫不好用的原因

標籤：style   blog   http   io   color   ar   使用   sp   檔案   

資料庫管理員有時候需要控制檔案的大小，可能選擇收縮檔案，或者把某些資料檔案情況以便從資料庫裡刪除。

這時候我們就要使用到DBCC SHRINKFILE命令，此命令的指令碼為：

DBCC SHRINKFILE (    { file_name | file_id }     { [ , EMPTYFILE ]     | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]    })[ WITH NO_INFOMSGS ]

由於DBCC SHRINKFILE一次運行會同時影響所有的檔案（包括資料檔案和記錄檔），使用者不能指定每個檔案的目標大小，其結果可能不能達到預期的要求。建議是做好規劃，對每個檔案確定預期目標，然後使用DBCC SHRINKFILE來一個檔案一個檔案的做比較妥當。

要注意一些幾點：

1、首先要瞭解資料檔案當前使用的使用方式。

收縮量的大小不可能超過當前檔案的空閑空間的大小。如果想要壓縮資料庫的大小，首先就要確認資料檔案的確有相應未被使用的空間。如果空間都在使用中，那就要先確認大量佔用空間的對象，比如：表格或索引，然後通過歸檔曆史資料，先把空間釋放出來。

2、主要資料檔案（primary File)是不能被清空的。能被完全清空的是有輔助資料檔案。

3、如果要把一個檔案組整個清空，要刪除分配在這個檔案組上的對象（表格或索引），或者把它們移動到其它檔案組上，DBCC SHRINKFILE不會幫你做這個工作。

把資料檔案裡面該刪除的資料和對象清除完、確認資料檔案（組）有足夠的空閑空間後，管理員就可以下DBCC SHRINKFILE命令來縮小或清空指定檔案了。如果是要縮小檔案，就填寫上要的tearget_size,如果要清空檔案，就選擇EmptyFile。SQL Server在做DBCC ShrinkFile的時候，會掃描資料檔案並對正在讀的頁面加鎖。所以對資料庫的效能會有所影響。但是這不是一個獨佔的行為，也就是說在收縮的時候，其他使用者照樣可以對資料庫進行讀寫訪問。所以不需要單獨安排伺服器停機時間來做，一般在資料庫維護的時候就可以進行。可以在進程中的任意點停止DBCC SHRINKFILE操作，任何已完成的工作都會保留。如果操作沒有在規定的時間內完成，也可以完全的停止它。

可是，有時候明明看到資料檔案裡有空間，為什麼就是不能壓縮或者情況它呢？這通常是因為資料檔案裡面雖然有很多空的頁面，但是這些頁面分散在各個區裡，使得整個檔案沒有很多空的區。

需要說明的是，DBCC SHRINKFILE做的，都是區一級的動作。它會把使用過的區前移，把沒有使用中的區從檔案中移除。但是，它不會把一個區裡面的空頁面移除、合并區，也不會在頁面裡面的空間移除、合并頁面。所以，一個資料庫中有很多隻使用了一兩個頁面的區，DBCC SHRINKFILE的效果會不明顯。

下面的案例來展示這個過程：

我們建立一個每一行都會佔用一個頁面的表格。表上沒有叢集索引，所以是一個堆表。往裡面插入8000條資料：

create table show_extent(   a int,   b nvarchar(3900))godeclare @i intset @i=1while @i<=1000begin    insert into show_extent values(1,replicate(N‘a‘,3900))    insert into show_extent values(2,replicate(N‘b‘,3900))    insert into show_extent values(3,replicate(N‘c‘,3900))    insert into show_extent values(4,replicate(N‘d‘,3900))    insert into show_extent values(5,replicate(N‘e‘,3900))    insert into show_extent values(6,replicate(N‘f‘,3900))    insert into show_extent values(7,replicate(N‘g‘,3900))    insert into show_extent values(8,replicate(N‘h‘,3900))    set @[email protected]+1enddbcc showcontig(‘show_extent‘)go 

可以看到這個表有1003個區，然後平均每個區裡面有8個頁面，共計8000個頁面...當然這裡麵包含區片段所以多處了3個區，24個頁的冗餘

我們下面刪除一部分資料，只保留a=5的這些個記錄，來對比前後的空間大小

sp_spaceused show_extentgodelete show_extent where a<>5gosp_spaceused show_extentgodbcc showcontig(‘show_extent‘)go   

可以看到...刪除之後的頁面空間是沒有釋放的。只是縮小了一點點。

區沒有變化，頁數減少了才一半左右，也就是說每個區平均現在只有4.1個頁面，在這種情況下去收縮資料庫是沒有效果的。

我們下面收縮一些看看效果

DBCC SHRINKFILE(1,40)

可以看到頁數不但沒有減少，而且增加了..這地方的原因是它按照邏輯分區計算的：1002*8約等於8016..

可以看到能夠收縮的空間很少很少..而且給出了一個好的解決方案就是：重新組織頁，但是這樣會影響效能，也就是說這樣它會重新組織頁，填充頁。

當然我們這裡還有另外一個解決方案，通過重建索引的方式把頁面重新排列一次。現在還沒有叢集索引，我們給他建立一個

create  clustered  index show_Ion show_extent(a)godbcc showcontig(‘show_extent‘)

可以看到...立馬縮減到1000頁，125個區，我們來看看能收縮多少資料。

 現在可以看到了可以收縮的百分比了。我們來執行收縮資料操作

可以看到我們已經將資料收縮至5120，說明此時收縮資料已經產生了作用。

如果不想建立叢集索引，可以把這張表的資料先移走，然後清空表格，再把資料插回來。當然這樣比較麻煩，還是有叢集索引管理起來比較方便。

剛才談到了造成Shrinkfile效果不佳的情況。在一個有叢集索引的的表格上，可以通過重建建立叢集索引來解決。但是如果區裡面存放的是text或者image之類的資料類型，SQL Server會單獨的頁面來存放這些資料。如果這類儲存頁面也發生同樣的問題，和堆一樣，做索引重建也不會影響到他們。

對於這種對象的處理方式，就是把這些可能有問題的對象都找出來，然後重建立立他們。可以利用DBCC Extentinfo這個命令打出資料檔案裡面的所有區的分配資訊。然後計算每個對象理論上區的數目和實際數目。如果實際數目遠大於理論的數目。那這個對象就是存在多於的片段，需我們考慮重建對象了。

我們下面來看一個例子： 

if exists(select name from sysobjects where NAME =‘extentinfo‘ and type=‘U‘)drop table extentinfogocreate table extentinfo ( [file_id] smallint,page_id int,pg_alloc int,ext_size int,obj_id int,index_id int,partition_number int,partition_id bigint,iam_chain_type varchar(50),pfs_bytes varbinary(10) )goif exists(select name from sysobjects where NAME =‘import_extentinfo‘ and type=‘P‘)drop procedure import_extentinfogocreate procedure import_extentinfo as dbcc extentinfo(‘TestDB‘)goinsert extentinfo exec import_extentinfogo select name as table_name,[file_id],obj_id, index_id, partition_id, ext_size,‘actual page count‘=sum(pg_alloc),‘actual extent count‘=count(*),‘expected extent count‘=ceiling(sum(pg_alloc)*1.0/ext_size), --一個對象的所有盤區頁數的各總和/‘expected extents / actual extents‘ = (ceiling(sum(pg_alloc)*1.00/ext_size)*100.00) / count(*)from extentinfo inner join sysobjectson obj_id=idgroup by [file_id],obj_id, index_id,partition_id, ext_size ,namehaving count(*)-ceiling(sum(pg_alloc)*1.0/ext_size) > 0 order by partition_id, obj_id, index_id, [file_id]

這裡我們通過DBCC extentinfo命令來查看資料庫中的區明細，然後查看理論值和實際值的差距，如果存在大量的片段，我們就需要進行重建清理了。

DBCC EXTENTINFO命令用於查詢某個資料庫、或者某個資料對象（主要是資料表）的盤區分配情況。其文法結構如下：
DBCC
EXTENTINFO（dbname,tablename,indexid）

我們給出這個命令顯示行的明細：

欄位名稱	說    明
file_id	資料庫的資料檔案編號
page_id	在某個盤區中的第一個頁面的頁面號

欄位名稱	說    明
pg_alloc	該盤區為資料庫分配的頁面數量m（1≤m≤8）
ext_size	盤區的大小，以頁面為單位
object_id	資料庫物件的ID
index_id	表示資料對象的類型
partition_number	分區號
rows	大約的資料行數
hobt_id	儲存資料的堆或B樹的儲存單元ID

我們來找個資料庫看一下存在片段的情況：

可以看到中，箭頭所指的這個行資料，理論應該為一個區就可以，但是實際上它建立了兩個分區...所以這種情況可以考慮整理片段，進行重建，釋放片段。

《SQL Server企業級平台管理實踐》讀書筆記——SQL Server中收縮資料庫不好用的原因