SQL Server dbcc shrinkfile 不起作用

標籤：style   blog   http   io   ar   資料   sp   2014   問題   

方法 1、重建叢集索引。

方法 2、重建堆表。

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

           原理說明。dbcc shrinkfile 的操作單位是區（extent 也有的書上說成是擴充），資料存在資料頁中，8 個資料頁的集合叫做一個區（extent)。

           區有統一區和混合區之分。如果一個對象所佔的空間大於 8 個頁面（一個頁面在大小是8KB，所以說一個區的大小是64KB）對象的空間分配都

           要以區為單位（就是說每一次分配最少給它64KB），如果對象小於64KB，每次的空間分配都以頁為單位（就是說一次給它 8KB的空間）。

例子、

      create table T(X int ,String nvarchar(4000));
      go -- 這個表的第一行都接近8KB 所以一個頁面只可以存一行。    

      declare @i as int =1;
      while @i <=1000
      begin
      insert into T(X,String) values
      (1,replicate(N‘A‘,4000)),
      (2,replicate(N‘A‘,4000)),
      (3,replicate(N‘A‘,4000)),
      (4,replicate(N‘A‘,4000)),
      (5,replicate(N‘A‘,4000)),
      (6,replicate(N‘A‘,4000)),
      (7,replicate(N‘A‘,4000)),
      (8,replicate(N‘A‘,4000));
      set @i = @i +1;
      end
      go -- 向表中插入8000行資料。

      資料的頁面如下、

           dbcc showcontig(‘T‘);

            

           可以看到T這個表有8000面

     這時我們來做一件事，就是用delete 命令刪除表中資料的 7/8；代碼如下。

     delete from T 

     where T.X != 1; 

     這樣就只留下了X = 1 的行。它要佔的空間是1000個頁面、可是事實真的是主樣嗎？我們用dbcc showcontig(‘T‘)看一下

     dbcc showcontig(‘T‘);

            

     可以看到它還是佔著4124個頁面不是我們想的1000個頁面。因為表T是一個堆表，它內部的資料是無序的也就是說不是 1、2、3、4、5、6、7、8

     這種方式存的。內部的區可能是這樣的

     話說回來，如果表T 它是有序的呢？ 前1000個頁面存的會是 1、也就是說後面的7000個頁面不會出現1。堆表是無序的每個頁面都可能

     出現 1。

重點：

      問題的重點來了，dbcc shrinkfile 的操作對象是區、也就是說只有空的區才可以被shrinkfile回收、剛才的dbcc shrinkfile(‘T‘) 的返回可以看出有4124個頁面

      存在，說明500（4123/8）多個區中儲存著 1 的資料、所以它沒有被回收。如果聚集表後面7000個頁面所在的區都會被回收。因為這些區都是空的。

--------------------------------------------------------------------------------------------------------------------------------------------------------------

方法 1、：

         重建堆表。

         alter table T rebuild;

        go

        

        可以看到頁面的佔用由4123頁變回了1001頁。

方法 2、

       加叢集索引      

      create clustered index idx_X
      on T(X);
      go

          

     可以看到加叢集索引也可以達到一樣的效果。

總結：DBA 要定期對錶進行管理以減少它的片段化程度，減少select 時的IO量，提高select 的速度。事實上叢集索引對insert 可以說基本上沒有太

        大的影響。對select ,update ,delete 的好處是大大的。本例可以看到它對空間的佔用也是有利的。

        所以、對錶加一個叢集索引還是相當不錯的。

 

 

 

 

     

 

SQL Server dbcc shrinkfile 不起作用