共用方式為

  • 發行項

SQL 問答集磁碟分割、一致性檢查及其他

Paul S. Randal

問：我不小心把實際執行資料庫附加到 SQL Server® 2005 伺服器，現在要把它附加到正確的伺服器，也就是執行 SQL Server 2000 的伺服器。我試過單純把資料庫卸離，然後再附加到 SQL Server 2000 伺服器，也試過透過備份和還原來進行相同的動作，但是兩種方法都沒有用。為什麼在我的 SQL Server 2000 伺服器就行不通呢？我就這麼一個資料庫。

答：在這裡要注意的第一件事，是了解備份的重要性。資料庫管理員 (DBA) 常常認為需要備份，是為了從損毀或其他破壞性的嚴重損壞復原。但是您碰到的是比較不明顯的嚴重損壞 — 升級時出錯 (雖然在這個案例中是意外升級)。不過，重點是無論如何一定都要保留一份最近的完整資料庫備份，以防萬一。

無論是刻意升級還是意外升級都屬於單向作業，要反轉結果比登天還難。當您在 SQL Server 各版本間進行升級時，資料庫上會進行一連串的升級步驟。每個步驟通常都牽涉到對資料庫的一些實體變更，而且每個步驟都會增加資料庫的版本號碼。

例如，當您將資料庫從 SQL Server 2000 升級到 SQL Server 2005 時，其中一項重大變更，就是對資料庫系統目錄 (常常稱為系統資料表或資料庫中繼資料，內存各種關於資料表、索引、資料行、配置的中繼資料，以及其他與關聯性和資料實際結構相關的詳細資料) 結構所做的變更。

隨著各個升級步驟的執行，資料庫版本號碼也會跟著增加。例如，SQL Server 7.0 資料庫的版本號碼是 515，SQL Server 2000 資料庫的版本號碼是 539，而 SQL Server 2005 資料庫版本號碼則是 611 (如果有啟用 Vardecimal 功能的話，則為 612)。這樣可以讓 SQL Server 知道在資料庫上所執行的最後一個升級步驟是什麼。

SQL Server 發行版無法讀取升級到較新 SQL Server 版本的資料庫 (譬如，SQL Server 2000 無法讀取升級至 SQL Server 2005 的資料庫)。這是因為較舊的版本並沒有所需的程式碼來解譯已升級的結構和資料庫配置。而這也正是您已升級至 SQL Server 2005 的資料庫，所碰到的問題癥結所在：不能附加回 SQL Server 2000。

由於沒有完整的資料庫備份，因此除了從升級的資料庫匯出所有資料，然後手動將它傳送到新的 SQL Server 2000 資料庫之外，別無他法。只要您還沒有使用 SQL Server 2005 中的任何新功能，就可以編寫資料庫結構，在 SQL Server 2000 上建立資料庫，然後匯出/匯入資料。

若要在 SQL Server 2005 中編寫資料庫，可以使用 SQL Server Management Studio (在資料庫上按右鍵，接著依序選取 [工作] 和 [產生指令碼] 選項) 中的物件總管。精靈很簡單明瞭，它會產生一個指令碼來建立所有物件、索引、條件約束、觸發程序等等。

問：我們最近剛剛將結構描述重新設計，以採用主資料表上的資料表磁碟分割 — 聽說這麼做可以提升效能。資料庫是存放在單一的 120GB 磁碟機上，而資料表是內含在單一檔案群組中。除了每週加入新的磁碟分割之外，並沒有進行任何滑動視窗 (sliding-window) 作業，而且所有資料都必須能夠在線上存取。我們大半的查詢都是在處理一週以內的資料，只有少數還在處理去年的資料。這在單一檔案群組中似乎比較容易進行，我這樣說對嗎？還是事實不止於此？

答：使用單一檔案群組看起來似乎比較簡單，但是這麼一來，使用磁碟分割可能就看不到什麼效益了。磁碟分割的主要用途是為了達成更有效率的資料庫維護，以及在發生嚴重損壞的情況下增加資料的可用性，另外您還可以建立結構描述來促進效能。

磁碟分割的典型範例是用於銷售資料表，內含 12 個磁碟分割，各自代表去年銷售資料的每一個月。每到月底，最舊的磁碟分割會被換掉 (並且封存起來或刪除掉)，然後換上新的磁碟分割，這就是您所提到的滑動視窗案例。當月的磁碟分割設為可讀寫，而較舊的月份則設為唯讀。每個磁碟分割都是存放在個別的檔案群組中。這種結構描述可達成磁碟分割的功效，但並不是最理想的做法。

內人 Kimberly 想出一個妙計，可以更有效的編列上述結構描述索引。那就是把銷售資料表分割成兩個資料表 — 一個用來存放單一可讀寫磁碟分割，另一個用來存放 11 個唯讀的磁碟分割，另外可分割檢視兩個資料表。

這可讓可讀寫資料表擁有較少量的索引，而讓唯讀資料表擁有較多量的索引來支援報告查詢。如此一來，便可讓可讀寫資料上的資料操作語言 (DML) 作業更具效率，因為它們不用維護那麼多非叢集索引。

此外，可讀寫資料上的查詢也不必處理唯讀資料。查詢計畫中的磁碟分割銷除在 SQL Server 2005 中還不甚完美，尤其是在有複雜述詞的情況下更是，但在 SQL Server 2008 中已經過改良。相關詳細資訊，請參閱 Kimberly 的部落格文章，網址是 sqlskills.com/blogs/kimberly/default,month,2007-10.aspx#a71c70243-3d57-4940-9af7-a802b73f2f93。

為了闡明我的意思，我將說明一些透過在多個檔案群組上磁碟分割所提供的功能。

部分資料庫可用性 這項功能可讓資料庫保持在線上狀態，而且可被存取，只要主要檔案群組在嚴重損壞修復的情況下還在線上就行了。如果您只有一個檔案群組，那麼整個資料庫就會在還原時停擺。若將資料分散到多個檔案群組，還原期間就只有受損的檔案群組會離線，而應用程式還是可以繼續運作。

分次還原 此種配置與部分資料庫可用性雷同。如果只有一個檔案群組時，還原的單位不是單頁，就是整個資料庫。如果有多個檔案群組時，就可以只還原一個檔案群組 — 這樣就有部分資料庫可以使用。

分割式資料庫維護 針對上述的任何一種磁碟分割案例，您可以依磁碟分割執行索引片段移除作業，即便所有磁碟分割都位在同一個檔案群組也可行。但是對於單一檔案群組，就無法進行依檔案群組的一致性檢查，而這可大幅減少資料庫一致性檢查 (DBCC) 需要處理的資料量，轉而降低所用的 CPU 和 I/O 資源量。

簡單的說，雖然您可以在相同的檔案群組內擁有多個磁碟分割，但是在磁碟分割與檔案群組之間擁有一對一對應關係也有很多優點。

問：我們有部高階資料庫伺服器最近發生了嚇人的情況 — 其中有張記憶體電路板因為運作不良而導致損毀情況。這種情況是應用程式開始出現隨機資料時發現的。我們在執行 DBCC CHECKDB 之後，發現了各種損毀情況。糟糕的是，備份也發生同樣的情形，因此我們不得不手動清除受損的資料。

長話短說 — 我們替換了受損的硬體，然後啟動頁總合檢查碼。本來想要定期執行一致性檢查，但是維護窗口的時間不夠長，而且要檢查 2.4TB 的資料庫也會花上好一段時間。該怎麼辦呢？

答：如何在 VLDB (非常大型的資料庫) 上執行一致性檢查和其他維護作業的問題，已變得越來越普遍。許多 DBA 在發現 DBCC CHECKDB 執行的時間比維護窗口允許的時間還長時，都乾脆舉白旗投降 (在有些情況下，資料庫是 24 小時全天候使用，因此沒有有效的時間可以讓出 CPU 和 I/O 供 DBCC CHECKDB 佔用一段持續的時間)。

除了放棄不執行任何一致性檢查之外 (我絕對不建議這麼做)，您還有四種方法可以採用。我個人就曾經幫客戶採用過這四種方法。

使用 DBCC CHECKDB 的 WITH PHYSICAL_ONLY 選項 標準的 DBCC CHECKDB 會執行大量的邏輯一致性檢查，而佔用相當高的 CPU (而且基本上是繫結 CPU 的作業)。使用 WITH PHYSICAL_ONLY 選項會限制檢查作業執行非常快速的 DBCC CHECKALLOC 配置點陣圖一致性檢查，然後讀取和稽核資料庫中每個配置的頁面，進而強制測試頁面上所顯示的任何頁總合檢查碼。這麼一來就會把 DBCC CHECKDB 轉換成繫結 I/O 作業，大幅減少執行的時間 (事實上，有時候甚至比完整的 DBCC CHECKDB 還要快速，換句話說，真的可省下不少時間)。

分散一致性檢查工作負載 您需要把資料庫中的資料表分割成相同大小的群組 (最簡單的方法是依頁面數量來分割)，然後每晚使用 DBCC CHECKTABLE 命令，對單一檔案群組中的所有資料表進行一致性檢查。因此，比方說，如果以每天一個群組來檢查七個群組，一週進行一次 DBCC CHECKALLOC 和 DBCC CHECKCATALOG，即使是分散在一週間進行，還是相當於完成了 DBCC CHECKDB。

使用資料表分割搭配多個檔案群組 VLDB 中最大的資料表可分割為好幾個檔案群組。讓我們舉個一致性檢查結構描述的例子，比方說，每天在存放可讀寫磁碟分割的檔案群組上執行 DBCC CHECKFILEGROUP，然後每週在存放唯讀磁碟分割的檔案群組上執行 DBCC CHECKFILEGROUP。這個做法所依據的邏輯是，唯讀資料已經完全備份，而且在日常處理作業中也不會用到。因此並不需要經常進行一致性檢查，因為這些資料的損毀狀況並不是那麼危急。

將一致性檢查的負載卸載到其他伺服器 這個選項所牽涉的步驟包括將標準完整資料庫備份還原到不同的伺服器上，然後在該伺服器上執行 DBCC CHECKDB。這麼做的確可以完全卸載實際執行伺服器的一致性檢查工作負載，只不過，如果發現有損毀狀況，就必須在實際執行伺服器上執行一致性檢查 — 但這應該不常發生。

如您所見，DBA 要在 VLDB 上執行一致性檢查其實有很多選擇，可以減少執行完整 DBCC CHECKDB 時所需的額外資源負載。我剛才在前面提到，我個人就幫過客戶採用過全部四種方法，相信這些選擇也會適用您的情況。

提示：使用觸發程序來實作伺服器端邏輯

在某些情況下，您需要使用觸發程序來實作伺服器端邏輯，但是必須特別注意一些陷阱。下面就是幾件應該銘記在心的事。

  • 觸發程序是由陳述式引發，而不是在每一個資料列引發。也就是說，如果要處理受陳述式影響的多資料列情況，或是沒有任何資料列受陳述式影響的情況 (觸發程序是在每一個陳述式引發，即使沒有任何資料列受影響也一樣)，您一定要將額外的邏輯放在觸發程序邏輯裡面。受影響的資料是放在資料操作語言 (DML) 陳述式的虛擬資料表中。您可以將這些資料表聯結起來，以便處理資料。
  • 觸發程序可在交易當中同步執行。每當您要呼叫外部應用程式或存取外部資源，但又不確定是否會在合理的時間內獲得回應時，都應該記住這一點。比方說，如果您對資料表引發一個 Update 陳述式，而在該動作內引發一個觸發程序，那麼該交易 (Update 陳述式的隱含) 會等到觸發程序的全部邏輯都完成之後才會結束。如果外部應用程式或處理序傳回錯誤碼，SQL Server 可能會取消交易，並將交易復原 (端視實作的錯誤處理和錯誤碼而定)。所以，如果您需要在觸發程序內進行外部作業，而此作業對交易來說不重要的話 (或者不需要在相同的領域內執行)，應該將它向外擴展到另一個處理序，以非同步的方式挑出資料。SQL Server 2005 引進了 SQL Server Service Broker，它可以非同步地進行這類動作。
  • 要找出觸發程序內因陳述式導致的錯誤非常困難。如果交易內涵蓋多個資料表，一定要記得在發生錯誤及實作適當錯誤處理的情況下偵測觸發程序。如果您在資料庫內變更結構描述，也別忘了追蹤觸發程序邏輯 — 否則即使是小小的觸發程序，也會大大影響整體的效能和穩定性。大多數與結構描述變更相關的隱含式都可以使用 Visual Studio® for Database Professionals，在編輯專案時進行自動結構描述檢查，以及進行靜態程式碼分析來檢查是否有資料類型不一致的情形。

─ Microsoft 的資料庫顧問 Jens K. Suessmeyer

Paul S. Randal 是 SQLskills.com 的常務董事，同時也是 SQL Server MVP。他在 1999 年 2007 年間服務於 Microsoft 的 SQL Server 儲存引擎 (SQL Server Storage Engine) 小組。Paul 為 SQL Server 2005 寫了 DBCC CHECKDB/修復，並且在 SQL Server 2008 開發期間負責核心儲存體引擎。身為嚴重損壞修復、高可用性及資料庫維護方面的專家，Paul 經常在研討會發表演說。他的部落格網址是 SQLskills.com/blogs/paul。

© 2008 Microsoft Corporation and CMP Media, LLC.著作權所有，並保留一切權利。未經許可，不得部分或全部重製。