原地址：http://www.cnblogs.com/smjack/archive/2010/03/29/1699941.html

?

雖然對數(shù)據(jù)庫進行分區(qū)本身就能提高查詢的性能，結(jié)合壓縮，也能減少每次查詢的IO。但如果數(shù)據(jù)持續(xù)增長，過于久遠的歷史數(shù)據(jù)就成了一個包袱，它們從 來不在查詢結(jié)果中出現(xiàn)，卻或多或少的影響著每次查詢的時間，成了一個揮之不去的陰影。此外，由于一個分區(qū)表的分區(qū)是有上限的（在2005中這一上限是 1000），我們也并不能在一張表上一直分區(qū)下去。所以在這種情況下 ，我們一般需要同時擁有兩張表，一張保存了最近的數(shù)據(jù)，用來應付所有的查詢，這張表要足夠精簡，在其上的查詢要足夠敏捷；同時有另一張表，保存所有過時的數(shù)據(jù)——我們并不能把過時的數(shù)據(jù)一刪了事。

這里面的關(guān)鍵問題是，既然數(shù)據(jù)是隨著時間持續(xù)增長的，那么當下有用的數(shù)據(jù)可能在幾天后就過時了，那么 怎樣將這一部分過時的數(shù)據(jù)從活動表遷移到存檔表，而且要保證遷移過程的快速、平穩(wěn)呢 ？如果采用常規(guī)的Select、Insert、Delete來進行數(shù)據(jù)遷移，會有如下問題：

1. IO過大，效率必然較低。
2. 遷移過程表被鎖住，所有查詢都會被擱置。
3. 恢復困難，如果想將移出的數(shù)據(jù)再移回來，需要進行同樣的操作，IO和鎖表的問題同樣存在。

那么很容易想到，利用之前提到的分區(qū)的Switch操作來解決遷移的問題， 將整個分區(qū)而不是數(shù)據(jù)在活動表和存檔表中遷移。 由于Switch的元數(shù)據(jù)操作屬性，這一幾乎沒有什么IO的操作效率極高，而且也不會鎖表。基于以上方法進行的周期性自動化的數(shù)據(jù)遷移，就是 Slide Window的基礎 。

原理圖

假設我們已經(jīng)有一張活動表，分了四個區(qū)，分別對應去年，今年一月、二月以及三月以后的數(shù)據(jù)：

同時我們有另外一個存檔表，分成了兩個區(qū)， 第一個區(qū)對應今年以前的數(shù)據(jù)，另外一個分區(qū)的范圍是今年之后：

注意， 活動表的第一個分區(qū) 、 存檔表中的第二個分區(qū)是沒有數(shù)據(jù)的，這是進行Slide Window的前提條件 。

現(xiàn)在，我們考慮將活動表中的一月份的數(shù)據(jù)放入存檔表中，而且我們還要保證在遷移之后，兩張表保持和遷移前相似的狀態(tài)。

那么我們可以采用如下的步驟：

1.在存檔表中建立新分區(qū)：

2.將活動表的第二個分區(qū)挪到存檔表的第二個分區(qū)中：

遷移的結(jié)果如下：

至此，我們已經(jīng)完成了數(shù)據(jù)的遷移，但為了恢復兩張表到之前的狀態(tài)，我們還需要以下兩個步驟：

3.合并存檔表的第一和第二個分區(qū)。

4.拆分活動表的第三個分區(qū)。

最后的結(jié)果，活動表：

存檔表：

? 我 們可以看到遷移過后，兩張表的分區(qū)數(shù)量沒有變，而且存檔表的第二個分區(qū)依然是空的。當需要遷移二月份的數(shù)據(jù)時，我們可以采用和上面完全一樣的步驟進行遷 移。而這一過程，類似在時間軸上開了一個窗口，將當前數(shù)據(jù)在活動表上展示，隨著時間推移，窗口不斷向前滑動（活動表的邊界前移），而且窗口大小（活動表的 分區(qū)數(shù)）始終保持不變，這就是Slide Window（滑動窗口）這一名稱的來源。

建立存檔表

建立存檔表最簡 單的方法是選中分好區(qū)的活動表，在Storage菜單中選擇“Manage Partition”，然后選擇“Create a stagin table for partition switching”。建議將“Staging table name”改成固定的沒有數(shù)字后綴的名字，之后隨便選擇一個“Switch Parition”，最后生成創(chuàng)建存檔表的腳本。

由于指定了待切換的分區(qū)，所以這里腳本中會添加對應的約束，由于我們要創(chuàng)建的存檔表并不應該有邊界限定，所以應當把腳本中添加邊界約束的部分刪除，運行，生成存檔表。

接下來要對存檔表進行分區(qū)，一般來說，存檔表分成兩個區(qū)就可以應對任意分區(qū)數(shù)量的活動表了，當然，分的更多也沒有問題。存檔表的分區(qū)邊界要和活動表的對應邊界一致，也就是存檔表的第一個分區(qū)和第二個分區(qū)的邊界等于活動表的第一個分區(qū)和第二個分區(qū)的邊界。

此時存檔表中并沒有數(shù)據(jù)，我們可以用

    
      ALTER TABLE 
    
    [STable] SWITCH 
    
      PARTITION 
    
    1 
    
      TO 
    
    [DTable] 
    
      PARTITION 
    
    1
  

將活動表的第一個分區(qū)遷移到存檔表的第一個分區(qū)中。這樣活動表的第一個分區(qū)、存檔表的第二個分區(qū)為空，也就達成了前文所述的執(zhí)行Slide Window的前提條件。

自動執(zhí)行

有了存檔表，就可以進行滑動窗口了。以用時間類型字段做分區(qū)依據(jù)的表為例，這里我把執(zhí)行腳本存到一個存儲過程里：

    
      CREATE PROCEDURE 
    
    [dbo]
    
      .
    
    [sp_SlideWindow] 
    

    @SplitRange 
    
      SMALLDATETIME 
    
    
      -- 指定活動表新增分區(qū)的邊界
      

      

    
    
      AS
      

      BEGIN
      

          DECLARE 
    
    @SwitchRange 
    
      SMALLDATETIME
      

          DECLARE 
    
    @MergeRange 
    
      SMALLDATETIME
      

    
    
      --獲得活動表、存檔表合并分區(qū)以及存檔表的新分區(qū)的邊界
      

    
    
      SELECT 
    
    @MergeRange
    
      =
    
    
      CONVERT
    
    
      (
    
    
      SMALLDATETIME
    
    
      ,
    
    value
    
      ) 
    
    
      FROM 
    
    
      sys
    
    
      .
    
    
      partition_range_values
    
    
      , 
    
    
      sys
    
    
      .
    
    
      partition_functions 
    
    
      WHERE 
    
    
      sys
    
    
      .
    
    
      partition_functions
    
    
      .
    
    function_id 
    
      = 
    
    
      sys
    
    
      .
    
    
      partition_range_values
    
    
      .
    
    function_id 
    
      AND 
    
    
      sys
    
    
      .
    
    
      partition_functions
    
    
      .
    
    name 
    
      = 
    
    
      'E_Alive_Partition_Func' 
    
    
      AND 
    
    boundary_id 
    
      = 
    
    1
    

    
      SELECT 
    
    @SwitchRange
    
      =
    
    
      CONVERT
    
    
      (
    
    
      SMALLDATETIME
    
    
      ,
    
    value
    
      ) 
    
    
      FROM 
    
    
      sys
    
    
      .
    
    
      partition_range_values
    
    
      , 
    
    
      sys
    
    
      .
    
    
      partition_functions 
    
    
      WHERE 
    
    
      sys
    
    
      .
    
    
      partition_functions
    
    
      .
    
    function_id 
    
      = 
    
    
      sys
    
    
      .
    
    
      partition_range_values
    
    
      .
    
    function_id 
    
      AND 
    
    
      sys
    
    
      .
    
    
      partition_functions
    
    
      .
    
    name 
    
      = 
    
    
      'E_Alive_Partition_Func' 
    
    
      AND 
    
    boundary_id 
    
      = 
    
    2
    

    

    
      BEGIN TRANSACTION
      

          ALTER PARTITION 
    
    SCHEME [E_Alive_Partition_Schema] 
    
      NEXT 
    
    USED [PRIMARY]
    

    
      ALTER PARTITION 
    
    SCHEME [E_Staging_Partition_Schema] 
    
      NEXT 
    
    USED [PRIMARY]
    

    

    
      --在活動表中新增分區(qū)
      

    
    
      ALTER PARTITION FUNCTION 
    
    [E_Alive_Partition_Func]
    
      ()
    
    SPLIT 
    
      RANGE
    
    
      (
    
    
      CONVERT
    
    
      (
    
    
      NVARCHAR
    
    
      ,
    
    @SplitRange
    
      ,
    
    120
    
      ))
      

    
    
      --在存檔表中新增分區(qū)           
      

    
    
      ALTER PARTITION FUNCTION 
    
    [E_Staging_Partition_Func]
    
      ()
    
    SPLIT 
    
      RANGE
    
    
      (
    
    
      CONVERT
    
    
      (
    
    
      NVARCHAR
    
    
      ,
    
    @SwitchRange 
    
      ,
    
    120
    
      ))
      

    
    
      --切換分區(qū)    
      

    
    
      ALTER TABLE 
    
    [Alive_Table] SWITCH 
    
      PARTITION 
    
    2 
    
      TO 
    
    [Staging_Table] 
    
      PARTITION 
    
    2
    

    

    
      --合并活動表分區(qū)與存檔表分區(qū)
      

    
    
      ALTER PARTITION FUNCTION 
    
    [E_Alive_Partition_Func]
    
      () 
    
    
      MERGE RANGE
    
    
      (
    
    
      CONVERT
    
    
      (
    
    
      NVARCHAR
    
    
      ,
    
    @MergeRange
    
      ,
    
    120
    
      ))
      

    
    
      ALTER PARTITION FUNCTION 
    
    [E_Staging_Partition_Func]
    
      () 
    
    
      MERGE RANGE
    
    
      (
    
    
      CONVERT
    
    
      (
    
    
      NVARCHAR
    
    
      ,
    
    @MergeRange
    
      ,
    
    120
    
      ))
      

      

    
    
      COMMIT TRANSACTION
      

      END
    
  

在整個滑動窗口的操作過程中，活動表和存檔表分別合并和拆分了兩次，有四個相關(guān)邊界值。而由于活動表的合并邊界值和存檔表的合并邊界值是一樣的，所 以實際有三個邊界值。其中合并邊界就是第一個分區(qū)和第二個分區(qū)的邊界，而存檔表的拆分邊界就是活動表的第二個分區(qū)和第三個分區(qū)的邊界，這些都可以通過 sys.partition_range_values, sys.partition_functions表獲得。所以這個存儲過程 只需要輸入一個變量，即活動表新拆分出的分區(qū)的邊界 。

注意

1. 由于新拆分的分區(qū)中非聚集索引不會應用原來的壓縮方式，所以如有需要，應當在存儲過程中補充對相應索引做壓縮的操作。
2. 之所以在存儲過程中先進行分區(qū)的拆分，再進行分區(qū)切換，最后進行分區(qū)合并，是考慮對空的分區(qū)（切換前的存檔表的第二分區(qū)、切換后的活動表的一、二分區(qū)）進行拆分或者合并效率比較高。
3. 以 上只是Slide Window的一種方式，事實上，如果對歷史數(shù)據(jù)不那么在意，我們依然可以用分區(qū)切換的方式，將舊的數(shù)據(jù)移出然后刪除。或者使用多個存檔表，每次都將活動 表的最后一個分區(qū)移到新的存檔表中，這樣省去了合并存檔表分區(qū)的性能消耗，但多個存檔表可能在管理上會比較麻煩。

?

：Sql Server性能優(yōu)化——Slide Window