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

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

以下是可以安排為自動(dòng)運(yùn)行的一些維護(hù)任務(wù)：

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

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

更新索引統(tǒng)計(jì)信息，確保查詢優(yōu)化器含有關(guān)于表中數(shù)據(jù)值分布的最新信息。這使得查詢優(yōu)化器能夠更好地確定 訪問數(shù)據(jù)的最佳方法，因?yàn)榭梢垣@得數(shù)據(jù)庫(kù)中存儲(chǔ)數(shù)據(jù)的詳細(xì)信息。雖然 SQL Server 會(huì)定期自動(dòng)更新索引統(tǒng) 計(jì)信息，但是此選項(xiàng)可以對(duì)統(tǒng)計(jì)信息立即進(jìn)行強(qiáng)制更新。

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

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

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

以下是詳細(xì)說(shuō)明：

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

Shrink Database(收縮數(shù)據(jù)庫(kù)任務(wù))收縮數(shù)據(jù)庫(kù)’任務(wù)”對(duì)話框可以創(chuàng)建一 個(gè)任務(wù)，嘗試減小所選數(shù)據(jù)庫(kù)的大小。此任務(wù)封裝了 DBCC SHRINKDATABASE 命令。 選項(xiàng)：Shrink database when it grows beyond當(dāng)數(shù)據(jù)庫(kù)大小超過指定值時(shí)收縮數(shù)據(jù)庫(kù)，指定引發(fā)此任務(wù)的數(shù)據(jù)庫(kù)大小(MB)。Amount of free space to remain after shrink收縮后保留的 可用空間，當(dāng)數(shù)據(jù)庫(kù)文件中的可用空間達(dá)到此值時(shí)停止收縮。Retain freed space in database files選擇在數(shù)據(jù)庫(kù)文件中保留所釋放的文件空間。如果指定 NOTRUNCATE 選項(xiàng)，數(shù)據(jù)文件好像沒有收縮。Return freed space to operating system選擇把數(shù)據(jù)文件中任何未使用空間被釋放給操作系統(tǒng)。無(wú)需移動(dò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ù)庫(kù)表和視圖中的索引。 通過使用“重新組織索引”任務(wù)，包可以重新組織單個(gè)數(shù)據(jù)庫(kù)或多個(gè)數(shù)據(jù)庫(kù)中的索引。如果此任務(wù)僅重新組織單個(gè)數(shù)據(jù)庫(kù)中的索引，則可以選擇任務(wù)要重新組織其索引的視圖或表。“重新組織索引”任務(wù)還包含壓縮大型對(duì)象數(shù)據(jù)的選項(xiàng)。大型對(duì)象數(shù)據(jù)是具有 image 、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 或 xml 數(shù)據(jù)類型的數(shù)據(jù)。此任務(wù)封裝了 Transact-SQL ALTER INDEX 語(yǔ)句。如果選擇壓縮大型對(duì)象數(shù)據(jù)，則該語(yǔ)句使用 REORGANIZE WITH(LOB_COMPACTION = ON) 子句，否則 LOB_COMPACTION 將設(shè)置為 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ù)庫(kù)表和視圖中的索引。包可 以重新生成單個(gè)數(shù)據(jù)庫(kù)或多個(gè)數(shù)據(jù)庫(kù)中的索引。如果任務(wù)僅重新生成單個(gè)數(shù)據(jù)庫(kù)中的索引，則可以選擇任務(wù)要 重新生成其索引的視圖和表。使用默認(rèn)可用空間重新組織頁(yè)刪除數(shù)據(jù)庫(kù)中表上的索引，并使用在創(chuàng)建索引時(shí)指 定的填充因子重新創(chuàng)建索引。此任務(wù)封裝 ALTER INDEX REBUILD 語(yǔ)句并提供下列索引重新生成選項(xiàng)： Reorganize pages with the default amount of free space指定 FILLFACTOR 百 分比或使用原始的 FILLFACTOR 量。Change free space per page percentage to:填充索引使用 PAD_INDEX 選項(xiàng)可以在索引創(chuàng)建過程中設(shè)置中間級(jí)頁(yè)中的可用空間百分比。將每頁(yè)的可用空間百分比更改,刪除數(shù)據(jù)庫(kù)中表上的索引，并使用新的、自動(dòng)計(jì)算的填充因子重新創(chuàng)建索引，從而在索引頁(yè)上保留指定的可用空間。Sort results in tempdb使用 SORT_IN_TEMPDB 選項(xiàng)，該選項(xiàng)確定在索引創(chuàng)建 過程中生成的中間排序結(jié)果的臨時(shí)存儲(chǔ)位置。使用索引的IGNORE_DUP_KEY 選項(xiàng)，該選項(xiàng)指定對(duì)唯一聚集或非聚集索引上多行 INSERT 事務(wù)中的重復(fù)鍵值的錯(cuò)誤響應(yīng) 。Keep index online while reindexing使用 ONLINE 選項(xiàng)，用戶可以在索引操作期間訪問基礎(chǔ)表或聚集索引數(shù)據(jù)以及任何關(guān)聯(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)計(jì)信息)為指定的表或索引視圖中的一個(gè)或多個(gè)統(tǒng)計(jì)信息組（ 集合）更新鍵值分布信息。此任務(wù)封裝 UPDATE STATISTICS 語(yǔ)句。All existing statistics如果更新應(yīng)用于所有統(tǒng)計(jì)信息，則暗示使用 WITH ALL 子句。Column statistics only如果更新僅 應(yīng)用于列，則包含 WITH COLUMN 子句。Index statistics only如果更新僅應(yīng)用于索引，則包含 WITH INDEX 子句。 Full scan全部統(tǒng)計(jì)Sample by從每個(gè)索引所對(duì)應(yīng)的表中抽樣的數(shù)據(jù)，此樣本的大小取決 于表中的行數(shù)和數(shù)據(jù)修改的頻率。 生成代碼：(只選擇了Employee表)UPDATE STATISTICS [HumanResources].[Employee]WITH FULLSCAN

Clean Up History(清除歷史記錄)使用“清除歷史記錄”對(duì)話框，可以放 棄 msdb 數(shù)據(jù)庫(kù)表中舊的歷史信息。此任務(wù)支持對(duì)備份和還原歷史記錄、Microsoft SQL Server 代理作業(yè)歷史記錄和維護(hù)計(jì)劃歷史記錄進(jìn)行刪除。此任務(wù)封裝 sp_delete_backuphistory 系統(tǒng)存儲(chǔ)過程并將指定日期作為參數(shù)傳遞給該過程。 選項(xiàng)：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è))任務(wù)運(yùn)行 SQL Server 代理作業(yè)。SQL Server 代理作業(yè)能夠自動(dòng)執(zhí)行您需要重復(fù)執(zhí)行的任務(wù)。此任務(wù)封裝 sp_start_job 系統(tǒng) 過程并把 SQL Server 代理作業(yè)的名稱作為參數(shù)傳遞給該過程。

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

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

Execute T-SQL Statement Task執(zhí)行T-SQL 任務(wù)運(yùn)行Transact-SQL 語(yǔ)句。這個(gè)任務(wù)用向?qū)У臅r(shí)候是沒有的，要到設(shè)計(jì)視圖里面去拖出來(lái)。

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

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

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

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