SQL Server 2005 數(shù)據(jù)維護(hù)實(shí)務(wù)（1）

知識藏園 2012-01-07

展開全文

為了使SQL Server數(shù)據(jù)庫的性能保持在最佳的狀態(tài)，數(shù)據(jù)庫管理員應(yīng)該對每一個(gè)數(shù)據(jù)庫進(jìn)行定期的常規(guī)維護(hù)。這些常規(guī)任務(wù)包括重建數(shù)據(jù)庫索引、檢查數(shù)據(jù)庫完整性，更新索引統(tǒng)計(jì)信息，數(shù)據(jù)庫內(nèi)部一致性檢查和備份等……

　　1.關(guān)于SQL Server 2005數(shù)據(jù)維護(hù)計(jì)劃

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

　　2.SQL Server Service Pack 2數(shù)據(jù)維護(hù)方面的新特性

　　SQL Server Service Pack 2有許多改進(jìn)的新功能和修復(fù)設(shè)置已經(jīng)能夠支持維護(hù)計(jì)劃的創(chuàng)建功能。其中改進(jìn)的特性包括：

　　維護(hù)計(jì)劃設(shè)計(jì)器支持在一個(gè)維護(hù)計(jì)劃里設(shè)置多個(gè)子計(jì)劃，而且每個(gè)子計(jì)劃可以具有創(chuàng)建獨(dú)立任務(wù)計(jì)劃書的功能。多重計(jì)劃書是備受期待的特性，能夠?yàn)椴煌娜粘＞S護(hù)事務(wù)設(shè)置獨(dú)立的計(jì)劃表，例如備份、更新統(tǒng)計(jì)信息和執(zhí)行SQL Server作業(yè)等。

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

　　支持多服務(wù)器管理環(huán)境，并把維護(hù)計(jì)劃信息記錄到遠(yuǎn)程服務(wù)器，以適應(yīng)不斷增加的管理維護(hù)計(jì)劃。可以從一臺中央主服務(wù)器為所有的目標(biāo)服務(wù)器設(shè)置維護(hù)計(jì)劃。

　　最早出現(xiàn)在SQL Server 2000備受歡迎的“清除維護(hù)任務(wù)”(Maintenance Cleanup Task)重新回到了維護(hù)計(jì)劃里。這個(gè)任務(wù)可以刪除維護(hù)計(jì)劃執(zhí)行以后任何殘留下來的文件。

　　下面列舉幾個(gè)人們預(yù)想不到的修復(fù)設(shè)置，用以改善相關(guān)的具體任務(wù)：

　　SQL Server 2005 Service Pack 2為數(shù)據(jù)庫備份維護(hù)計(jì)劃任務(wù)增加了新的備份過期選項(xiàng)。如果您想讓備份設(shè)置在某個(gè)特定日期之后失效，就可以通過設(shè)置備份過期選項(xiàng)來實(shí)現(xiàn)。SQL Server 2000具有這個(gè)特性，不過在SQL Server 2005發(fā)布之初被刪除了。

　　您可以另外指定備份文件夾的位置，數(shù)據(jù)庫備份維護(hù)計(jì)劃任務(wù)不會再重新設(shè)置這個(gè)選項(xiàng)為默認(rèn)位置。

　　過去當(dāng)您運(yùn)行備份數(shù)據(jù)庫維護(hù)計(jì)劃任務(wù)時(shí)，系統(tǒng)可能會錯認(rèn)為您要利用簡單恢復(fù)模式為系統(tǒng)數(shù)據(jù)庫創(chuàng)建差異和事務(wù)日志備份?，F(xiàn)在這個(gè)缺陷已經(jīng)修復(fù)了。

　　歷史清除維護(hù)計(jì)劃任務(wù)能夠?qū)h除文件的時(shí)間選項(xiàng)設(shè)置成以小時(shí)為單位，大大減少了人工操作時(shí)間。

　　更新統(tǒng)計(jì)信息任務(wù)提供原先在SQL Server 2000維護(hù)計(jì)劃中包含的完全掃描或根據(jù)樣本大小掃描的選項(xiàng)。

　　3. SQL Server維護(hù)計(jì)劃的任務(wù)

　　一個(gè)維護(hù)計(jì)劃可以在設(shè)定的時(shí)間段里運(yùn)行全套的SQL Server維護(hù)任務(wù)，以確保數(shù)據(jù)庫引擎里的關(guān)系數(shù)據(jù)庫能夠優(yōu)化運(yùn)行、執(zhí)行日常備份和檢查異常數(shù)據(jù)。作為SQL Server數(shù)據(jù)庫引擎的一個(gè)特性，可以自動創(chuàng)建數(shù)據(jù)庫維護(hù)計(jì)劃并為這些日常維護(hù)設(shè)置計(jì)劃書。一個(gè)全面的維護(hù)計(jì)劃包括一下幾個(gè)主要的任務(wù)：

　　檢查數(shù)據(jù)庫完整性
　　更新數(shù)據(jù)庫統(tǒng)計(jì)信息
　　重新組織數(shù)據(jù)庫索引
　　進(jìn)行數(shù)據(jù)庫備份
　　清洗數(shù)據(jù)庫歷史操作數(shù)據(jù)
　　收縮數(shù)據(jù)庫
　　清除維護(hù)計(jì)劃殘留文件
　　執(zhí)行SQL Server作業(yè)
　　清除維護(hù)任務(wù)

　　注意，和SQL Server 2000不同，日志傳送不再包括在維護(hù)計(jì)劃的范疇里?？梢栽赟QL Server Management Studio的數(shù)據(jù)庫水平上或者通過TSQL腳本設(shè)置日志傳送任務(wù)。

　　3.1 檢查數(shù)據(jù)庫完整性任務(wù)

　　檢查數(shù)據(jù)庫完整性任務(wù)(Check Database Integrity Task)檢驗(yàn)選定的關(guān)系數(shù)據(jù)庫中用戶和系統(tǒng)表的性能和結(jié)構(gòu)完整性，同時(shí)也可以選擇檢查所有索引頁的完整性，檢查對象可以是所有的系統(tǒng)和用戶數(shù)據(jù)庫，也可以是單個(gè)指定數(shù)據(jù)庫。通過維護(hù)計(jì)劃向?qū)?Maintenance Plan Wizard)或使用TSQL語句能夠手動創(chuàng)建該任務(wù)。

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

　　USE [AdventureWorks]
　　GO
　　DBCC CHECKDB WITH NO_INFOMSGS
　　GO

　　3.2收縮數(shù)據(jù)庫任務(wù)

　　收縮數(shù)據(jù)庫任務(wù)可以把數(shù)據(jù)庫的物理空間和日志文件所占的空間減小到特定值，類似于SSMS中使用的自動收縮任務(wù)(Automatic Shrink Task)。收縮對象可以是所有數(shù)據(jù)庫、所有系統(tǒng)數(shù)據(jù)庫、所有用戶數(shù)據(jù)庫或單個(gè)任務(wù)中指定的數(shù)據(jù)庫。該任務(wù)會根據(jù)您輸入的百分比值消除多余的空間。此外，還可以設(shè)定各種表示大小(MB)的閾值，包括當(dāng)數(shù)據(jù)庫大小達(dá)到某特定值時(shí)的收縮量以及收縮后必須保留的可用空間大小等?？捎每臻g可以保留在數(shù)據(jù)庫里，也可以釋放到操作系統(tǒng)中。

　　以下的TSQL語法可以用來收縮AdventureWorks 數(shù)據(jù)庫，并把所釋放的空間返回操作系統(tǒng)，且允許在收縮后保留15%的可用空間。

　　USE [AdventureWorks]
　　GO
　　DBCC SHRINKDATABASE(N'AdventureWorks', 15, TRUNCATEONLY)
　　GO

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

　　3.3 重新組織索引任務(wù)

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

　　為了更深入了解這個(gè)任務(wù)，下面舉一個(gè)用來重新組織與AdventureWorks 數(shù)據(jù)庫中的[Sales]. [SalesOrderDetail]表關(guān)聯(lián)的索引的TSQL語法實(shí)例，本例中還包含了壓縮大型對象數(shù)據(jù)的選項(xiàng)：

　　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 重新生成索引任務(wù)

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

　　對于可用空間，重新生成索引任務(wù)包含以下兩個(gè)選項(xiàng)：

　　采用默認(rèn)可用空間大小來重新組織索引頁——刪除數(shù)據(jù)庫里的表索引，并重新生成索引，生成索引的同時(shí)就指定填充因子(fill factor)的值。

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

　　重新生成索引的高級選項(xiàng)包括：

　　指定是否在tempdb中存儲排序結(jié)果——這是重新生成索引的第一個(gè)高級選項(xiàng)，相當(dāng)于索引中的SORT_IN_TEMPDB選項(xiàng)，如果激活這個(gè)選項(xiàng)，那么中間排序結(jié)果將會在重新生成索引的過程中存儲到tempdb中。

　　指定重新生成索引操作中是否保持索引聯(lián)機(jī)——如果設(shè)置值為ON，那么這個(gè)選項(xiàng)允許用戶在重新生成索引操作過程中對基礎(chǔ)表、聚集索引數(shù)據(jù)和相關(guān)聯(lián)的索引進(jìn)行查詢和數(shù)據(jù)修改操作。

　　為了更深入了解這個(gè)任務(wù)，下面舉一個(gè)TSQL語法實(shí)例用來重新生成與AdventureWorks 數(shù)據(jù)庫中的[Sales]. [SalesOrderDetail]表關(guān)聯(lián)的索引，例子中采用默認(rèn)可用空間大小選項(xiàng)，同時(shí)將排序結(jié)果存儲在tempdb中，并在操作過程中保持索引聯(lián)機(jī)：

　　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 WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = ON )
　　GO
　　USE [AdventureWorks]
　　GO
　　ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
　　ON [Sales].[SalesOrderDetail]
　　REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = ON )

　　3.5 更新統(tǒng)計(jì)信息任務(wù)

　　更新統(tǒng)計(jì)信息任務(wù)(pdate Statistics Task)通過對用戶表創(chuàng)建的每個(gè)索引統(tǒng)計(jì)信息分布進(jìn)行重新抽樣，以確保在一個(gè)或多個(gè)SQL Server數(shù)據(jù)庫內(nèi)表和索引中的數(shù)據(jù)都是最新的。

　　此任務(wù)的選項(xiàng)有很多，下面為您一一介紹：

　　數(shù)據(jù)庫——首先選擇受此任務(wù)影響的數(shù)據(jù)庫。這個(gè)選項(xiàng)范圍包括所有數(shù)據(jù)庫、所有系統(tǒng)數(shù)據(jù)庫、所有用戶數(shù)據(jù)庫或指定數(shù)據(jù)庫。

　　對象——選擇完數(shù)據(jù)庫后，就該在對象框中選擇限定顯示表、顯示視圖還是兩者同時(shí)顯示。

　　選擇——選擇受此任務(wù)影響的表或索引。如果在對象框中選擇了同時(shí)顯示表和視圖選項(xiàng)的話，此選項(xiàng)不可用。

　　更新——“更新”框提供了三個(gè)選項(xiàng)。如果需要更新列和索引的統(tǒng)計(jì)信息那就選擇全部現(xiàn)有統(tǒng)計(jì)信息，如果只需要更新列統(tǒng)計(jì)信息那就選擇僅限列統(tǒng)計(jì)信息，如果只更新索引統(tǒng)計(jì)信息那就選擇僅限索引統(tǒng)計(jì)信息。

　　掃描類型——此選項(xiàng)使用戶可以對收集已更新統(tǒng)計(jì)信息進(jìn)行完全掃描或通過在抽樣選項(xiàng)鍵入特定值進(jìn)行掃描。抽樣選項(xiàng)的值可以是要抽樣的表或索引視圖的百分比，也可以是指定的行數(shù)。

　　下面是用來更新AdventureWorks 數(shù)據(jù)庫中的[Sales]. [SalesOrderDetail]表的索引統(tǒng)計(jì)信息的TSQL語法，例子中選擇更新全部現(xiàn)有信息，并執(zhí)行完全掃描：

　　use [AdventureWorks]
　　GO
　　UPDATE STATISTICS [Sales].[SalesOrderDetail]
　　WITH FULLSCAN

　　3.6 清除歷史記錄任務(wù)

　　清除歷史記錄任務(wù)(History Cleanup Task)用幾個(gè)簡單的步驟就可以完全清除數(shù)據(jù)庫表中舊的歷史信息。任務(wù)支持刪除多種類型的數(shù)據(jù)。下面介紹與此任務(wù)相關(guān)的幾個(gè)選項(xiàng)：

　　即將刪除的歷史數(shù)據(jù)——使用維護(hù)計(jì)劃向?qū)砬宄齻浞莺瓦€原歷史記錄，SQL Server代理作業(yè)歷史記錄和維護(hù)計(jì)劃歷史記錄。

　　移除歷史數(shù)據(jù)，如果其保留時(shí)間超過——同樣是通過維護(hù)計(jì)劃向?qū)?shí)現(xiàn)，用于指定需要刪除的數(shù)據(jù)所保留的最早日期。例如您可以選擇以天數(shù)、周數(shù)、月數(shù)或年數(shù)為單位作為間隔周期來刪除舊數(shù)據(jù)，系統(tǒng)將自動將該間隔單位轉(zhuǎn)換為日期。

　　當(dāng)清除歷史記錄任務(wù)完成后，點(diǎn)擊“下一步”，調(diào)用“選擇報(bào)告選項(xiàng)”界面，激活檢查框中的將報(bào)告寫入文本文檔選項(xiàng)，然后選擇保存路徑就可以選擇將結(jié)果報(bào)告保存到一個(gè)文本文檔或用電子郵件發(fā)送這份報(bào)告給操作人員。

　　下面的TSQL實(shí)例顯示如何清除保留了超過四星期的備份和還原歷史、SQL Server代理作業(yè)歷史以及維護(hù)計(jì)劃歷史等數(shù)據(jù)：

　　declare @dt datetime select @dt = cast(N'2007-10-21T09:26:24' as datetime)
　　exec msdb.dbo.sp_delete_backuphistory @dt
　　GO
　　EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2007-10-21T09:26:24'
　　GO
　　EXECUTE msdb..sp_maintplan_delete_log null, null,'2007-10-21T09:26:24'

　　3.7 執(zhí)行SQL Server代理作業(yè)任務(wù)

　　執(zhí)行SQL Server代理作業(yè)任務(wù)(Execute SQL Server Agent Job task)可以讓您把運(yùn)行已有的SQL Server代理作業(yè)和SSIS程序包作為維護(hù)計(jì)劃的一部分。通過在“定義執(zhí)行SQL Server代理作業(yè)任務(wù)”界面的可用SQL Server代理作業(yè)選項(xiàng)卡選擇完成這項(xiàng)任務(wù)。同樣，也可以通過TSQL語法來通過輸入與已有的作業(yè)相應(yīng)的作業(yè)ID來執(zhí)行這項(xiàng)任務(wù)。

　　執(zhí)行此任務(wù)的語法如下：

　　EXEC msdb.dbo.sp_start_job @job_id=N'35eca119-28a6-4a29-994b-0680ce73f1f3'

本站是提供個(gè)人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自：知識藏園 > 《SQL sever》

舉報(bào)/認(rèn)領(lǐng)