SQL Server保駕護(hù)航的正確維護(hù)4步驟

作者：佚名 2010-07-16 09:24:19

我們今天主要向大家講述的是正確維護(hù)SQL Server保駕護(hù)航的實(shí)際操作步驟，我們主要是將其分成四部分進(jìn)行詳細(xì)說(shuō)明。

此文章主要向大家講述的是正確維護(hù)SQL Server保駕護(hù)航的實(shí)際操作步驟，我前兩天在相關(guān)網(wǎng)站看見(jiàn)正確維護(hù)SQL Server保駕護(hù)航的實(shí)際操作步驟的資料，覺(jué)得挺好，就拿出來(lái)供大家分享。

SQL S

每個(gè)新發(fā)布的SQL Server都具有越來(lái)越多的自我維護(hù)SQL Server保駕護(hù)航能力，但是“安全比抱歉好得多”的原則仍然是正確的:實(shí)際的數(shù)據(jù)庫(kù)管理包含了對(duì)系統(tǒng)和用戶(hù)數(shù)據(jù)庫(kù)進(jìn)行的周期性維護(hù)，這樣才能在你的用戶(hù)發(fā)現(xiàn)問(wèn)題之前把它解決掉。

SQL Server 2000中包含了數(shù)據(jù)庫(kù)維護(hù)計(jì)劃向?qū)В梢詾槟阕詣?dòng)完成所有的維護(hù)任務(wù)。然而，你需要了解，在現(xiàn)場(chǎng)和你的維護(hù)計(jì)劃后面，什么是隱藏的可能給你帶來(lái)麻煩的東西。

數(shù)據(jù)庫(kù)維護(hù)計(jì)劃是通過(guò)一系列的DBCC命令和系統(tǒng)存儲(chǔ)過(guò)程實(shí)現(xiàn)的。DBCC的意思是數(shù)據(jù)庫(kù)一致性檢測(cè)(database consistency check)或者數(shù)據(jù)庫(kù)控制臺(tái)命令(database console command)。有非常多的寫(xiě)入文檔的和沒(méi)有寫(xiě)入文檔的DBCC命令，但是只有一小部分可以用于維護(hù)SQL Server保駕護(hù)航。這里我將回顧一些與數(shù)據(jù)庫(kù)維護(hù)SQL Server保駕護(hù)航的各個(gè)方面相關(guān)的DBCC命令和系統(tǒng)存儲(chǔ)過(guò)程。

通常的數(shù)據(jù)庫(kù)維護(hù)時(shí)間表都具有如下的活動(dòng)。點(diǎn)擊活動(dòng)，可以獲得與此相關(guān)的DBCC命令和系統(tǒng)存儲(chǔ)過(guò)程。

1、檢測(cè)數(shù)據(jù)庫(kù)一致性和數(shù)據(jù)的完整性

DBCC CHECKDB是最廣泛使用的，檢測(cè)數(shù)據(jù)庫(kù)中所有對(duì)象的工具。這個(gè)語(yǔ)句可以為每一個(gè)表和索引視圖，以及文本和圖像對(duì)象，檢測(cè)所有的數(shù)據(jù)和索引頁(yè)面的分配和結(jié)構(gòu)上的完整性。DBCC CHECKDB保證了所有的數(shù)據(jù)和索引頁(yè)面都正確鏈接，并且指針都是一致的。在指定的數(shù)據(jù)庫(kù)中，用戶(hù)和系統(tǒng)表都會(huì)被檢測(cè)到。DBCC CHECKDB對(duì)數(shù)據(jù)庫(kù)中每個(gè)對(duì)象都都執(zhí)行DBCC CHECKALLOC和 DBCC CHECKTABLE語(yǔ)句，所以如果你使用DBCC CHECKDB，你就不需要再執(zhí)行DBCC CHECKALLOC和DBCC CHECKTABLE了。

DBCC CHECKDB的某些選項(xiàng)(REPAIR_FAST, REPAIR_REBUILD 和 REPAIR_ALLOW_DATA_LOSS)需要數(shù)據(jù)庫(kù)在單用戶(hù)的模式下運(yùn)行。如果不是單用戶(hù)模式的化，語(yǔ)句就失敗了。注意，當(dāng)用戶(hù)連接在上面的時(shí)候，不要將數(shù)據(jù)庫(kù)設(shè)置成單用戶(hù)模式。

如果你執(zhí)行不帶參數(shù)的DBCC CHECKDB并且發(fā)現(xiàn)錯(cuò)誤的時(shí)候，你應(yīng)該將你的數(shù)據(jù)庫(kù)設(shè)置在單用戶(hù)模式，然后嘗試修復(fù)這個(gè)問(wèn)題。首先，確保執(zhí)行那些不會(huì)導(dǎo)致數(shù)據(jù)丟失的語(yǔ)句——REPAIR_FAST 和 REPAIR_REBUILD。如果你還是遇到錯(cuò)誤，那么執(zhí)行DBCC CHECKDB，帶著參數(shù)REPAIR_ALLOW_DATA_LOSS。還要確保在顯性事務(wù)中關(guān)閉語(yǔ)句。如果發(fā)生了可接受的數(shù)據(jù)丟失，你可以提交這個(gè)事務(wù)。否則，你還可以通過(guò)語(yǔ)句來(lái)回滾所作的修改。

請(qǐng)注意，運(yùn)行DBCC CHECKDB是一項(xiàng)非常消耗資源的操作。你應(yīng)該在限制用戶(hù)在數(shù)據(jù)庫(kù)服務(wù)器上的活動(dòng)的時(shí)候運(yùn)行這個(gè)語(yǔ)句。

DBCC CHECKTABLE與DBCC CHECKDB相同，除了它是在一個(gè)單個(gè)的表、索引視圖或者即使是一個(gè)索引上，而不是在整個(gè)數(shù)據(jù)庫(kù)上。

DBCC CHECKALLOC檢測(cè)某個(gè)數(shù)據(jù)庫(kù)的磁盤(pán)空間分配結(jié)構(gòu)的一致性。因?yàn)镈BCC CHECKDB包括了與DBCC CHECKALLOC同樣的檢測(cè)，那么如果執(zhí)行了CHECKDB的話就沒(méi)有必要再執(zhí)行DBCC CHECKALLOC了。實(shí)際上，我們推薦只使用DBCC CHECKALLOC，如果用DBCC CHECKDB或者 DBCC CHECKTABLE會(huì)報(bào)告說(shuō)產(chǎn)生分配錯(cuò)誤的話。

DBCC CHECKCONSTRAINTS在某個(gè)數(shù)據(jù)庫(kù)中，檢測(cè)某些特定的約束或者全部約束的一致性。DBCC CHECKCONSTRAINTS總是在當(dāng)前數(shù)據(jù)庫(kù)的上下文環(huán)境中執(zhí)行。

注意，DBCC CHECKCONSTRAINTS并不進(jìn)行磁盤(pán)或者文件級(jí)別的一致性檢測(cè);它只是確保外鍵定義的一致性，同時(shí)檢測(cè)約束——僅僅是確認(rèn)數(shù)據(jù)有效。如果你希望檢測(cè)磁盤(pán)上表和索引的一致性，你應(yīng)該執(zhí)行DBCC CHECKDB或者在所有的表上執(zhí)行DBCC CHECKALLOC和 DBCC CHECKTABLE的組合。

首先，為什么會(huì)發(fā)生約束違規(guī)?當(dāng)數(shù)據(jù)庫(kù)創(chuàng)建的時(shí)候，外鍵和一致性檢測(cè)也許并不存在。開(kāi)發(fā)人員和數(shù)據(jù)庫(kù)管理員也許使用了WITH NOCHECK選項(xiàng)來(lái)創(chuàng)建約束，這個(gè)選項(xiàng)只能防止約束違規(guī)的進(jìn)一步擴(kuò)展，而不是會(huì)檢測(cè)已經(jīng)存在的數(shù)據(jù)。更進(jìn)一步的說(shuō)，通過(guò)外鍵鏈接的數(shù)據(jù)可能會(huì)過(guò)期并且從母表中刪除，但是仍然會(huì)留在相關(guān)的表中，因?yàn)樗谙录?jí)表中仍然具有相關(guān)記錄。

DBCC CHECKCATALOG在某個(gè)數(shù)據(jù)庫(kù)的系統(tǒng)表內(nèi)或者之間檢測(cè)一致性。很多類(lèi)似DBCC CHECKCONSTRAINTS的命令都不會(huì)檢測(cè)頁(yè)面分配的一致性;它只是檢測(cè)系統(tǒng)表中的數(shù)據(jù)。DBCC CHECKCATALOG報(bào)告錯(cuò)誤意味著有些人手工從系統(tǒng)表中添加、修改或者刪除記錄了。如果你沒(méi)有注意此類(lèi)活動(dòng)，那么你應(yīng)該看緊你的安全措施了——看看誰(shuí)具有系統(tǒng)管理員和數(shù)據(jù)庫(kù)主任的全縣，然后評(píng)估你的安全策略。

2、重建索引

當(dāng)數(shù)據(jù)行從表中INSERTED, UPDATED and DELETED的時(shí)候，索引就產(chǎn)生了碎片。碎片越多，索引的效率越低。數(shù)據(jù)庫(kù)管理員必需確保碎片的級(jí)別很低或者根本不存在。碎片級(jí)別可以通過(guò)在某個(gè)索引上執(zhí)行DBCC SHOWCONTIG語(yǔ)句來(lái)找到。

這里有三種方法可以去掉碎片:

1、使用CREATE INDEX……WITH DROP EXISTING語(yǔ)句來(lái)刪除并重新創(chuàng)建索引

2、執(zhí)行DBCC DBREINDEX

3、執(zhí)行DBCC INDEXDEFRAG

DBCC DBREINDEX重新構(gòu)建一個(gè)特定的索引或者某個(gè)特定的表上的所有的索引。這個(gè)語(yǔ)句允許強(qiáng)制PRIMARY KEY和UNIQUE約束的索引重新構(gòu)建，而不需要?jiǎng)h除約束。你不需要知道索引的類(lèi)別和名稱(chēng)，你同樣可以使用。使用DBCC DBREINDEX比單獨(dú)為表上的每個(gè)索引編寫(xiě)DROP INDEX 和CREATE INDEX語(yǔ)句簡(jiǎn)單。時(shí)刻記住，重新構(gòu)建聚簇索引也會(huì)引起非聚簇索引的重建。

DBCC INDEXDEFRAG可以刪除某個(gè)聚簇索引或者非聚簇索引的碎片。與DBCC DBREINDEX不同，這個(gè)語(yǔ)句需要指定某個(gè)特別的索引，并且不能運(yùn)行在表上所有索引上。刪除碎片也是一項(xiàng)在線操作，因此不會(huì)妨礙用戶(hù)對(duì)表進(jìn)行操作。DBCC INDEXDEFRAG給系統(tǒng)增加了額外的負(fù)擔(dān)，因?yàn)樗a(chǎn)生了額外的I/O負(fù)擔(dān)。它還會(huì)影響到索引頁(yè)面，并且會(huì)在壓縮之后刪除所有遺留的沒(méi)有數(shù)據(jù)的頁(yè)面。

刪除碎片的頻率依賴(lài)于在你的數(shù)據(jù)庫(kù)中，數(shù)據(jù)修改的級(jí)別。需要每天處理幾百萬(wàn)個(gè)事務(wù)的系統(tǒng)應(yīng)該至少每個(gè)星期都進(jìn)行一次索引重建。另一方面，在幾乎沒(méi)有修改的數(shù)據(jù)庫(kù)上，即使你每個(gè)月進(jìn)行一次索引重建，數(shù)據(jù)庫(kù)都會(huì)運(yùn)行得不錯(cuò)。

3、更新統(tǒng)計(jì)數(shù)字

統(tǒng)計(jì)數(shù)據(jù)中包含了表中某個(gè)索引或者字段的數(shù)值分布的信息。你可以通過(guò)使用CREATE STATISTICS語(yǔ)句或者使用sp_createstatistics系統(tǒng)過(guò)程來(lái)創(chuàng)建統(tǒng)計(jì)數(shù)字。統(tǒng)計(jì)數(shù)字檢索有關(guān)索引是否具有良好或者糟糕的選擇性的信息，索引的選擇性可以用來(lái)判斷索引的效率是否足以滿足查詢(xún)的要求。當(dāng)你創(chuàng)建索引的時(shí)候，SQL Serve保駕護(hù)航r自動(dòng)創(chuàng)建統(tǒng)計(jì)數(shù)字。此外，SQL Server還為那些沒(méi)有定義索引的字段創(chuàng)建統(tǒng)計(jì)數(shù)字。

針對(duì)某個(gè)索引的統(tǒng)計(jì)數(shù)字可以通過(guò)使用DBCC SHOW_STATISTIC0S語(yǔ)句來(lái)查看。當(dāng)數(shù)據(jù)發(fā)生改變的時(shí)候，統(tǒng)計(jì)數(shù)字就過(guò)時(shí)了。當(dāng)選擇索引來(lái)滿足查詢(xún)的時(shí)候，陳舊的統(tǒng)計(jì)數(shù)字會(huì)讓SQL Server作出次優(yōu)化的決定。

默認(rèn)情況下，SQL Server 2000自動(dòng)更新每個(gè)表上的統(tǒng)計(jì)數(shù)字。然而，在某些情況下，關(guān)閉統(tǒng)計(jì)數(shù)字的自動(dòng)更新是有意義的。例如，我們假設(shè)你有某種類(lèi)型的批處理例程，每個(gè)周末，系統(tǒng)具有最小利用率的時(shí)候，都會(huì)向你的表中添加上百萬(wàn)行數(shù)據(jù)。統(tǒng)計(jì)數(shù)字的自動(dòng)更新只會(huì)降低你的批處理過(guò)程，而不會(huì)為系統(tǒng)帶來(lái)任何好處。除了你可以在周末關(guān)閉自動(dòng)更新，然后在每個(gè)周一早上首先更新統(tǒng)計(jì)數(shù)字之外，你還可以啟用或者禁用統(tǒng)計(jì)數(shù)字的自動(dòng)更新，通過(guò)使用sp_autostats過(guò)程。

如果你想要為單個(gè)的表或者索引更新統(tǒng)計(jì)數(shù)字，你可以使用UPDATE STATISTICS命令。或者你還可以執(zhí)行sp_updatestats系統(tǒng)過(guò)程來(lái)更新當(dāng)前數(shù)據(jù)庫(kù)中所有表上的統(tǒng)計(jì)數(shù)字。

4、報(bào)告數(shù)據(jù)和日志文件中的空間利用率

也許你被要求擴(kuò)展或者縮減一個(gè)數(shù)據(jù)文件或者事務(wù)日志文件的尺寸，那么這時(shí)候就可以使用DBCC SHRINKDATABASE 或者 DBCC SHRINKFILE命令了。

Sysindexes表在經(jīng)過(guò)一段時(shí)間之后會(huì)變得不準(zhǔn)確，特別是在增長(zhǎng)頻繁并且/或者縮減頻繁的數(shù)據(jù)庫(kù)中。DBCC UPDATEUSAGE命令報(bào)告并且糾正sysindexes表中不準(zhǔn)確的數(shù)字。如果你認(rèn)為你的數(shù)據(jù)庫(kù)或者表的尺寸與sp_spaceused系統(tǒng)過(guò)程中報(bào)告的數(shù)字不符的話，那么你就應(yīng)該使用這個(gè)語(yǔ)句。

每次在你使用DBCC SHRINKDATABASE 或者 DBCC SHRINKFILE縮減數(shù)據(jù)庫(kù)文件之后，都執(zhí)行DBCC UPDATEUSAGE，或者只是作為一個(gè)周期性的維護(hù)SQL Server保駕護(hù)航計(jì)劃，都是個(gè)好主意。

【編輯推薦】