這個星期開始為了減輕工作壓力開始使用數(shù)據(jù)庫維護計劃(SQL Server Maintenance Plan Wizard)維護數(shù)據(jù)庫，由于以前都沒用過，在個人使用的免費版(Express)里也沒有這個功能，所以現(xiàn)在好好學習了一番，這里總結一下。

維護計劃向導可以用于幫助您設置核心維護任務，從而確保數(shù)據(jù)庫執(zhí)行良好，做到定期備份數(shù)據(jù)庫以防系統(tǒng)出現(xiàn)故障，對數(shù)據(jù)庫實施不一致性檢查。維護計劃向導可創(chuàng)建一個或多個 SQL Server 代理作業(yè)，代理作業(yè)將按照計劃的間隔自動執(zhí)行這些維護任務。它使您可以執(zhí)行各種數(shù)據(jù)庫管理任務，包括備份、運行數(shù)據(jù)庫完整性檢查、或以指定的間隔更新數(shù)據(jù)庫統(tǒng)計信息。創(chuàng)建數(shù)據(jù)庫維護計劃可以讓SQL Server有效地自動維護數(shù)據(jù)庫，保持數(shù)據(jù)庫運行在最佳狀態(tài)，并為管理員節(jié)省了寶貴的時間。

以下是可以安排為自動運行的一些維護任務：

用新填充因子重新生成索引來重新組織數(shù)據(jù)和索引頁上的數(shù)據(jù)。這確保了數(shù)據(jù)庫頁中包含的數(shù)據(jù)量和可用空間的平均分布，還使得以后能夠更快地增長。

通過刪除空數(shù)據(jù)庫頁壓縮數(shù)據(jù)文件。

更新索引統(tǒng)計信息，確保查詢優(yōu)化器含有關于表中數(shù)據(jù)值分布的最新信息。這使得查詢優(yōu)化器能夠更好地確定 訪問數(shù)據(jù)的最佳方法，因為可以獲得數(shù)據(jù)庫中存儲數(shù)據(jù)的詳細信息。雖然 SQL Server 會定期自動更新索引統(tǒng) 計信息，但是此選項可以對統(tǒng)計信息立即進行強制更新。

對數(shù)據(jù)庫內的數(shù)據(jù)和數(shù)據(jù)頁執(zhí)行內部一致性檢查，確保系統(tǒng)或軟件故障沒有損壞數(shù)據(jù)。

備份數(shù)據(jù)庫和事務日志文件。數(shù)據(jù)庫和日志備份可以保留一段指定時間。這使您可以為備份創(chuàng)建一份歷史記錄 ，以便在需要將數(shù)據(jù)庫還原到早于上一次數(shù)據(jù)庫備份的時間的時候使用。還可以執(zhí)行差異備份。

運行 SQL Server 代理作業(yè)。這可以用來創(chuàng)建可執(zhí)行各種操作的作業(yè)以及運行這些作業(yè)的維護計劃。 維護任務生成的結果可以作為報表寫入文本文件，或寫入 msdb 中的 sysmaintplan_log 和 sysmaintplan_log_detail 維護計劃表。若要在日志文件查看器中查看結果，請右鍵單擊“維護計劃”，再單 擊“查看歷史記錄”。

以下是詳細說明：

Check Database Integrity(檢查數(shù)據(jù)庫完整性)任務檢查指定數(shù)據(jù)庫中所有對象 的分配和結構完整性。此任務可以檢查單個數(shù)據(jù)庫或多個數(shù)據(jù)庫，您還可以選擇是否也檢查數(shù)據(jù)庫索引,檢查所有索引頁以及表數(shù)據(jù)頁的完整性。此任務封裝 DBCC CHECKDB 語句 。 生成的代碼：--檢查當前數(shù)據(jù)庫，取消信息性消息DBCC CHECKDB WITH NO_INFOMSGS

Shrink Database(收縮數(shù)據(jù)庫任務)收縮數(shù)據(jù)庫’任務”對話框可以創(chuàng)建一 個任務，嘗試減小所選數(shù)據(jù)庫的大小。此任務封裝了 DBCC SHRINKDATABASE 命令。 選項：Shrink database when it grows beyond當數(shù)據(jù)庫大小超過指定值時收縮數(shù)據(jù)庫，指定引發(fā)此任務的數(shù)據(jù)庫大小(MB)。Amount of free space to remain after shrink收縮后保留的 可用空間，當數(shù)據(jù)庫文件中的可用空間達到此值時停止收縮。Retain freed space in database files選擇在數(shù)據(jù)庫文件中保留所釋放的文件空間。如果指定 NOTRUNCATE 選項，數(shù)據(jù)文件好像沒有收縮。Return freed space to operating system選擇把數(shù)據(jù)文件中任何未使用空間被釋放給操作系統(tǒng)。無需移動任何數(shù)據(jù)即可減小文件大小。

生成的代碼：--選擇Retain freed space in database filesDBCC SHRINKDATABASE (N'AdventureWorks', 10, NOTRUNCATE)--選擇Return freed space to operating systemDBCC SHRINKDATABASE(N'AdventureWorks', 10, TRUNCATEONLY)

Reorganize Index(重新組織索引)重新組織 SQL Server 數(shù)據(jù)庫表和視圖中的索引。 通過使用“重新組織索引”任務，包可以重新組織單個數(shù)據(jù)庫或多個數(shù)據(jù)庫中的索引。如果此任務僅重新組織單個數(shù)據(jù)庫中的索引，則可以選擇任務要重新組織其索引的視圖或表。“重新組織索引”任務還包含壓縮大型對象數(shù)據(jù)的選項。大型對象數(shù)據(jù)是具有 image 、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 或 xml 數(shù)據(jù)類型的數(shù)據(jù)。此任務封裝了 Transact-SQL ALTER INDEX 語句。如果選擇壓縮大型對象數(shù)據(jù)，則該語句使用 REORGANIZE WITH(LOB_COMPACTION = ON) 子句，否則 LOB_COMPACTION 將設置為 OFF。 生成代碼：(只選擇了Employee表)--選擇compact large objectsALTER INDEX [PK_Employee_EmployeeID] ON [HumanResources].[Employee] REORGANIZE WITH ( LOB_COMPACTION = ON )--不選擇ALTER INDEX [PK_Employee_EmployeeID] ON [HumanResources].[Employee] REORGANIZE WITH ( LOB_COMPACTION = OFF )

Rebuild Index(重新生成索引)重新生成 SQL Server 數(shù)據(jù)庫表和視圖中的索引。包可 以重新生成單個數(shù)據(jù)庫或多個數(shù)據(jù)庫中的索引。如果任務僅重新生成單個數(shù)據(jù)庫中的索引，則可以選擇任務要 重新生成其索引的視圖和表。使用默認可用空間重新組織頁刪除數(shù)據(jù)庫中表上的索引，并使用在創(chuàng)建索引時指 定的填充因子重新創(chuàng)建索引。此任務封裝 ALTER INDEX REBUILD 語句并提供下列索引重新生成選項： Reorganize pages with the default amount of free space指定 FILLFACTOR 百 分比或使用原始的 FILLFACTOR 量。Change free space per page percentage to:填充索引使用 PAD_INDEX 選項可以在索引創(chuàng)建過程中設置中間級頁中的可用空間百分比。將每頁的可用空間百分比更改,刪除數(shù)據(jù)庫中表上的索引，并使用新的、自動計算的填充因子重新創(chuàng)建索引，從而在索引頁上保留指定的可用空間。Sort results in tempdb使用 SORT_IN_TEMPDB 選項，該選項確定在索引創(chuàng)建 過程中生成的中間排序結果的臨時存儲位置。使用索引的IGNORE_DUP_KEY 選項，該選項指定對唯一聚集或非聚集索引上多行 INSERT 事務中的重復鍵值的錯誤響應 。Keep index online while reindexing使用 ONLINE 選項，用戶可以在索引操作期間訪問基礎表或聚集索引數(shù)據(jù)以及任何關聯(lián)的非聚集索引。 生成代碼：(只選擇了Employee表)ALTER INDEX [PK_Employee_EmployeeID] ON [HumanResources]. [Employee] REBUILD WITH ( FILLFACTOR = 90, PAD_INDEX; = OFF, STATISTICS_NORECOMPUTE; = OFF, ALLOW_ROW_LOCKS; = ON, ALLOW_PAGE_LOCKS; = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )

Updata Statics(更新統(tǒng)計信息)為指定的表或索引視圖中的一個或多個統(tǒng)計信息組（ 集合）更新鍵值分布信息。此任務封裝 UPDATE STATISTICS 語句。All existing statistics如果更新應用于所有統(tǒng)計信息，則暗示使用 WITH ALL 子句。Column statistics only如果更新僅 應用于列，則包含 WITH COLUMN 子句。Index statistics only如果更新僅應用于索引，則包含 WITH INDEX 子句。 Full scan全部統(tǒng)計Sample by從每個索引所對應的表中抽樣的數(shù)據(jù)，此樣本的大小取決 于表中的行數(shù)和數(shù)據(jù)修改的頻率。 生成代碼：(只選擇了Employee表)UPDATE STATISTICS [HumanResources].[Employee]WITH FULLSCAN

Clean Up History(清除歷史記錄)使用“清除歷史記錄”對話框，可以放 棄 msdb 數(shù)據(jù)庫表中舊的歷史信息。此任務支持對備份和還原歷史記錄、Microsoft SQL Server 代理作業(yè)歷史記錄和維護計劃歷史記錄進行刪除。此任務封裝 sp_delete_backuphistory 系統(tǒng)存儲過程并將指定日期作為參數(shù)傳遞給該過程。 選項：Backup and restore historySql Server Agent job historyMaintenance plan history

生成代碼：declare @dt datetime select @dt = cast(N'2007-05-31T08:00:00' as datetime)exec msdb.dbo.sp_delete_backuphistory @dtGOEXEC msdb.dbo.sp_purge_jobhistory; @oldest_date=@dtGOEXECUTE msdb..sp_maintplan_delete_log null,null,@dtGO

Execute Sql Server Agent Job(執(zhí)行 SQL Server 代理作業(yè))任務運行 SQL Server 代理作業(yè)。SQL Server 代理作業(yè)能夠自動執(zhí)行您需要重復執(zhí)行的任務。此任務封裝 sp_start_job 系統(tǒng) 過程并把 SQL Server 代理作業(yè)的名稱作為參數(shù)傳遞給該過程。

Back Up Database Task備份用的，太熟悉了，不介紹了。

Maintenance Cleanup Task此任務封裝 master.dbo.xp_delete_file 系統(tǒng)過程，用來刪除備份文件。

Execute T-SQL Statement Task執(zhí)行T-SQL 任務運行Transact-SQL 語句。這個任務用向導的時候是沒有的，要到設計視圖里面去拖出來。

Notify Operator Task通知操作員任務將通知消息發(fā)送到 SQL Server 代理操作員。此任務是唯一一個不封裝 Transact-SQL 語句或 DBCC 命令的數(shù)據(jù)庫維護任務。

執(zhí)行維護計劃最好按一定的順序，首先是執(zhí)行檢查數(shù)據(jù)庫完整性，然后是收縮數(shù)據(jù)庫，重新生成索引或者重新組織索引任務，最后是更新統(tǒng)計信息。

重新生成索引或者重新組織索引要根據(jù)情況選擇不同的操作，兩個一起選擇沒有什么意義。決定使用哪種碎片整理方法的第一步是分析索引以確定碎片程度。使用系統(tǒng)函數(shù) sys.dm_db_index_physical_stats 可以檢測特定索引、表或索引視圖的所有索引、一個數(shù)據(jù)庫中的所有索引或所有數(shù)據(jù)庫中的所有索引中的碎片。知道碎片程度后，可以確定修復碎片的最佳方法。索引碎片不太多時，可以重新組織索引。不過，如果索引碎片非常多，重新生成索引則可以獲得更好的結果。

我們公司這些任務都是一個星期運行一次，幾個數(shù)據(jù)庫加起來有200G，數(shù)據(jù)庫也不算很大，每次運行要兩個小時以上，所以都是在凌晨進行。如果進行的是重新生成索引那么在執(zhí)行的時候表是無法訪問的，現(xiàn)在也沒什么更好的解決方案。這個問題還在繼續(xù)學習中！