五餅二魚工作室

最近剛好臨時接到一個工作，要針對一台 SQL Server 主機來做效能調教，而該主機上面有好幾百個資料庫，存放不同租戶的資料庫。經過一兩天的觀察和測試，總算找到了問題原因，原來是幾個很特別的資料表，裡面遺漏了一些可以加速查詢的索引。而所有資料庫內有幾個資料表內有些歷史資料， 需要將這些資料移出原本的資料庫放到一個歷史存放區，這樣就可以提升存取的速度了。

既然知道問題了，那接下來就處理囉，但要如何將指令一次佈署到所有的資料庫呢 ? 這裡我們就可以借用一個存在很久的預存程序 sp_MSforeachDB 的指令，使用方式如下 :

EXEC sp_MSforeachdb '
PRINT ''?'' ;
PRINT ''------------------'';
'

 

我們可以在參數的部分加入我們所想要的指令，預設是採用 ? 取代資料庫名稱，因此透過這樣的指令執行後就可以看到您所有的資料庫名稱。而在使用的時候，在這裡有兩個要注意的地方：

1. 如果要搭配雙引號和單引號使用的時候，要注意指令長度超過 128 字的長度限制，如果沒有超過的話是沒有問題，但如果有超過的時候，則要搭配 SET QUOTED_IDENTIFIER OFF 才可以正常使用。

SET QUOTED_IDENTIFIER OFF
EXEC sp_MSforeachdb "/*                                                                                                                   */ PRINT '?'"
SET QUOTED_IDENTIFIER ON

 

2. 當使用的時候要注意，雖然透過這個 sp 可以幫你切換資料庫，但也僅止於提供給你每個資料庫的名稱，實際你的 Session  並沒有真正切換到對應的資料庫上面，這個狀況可以用下面的範例中來做測試，你就可以發現當執行的時候，透過 DB_NAME() 這個函數，取得的 Session 連接的資料庫都還是固定的。 因此如果真的要切換到該資料庫下面，要記得搭配 USE 來做使用。

EXEC sp_MSforeachdb 
  @command1="PRINT 'DB NAME :?'; " , 
  @command2="PRINT 'SESSION :'+DB_NAME();", 
  @command3="PRINT '------'";

而除了上述的 sp_MSforeachdb 之外，另外微軟還有提供另外一個預存程序 sp_MSforeachtable ，透過這兩個指令就可以很方便的來做處理了，可以簡化以往很多都會先把要處理的資料庫放到暫存資料表，然後又要用 cursor 的方式一個一個撈出來處理。

---

除了上述的狀況之外，另外我們需要將一些資料表的歷史資料移到不同的資料庫上面去，基本上看起來問題不難，只要搭配 INSERT 和 DELETE 指令就好了，但因為會需要這樣處理的資料表都是非常的大，如果要這樣處理的話，可能會在瞬間產生很大的交易紀錄檔，並且很長時間的鎖定，造成使用上的不便。因此這個時候我們利用之前另外一篇文章( 解決大量資料刪除，造成資料庫交易紀錄檔案容量過大且耗費時間之處理 ) 所介紹的方法，想要像是擠牙膏般的方式，一部分資料搬移過去，然後刪除一部分資料，但 DELETE 不能直接下 ORDER BY，因此又會擔心要是不小心刪除錯資料，那麼就麻煩了。

 

因此在這裡我們利用 DELETE 來搭配 OUTPUT 的指令 ( MSDN 說明 )，透過 OUTPUT 可以將我們所異動的資料給轉出到資料表變數或者是一個實體的資料表上面，因此在使用上我們就可以利用下面的方式來做處理：

DELETE TOP(10000) FROM ?.dbo.Table1
    OUTPUT 	deleted.Field1 ,deleted.Field2 ,deleted.Field3 
    INTO History.dbo.OLD_Table1
    WHERE Field3 < '2016-01-01'

 

在上面的範例中，我們先測試直接刪除測試資料表內小於今年的 10000 筆記錄，並且將這些資料移到一個歷史的資料庫內對應的資料表，測試沒有問題之後，我們就將這個指令，和前述的 sp_MSforeachdb 一起合併來使用，因為避免處理的時候會不小心又刪除到歷史資料表，在這裡我們故意讓歷史資料庫內的資料表和來源資料庫的不同，當然你也可以不用這樣的方式，只要能夠區別開來就好。

 

因此我們整合起來的指令如下，Orders 是我們範例的資料表，因此在下面的範例中，我們去掃所有的資料庫，如果該資料庫內有 Orders 這個資料表，那我們就一次刪除 10000 筆的紀錄，並且將刪除的紀錄移到 History 的資料庫下的 OLD_Orders 的資料表，因為會跑每個資料庫，因此移過去的時候多帶入一個資料庫名稱過去，在這個迴圈內我們會一值執行到沒有符合的資料，然後就再換一個資料庫去執行。

EXEC sp_MSforeachdb '
use [?];
DECLARE @Count int=1;
IF ( OBJECT_ID(''Orders'')>0 ) 
WHILE @Count > 0
BEGIN
    DELETE TOP(10000) FROM ?.dbo.Orders
        OUTPUT DB_NAME(), 
            deleted.BUE001,deleted.BUE002,deleted.BUE003,deleted.BUE004,deleted.BUE005,
            deleted.BUE006,deleted.BUE007,deleted.BUE008,deleted.BUE009,deleted.BUE010
        INTO History.dbo.OLD_Orders
        WHERE BUE999 < ''2016-01-01'';
    SET @Count=@@ROWCOUNT;
END	
'

 

透過上述這兩個 SQL 的特性，因此我們就可以很輕鬆的解決整批設定和資料搬移的處理，算是完成一項簡單任務了。