SQL Server 2005 數據維護實務

　　1.關於SQL Server 2005數據維護計劃

　　為了使SQL Server數據庫的性能保持在最佳的狀態，數據庫管理員應該對每一個數據庫進行定期的常規維護。這些常規任務包括重建數據庫索引、檢查數據庫完整性，更新索引統計信息，數據庫內部一致性檢查和備份等。這些常規的數據庫維護任務需要經常重復，而且繁瑣耗時，所以往往被管理員忽略。而且，現在的數據庫管理員一天到晚都被很多其他的任務壓得喘不過氣來，根本沒有時間去進行日常維護工作。認識到這些問題的存在，SQL Server通過制定維護計劃，提供了一個可以自動或手動執行這些日常維護事務的方法。當確定並創建了維護任務後，日常維護就會根據設定的時間段啟動，最終會為企業提供更優質更穩定更值得信賴的數據庫。

　　2.SQL Server Service Pack 2數據維護方面的新特性

　　SQL Server Service Pack 2有許多改進的新功能和修復設置已經能夠支持維護計劃的創建功能。其中改進的特性包括：

　　維護計劃設計器支持在一個維護計劃裡設置多個子計劃，而且每個子計劃可以具有創建獨立任務計劃書的功能。多重計劃書是備受期待的特性，能夠為不同的日常維護事務設置獨立的計劃表，例如備份、更新統計信息和執行SQL Server作業等。

　　在SQL Server 2005推出的初期，如果企業想要運行維護計劃，需要安裝SQL Server集成服務(SQL Server Integration Services，SSIS)。不過現在維護計劃已經作為一項完全支持的特性整合到了數據庫引擎中，所以不再需要啟動集成服務了。

　　支持多服務器管理環境，並把維護計劃信息記錄到遠程服務器，以適應不斷增加的管理維護計劃。可以從一台中央主服務器為所有的目標服務器設置維護計劃。

　　最早出現在SQL Server 2000備受歡迎的“清除維護任務”(Maintenance Cleanup Task)重新回到了維護計劃裡。這個任務可以刪除維護計劃執行以後任何殘留下來的文件。

　　下面列舉幾個人們預想不到的修復設置，用以改善相關的具體任務：

　　SQL Server 2005 Service Pack 2為數據庫備份維護計劃任務增加了新的備份過期選項。如果您想讓備份設置在某個特定日期之後失效，就可以通過設置備份過期選項來實現。SQL Server 2000具有這個特性，不過在SQL Server 2005發布之初被刪除了。

　　您可以另外指定備份文件夾的位置，數據庫備份維護計劃任務不會再重新設置這個選項為默認位置。

　　過去當您運行備份數據庫維護計劃任務時，系統可能會錯認為您要利用簡單恢復模式為系統數據庫創建差異和事務日志備份。現在這個缺陷已經修復了。

　　歷史清除維護計劃任務能夠將刪除文件的時間選項設置成以小時為單位，大大減少了人工操作時間。

　　更新統計信息任務提供原先在SQL Server 2000維護計劃中包含的完全掃描或根據樣本大小掃描的選項。

　　3. SQL Server維護計劃的任務

　　一個維護計劃可以在設定的時間段裡運行全套的SQL Server維護任務，以確保數據庫引擎裡的關系數據庫能夠優化運行、執行日常備份和檢查異常數據。作為SQL Server數據庫引擎的一個特性，可以自動創建數據庫維護計劃並為這些日常維護設置計劃書。一個全面的維護計劃包括一下幾個主要的任務：

　　檢查數據庫完整性

　　更新數據庫統計信息

　　重新組織數據庫索引

　　進行數據庫備份

　　清洗數據庫歷史操作數據

　　收縮數據庫

　　清除維護計劃殘留文件

　　執行SQL Server作業

　　清除維護任務

　　注意，和SQL Server 2000不同，日志傳送不再包括在維護計劃的范疇裡。可以在SQL Server Management Studio的數據庫水平上或者通過TSQL腳本設置日志傳送任務。

　　3.1 檢查數據庫完整性任務

　　檢查數據庫完整性任務(Check Database Integrity Task)檢驗選定的關系數據庫中用戶和系統表的性能和結構完整性，同時也可以選擇檢查所有索引頁的完整性，檢查對象可以是所有的系統和用戶數據庫，也可以是單個指定數據庫。通過維護計劃向導(Maintenance Plan Wizard)或使用TSQL語句能夠手動創建該任務。

　　下面的語法雖然簡單，但提供了在AdventureWorks數據庫中創建檢查數據庫完整性任務所需要的所有信息。

　　USE [AdventureWorks]

　　GO

　　DBCC CHECKDB WITH NO_INFOMSGS

　　GO

　　3.2收縮數據庫任務

　　收縮數據庫任務可以把數據庫的物理空間和日志文件所占的空間減小到特定值，類似於SSMS中使用的自動收縮任務(Automatic Shrink Task)。收縮對象可以是所有數據庫、所有系統數據庫、所有用戶數據庫或單個任務中指定的數據庫。該任務會根據您輸入的百分比值消除多余的空間。此外，還可以設定各種表示大小(MB)的阈值，包括當數據庫大小達到某特定值時的收縮量以及收縮後必須保留的可用空間大小等。可用空間可以保留在數據庫裡，也可以釋放到操作系統中。

　　以下的TSQL語法可以用來收縮AdventureWorks 數據庫，並把所釋放的空間返回操作系統，且允許在收縮後保留15%的可用空間。

　　USE [AdventureWorks]

　　GO

　　DBCC SHRINKDATABASE(N'AdventureWorks', 15, TRUNCATEONLY)

　　GO

　　但是，如果您要創建維護計劃，最好不要選擇收縮數據庫的選項。首先，數據庫收縮操作總是反向進行的，即從文件末端開始釋放空間，把分配頁移動到文件起始端的未分配頁。由於所有的轉移操作都會被記錄到日志中，所以這個過程會增加事務日志文件的大小。其次，如果數據庫的使用頻率很高就會產生插入碎片，數據庫文件又會不斷增加。SQL Server 2005啟用即時文件初始化來解決數據庫自動增長緩慢的問題，因此增長過程會比過去快。不過，有時候會出現自動增長需要的空間不足的情況，這將造成數據庫性能衰退。最後，數據庫收縮和增長過於頻繁會產生很多文件碎片。如果您想要收縮數據庫空間，最好在數據庫運行非高峰時段手動進行。

　　3.3 重新組織索引任務

　　重新組織索引任務(Reorganize Index Task)可以整理索引碎片，並壓縮與所有表和視圖相關聯的或者與特定表和視圖關聯的聚集和非聚集索引，以此來來改善索引掃描性能。受此任務影響的數據庫可以是所有的數據庫、所有系統數據庫、所有用戶數據庫或單個目標數據庫。任務設置了可以用來選擇壓縮圖像或文本等大型對象(LOB)數據的額外選項。

　　為了更深入了解這個任務，下面舉一個用來重新組織與AdventureWorks 數據庫中的[Sales]. [SalesOrderDetail]表關聯的索引的TSQL語法實例，本例中還包含了壓縮大型對象數據的選項：

　　USE [AdventureWorks]

　　GO

　　ALTER INDEX [IX_SalesOrderDetail_ProductID]

　　ON [Sales].[SalesOrderDetail]

　　REORGANIZE WITH ( LOB_COMPACTION = ON )

　　GO

　　USE [AdventureWorks]

　　GO

　　ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]

　　ON [Sales].[SalesOrderDetail]

　　REORGANIZE WITH ( LOB_COMPACTION = ON )

　　3.4 重新生成索引任務

　　重新生成索引任務(Rebuild Index Task)旨在通過重新組織數據庫中所有的表索引而清除碎片。此任務對於確保查詢性能和應用程序響應不會退化非常有用。因此，當需要對SQL執行索引掃描和查找的時候，系統運行會非常順暢。另外，此任務能夠優化數據和可用空間的再索引頁的分配，使數據庫增長更加快速。

　　對於可用空間，重新生成索引任務包含以下兩個選項：

　　采用默認可用空間大小來重新組織索引頁——刪除數據庫裡的表索引，並重新生成索引，生成索引的同時就指定填充因子(fill factor)的值。

　　改變每個索引頁的可用空間比例——刪除數據庫裡的表索引，並指定一個自動計算得到的新填充因子值來重新生成索引，因此能夠保留索引頁上指定的有用空間大小。填充因子的有效值范圍從0到100，數值越大，索引頁上保留的有用空間就越多，索引就可以增長得越大。

　　重新生成索引的高級選項包括：

　　指定是否在tempdb中存儲排序結果——這是重新生成索引的第一個高級選項，相當於索引中的SORT_IN_TEMPDB選項，如果激活這個選項，那麼中間排序結果將會在重新生成索引的過程中存儲到tempdb中。

　　指定重新生成索引操作中是否保持索引聯機——如果設置值為ON，那麼這個選項允許用戶在重新生成索引操作過程中對基礎表、聚集索引數據和相關聯的索引進行查詢和數據修改操作。

　　為了更深入了解這個任務，下面舉一個TSQL語法實例用來重新生成與AdventureWorks 數據庫中的[Sales]. [SalesOrderDetail]表關聯的索引，例子中采用默認可用空間大小選項，同時將排序結果存儲在tempdb中，並在操作過程中保持索引聯機：

　　USE [AdventureWorks]

　　GO

　　ALTER INDEX [AK_SalesOrderDetail_rowguid]

　　ON [Sales].[SalesOrderDetail]

　　REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = ON )

　　GO

　　USE [AdventureWorks]

　　GO

　　ALTER INDEX [IX_SalesOrderDetail_ProductID]

　　ON [Sales].[SalesOrderDetail]

　　REBUILD WIT