SQL Server 使用 T-SQL 管理數(shù)據(jù)庫

第12章

使用T-SQL管理數(shù)據(jù)庫1管理索引碎片索引碎片檢測索引碎片索引碎片整理選項管理統(tǒng)計信息收縮數(shù)據(jù)庫使用DBCCCHECKDB2管理索引碎片：索引是保證優(yōu)化查詢執(zhí)行中最基本的元素之一。它不像SQLServer2005中其他的對象，合理地建立索引只是關鍵的第一步。對于索引還要進行長時間合理的維護。對索引維護不當，構成索引的索引頁中就會包含過多的碎片，失去它的功效。索引碎片的程度以及DBA對索引的維護程度決定著哪些索引會在SQLServer查詢優(yōu)化器中進行使用。知識點：學完本節(jié)后，你應能夠了解：什么是索引碎片；索引碎片的類型；檢測索引碎片；索引碎片的整理選項。3索引碎片：索引碎片是由于索引內的頁使用不充分而造成的。數(shù)據(jù)的修改，碎片會不斷的產(chǎn)生。索引碎片發(fā)生當數(shù)據(jù)被更改的時候，SQLServer重新整理索引頁，對索引頁進行拆分碎片類型內部碎片——由于每頁內存儲的數(shù)據(jù)量低于該數(shù)據(jù)頁可包含的數(shù)據(jù)量，因此造成索引內的頁使用不充分。外部碎片——由于頁的邏輯順序錯誤造成索引內頁的使用不充分。檢測碎片SQLServerManagementStudio–索引屬性窗口系統(tǒng)函數(shù)－－sys.dm_db_index_physical_stats

4使用SQLServerManagementStudio–（索引屬性窗口）檢測索引碎片：打開SSMS圖形化工具，查看索引碎片的詳細信息。打開查看索引的『屬性』對話框，單擊『碎片』選項。5使用sys.dm_db_index_physical_stats系統(tǒng)函數(shù)

–

檢測索引碎片：可以使用sys.dm_db_index_physical_stats函數(shù)查看一個特定索引的碎片、表或索引視圖中所有索引的碎片、一個數(shù)據(jù)庫中所有索引的碎片等等。

sys.dm_db_index_physical_stats函數(shù)的參數(shù)包括評估的數(shù)據(jù)庫、表、索引和分區(qū)的ID。該函數(shù)的結果集中：avg_fragmentation_in_percent列，它顯示了平均索引碎片的百分比；avg_page_space_used_in_percent列。它顯示了頁的填充度的百分比?？梢酝ㄟ^檢查它們，驗證索引好用與否。舉例：用T－SQL代碼演示如何使用sys.dm_db_index_physical_stats

函數(shù)獲取Production.Product

表上所有索引的平均碎片率。程序清單如下：6--獲取Production.Product表上所有索引的平均碎片率SELECTa.index_id,name,avg_fragmentation_in_percentFROMsys.dm_db_index_physical_stats(DB_ID(N’AdventureWorks’),OBJECT_ID(N’

Production.Product

’)NULL,NULL,NULL)ASaJOINsys.indexesASbONa.object_id=b.object_idANDa.index_id=b.index_id;index_idnameavg_fragmentation_in_percent1PK_Product.ProductID23.312AK_Product.ProductNumber503AK_Product.ProductName66.6666666666667代碼執(zhí)行結果表：7索引碎片整理選項：整理碎片的選項：重新組織：重新組織索引將對表的聚集索引和非聚集索引的頁級進行碎片整理；重新生成：重新生成索引將刪除索引然后創(chuàng)建一個新的索引。

重新組織：它是對頁級頁重新進行物理排序以匹配頁節(jié)點的邏輯順序（從左至右），使頁有序排列將改善索引掃描性能。重新組織索引還將壓縮索引頁。這種縮所產(chǎn)生的空頁將被刪除，提供了額外的磁盤空間。重新生成：執(zhí)行此操作時，碎片將被刪除。磁盤空間通過對頁的壓縮得以回收，并且索引行將記錄在連續(xù)頁中。通過減少獲取請求數(shù)據(jù)所需要的頁讀取次數(shù)，來提高磁盤的性能。8重新組織索引與重新生成索引：

在決定對于索引是重新組織還是重新生成來消除碎片時，應基于索引中現(xiàn)有的碎片程度?？赏ㄟ^以下兩種情況進行報告：使用SSMS圖形化工具；使用sys.dm_db_index_physical_stats

函數(shù)。avg_fragmentation_in_percent操作<=30%重新組織>30%重新生成碎片的處理方法：注意：

若碎片高于30％，重新組織索引將不會帶來改善，應嘗試重新生成索引。9重新組織索引：

使用ALTERINDEX語句的REORGANIZE子句來整理索引碎片。舉例：用T－SQL代碼使用ALTERINDEX語句重新組織了Production.Product

表上AK_Product.ProductName

索引。程序清單如下：ALTERINDEXAK_Product.ProductNameONProduction.ProductREORGANIZE重新生成索引：

使用ALTERINDEX語句的REBUILD子句來整理索引碎片。舉例：用T－SQL代碼使用ALTERINDEX語句重新生成了Production.Product

表上AK_Product.ProductName

索引。程序清單如下：ALTERINDEXAK_Product.ProductNameONProduction.ProductREBUILD10管理統(tǒng)計信息：SQLServer創(chuàng)建的統(tǒng)計信息是實現(xiàn)最佳查詢性能的一個重要方面，這些統(tǒng)計信息展示了列中數(shù)值的分布情況。在對查詢進行評估時，查詢優(yōu)化器可以使用這些統(tǒng)計信息來預估使用索引優(yōu)化器而產(chǎn)生的代價。自動生成統(tǒng)計信息：當DBA創(chuàng)建索引時，查詢優(yōu)化器將針對被索引的列存儲相應的統(tǒng)計信息，以優(yōu)化查詢的速度。在『數(shù)據(jù)庫屬性』選項對話框中（如下圖：），『自動創(chuàng)建統(tǒng)計信息』為true，數(shù)據(jù)庫引擎將為不包含索引的列創(chuàng)建統(tǒng)計信息，這些列會在查詢中用到。不同于索引，統(tǒng)計信息不包含列中的全部信息它通常只會從列中抽取200個數(shù)據(jù)來構成統(tǒng)計信息。11如上圖所示：『自動更新統(tǒng)計信息』選項，它可以使得查詢優(yōu)化器根據(jù)數(shù)據(jù)庫的變化自動更新統(tǒng)計信息。12用T-SQL語句生成統(tǒng)計信息：可使用sp_createstats

系統(tǒng)存儲過程為所有的表中所有符合條件的列創(chuàng)建統(tǒng)計信息。舉例：用代碼為當前數(shù)據(jù)庫中全部用戶表的所有合格的列創(chuàng)建統(tǒng)計信息。

程序清單：EXECsp_createstats

；

可使用CREATESTATISTICS語句為特定的表或視圖創(chuàng)建統(tǒng)計信息。舉例：用代碼創(chuàng)建NamePurchase

統(tǒng)計信息的集合。此示列對ContactID和EmailAddress列中的所有行計算統(tǒng)計信息。程序清單如下：CREATESTATISTICSNamePurchaseONAdventureWorks.Person.Contact(ContactID,EmailAddress)WITHFULLSCAN,NORECOMPUTE;13可以使用UPDATESTATISTICS或sp_updatestats系統(tǒng)存儲過程來更新統(tǒng)計信息；也可使用DROPSTATISTICS來刪除統(tǒng)計信息。查看統(tǒng)計信息：可使用不同的方法來查看統(tǒng)計信息：sp_autostats

系統(tǒng)存儲過程sys.stats

目錄視圖sys.stats_column目錄視圖STATS_DATE函數(shù)DBCCSHOW_STATISTICS語句收縮數(shù)據(jù)庫：在SQLServer中，一些操作可能會導致數(shù)據(jù)庫文件的大小超過其實際的大小。數(shù)據(jù)庫管理員可以通過收縮文件，移出未使用的數(shù)據(jù)頁，以釋放磁盤空間。14自動收縮數(shù)據(jù)庫文件：

SQLServer2005允許通過設置『數(shù)據(jù)庫屬性』中的『自動收縮』選項，使數(shù)據(jù)庫引擎可以自動對數(shù)據(jù)庫進行收縮。數(shù)據(jù)庫的持續(xù)增長和自動收縮，會導致文件碎片的產(chǎn)生。手動收縮數(shù)據(jù)庫文件：

當需要收縮數(shù)據(jù)庫文件時，更好的方法是手動收縮法?？梢詻Q定收縮操作的時機，不會給服務器造成持續(xù)的負擔。使用SHRINKDATABASE語句或者SHRINKFILE語句來執(zhí)行數(shù)據(jù)庫或者數(shù)據(jù)庫文件的收縮。注意：無法將數(shù)據(jù)庫收縮到小于創(chuàng)建時的大小。舉例：用下列代碼表明以上兩個語句的使用格式。15--減小UserDB用戶數(shù)據(jù)庫中數(shù)據(jù)文件和日志文件的大小，以便在數(shù)據(jù)庫中留出10％的可用空間DBCCSHRINKDATABASE(UserDB,10);GO--將UserDB用戶數(shù)據(jù)庫中名為DataFile1的數(shù)據(jù)文件的大小收縮到7MB

USEUserDB;GODBCCSHRINKFILE(DataFile1,7);GO程序清單如下：收縮的最佳實踐：

執(zhí)行可能會產(chǎn)生很多未使用空間的操作后，可進行收縮操作；不要反復收縮數(shù)據(jù)庫，大多數(shù)數(shù)據(jù)庫需要一些可用空間，用來日常操作時使用；16收縮操作不會保留索引碎片狀態(tài)，還會增加一定的碎片；若無特定需要，不應將AUTO_SHRINK數(shù)據(jù)庫選項設置為ON。使用DBCCCHECKDB

：作為數(shù)據(jù)庫維護操作的一部分，需要定期檢查數(shù)據(jù)庫的完整性?？梢允褂肈BCCCHECKDB語句完成任務。需要了解該語句的輸出，明確數(shù)據(jù)庫的狀態(tài)。

DBCCCHECKDB語句的命令語法如下：DBCCCHECKDB[[(database_name|database_id|0[,NOINDEX|,{REPAIR_ALLOW_DATA_LOSS|REPAIR_FAST|REPAIR_REBUILD}])][WITH{[ALL_ERRORMSGS][,NO_INFOMSGS][,TABLOCK][,ESTIMATEONLY][,{PHYSICAL_ONLY|DATA_PURITY}]}]]17參數(shù)的含義如下：

database_name|database_id|0：運行完整的數(shù)據(jù)庫名稱或ID；

NOINDEX：指定不應對用戶表的非聚集索引執(zhí)行可能引起很大系統(tǒng)開銷的檢查，這將有效減少總執(zhí)行時間；

REPAIR_ALLOW_DATA_LOSS：嘗試修改報告的所有錯誤；

REPAIR_FAST：保留語法是為了向后兼容，未執(zhí)行修復操作；

REPAIR_REBUILD：執(zhí)行次要、快速修復以及耗時修復，執(zhí)行這些修復不會丟失數(shù)據(jù)；

ALL_ERRORMSGS：顯示針對每個對象報告的所有錯誤。指定了它，則只為每個對象顯示前200條錯誤消息。按對象ID對錯誤進行排序，tempdb

數(shù)據(jù)庫生成消息除外；

NO_INFOMSGS：取消顯示所有信息性消息；

TABLOCK：使DBCCCHECKDB獲取鎖，并且使其在負荷較重的數(shù)據(jù)庫上運行更快，會減少數(shù)據(jù)庫上可獲得的并發(fā)性；18

ESTIMATEONLY：顯示運行包含所有其他執(zhí)行選項的DBCCCHECKDB時所需的tempdb

空間估計數(shù)量，但不執(zhí)行實際數(shù)據(jù)庫檢查。

PHYSICAL_ONLY：用較小的開銷數(shù)據(jù)庫的物理一致性、硬件故障等；

DATA_PURITY：檢查數(shù)據(jù)庫中是否