[點(diǎn)晴永久免費(fèi)OA]SQLServer索引碎片的整理
你可能已經(jīng)創(chuàng)建好了索引，并且所有索引都在工作，但性能卻仍然不好，那很可能是產(chǎn)生了索引碎片，你需要進(jìn)行索引碎片整理。
什么是索引碎片? 由于表上有過度地插入、修改和刪除操作，索引頁被分成多塊就形成了索引碎片，如果索引碎片嚴(yán)重，那掃描索引的時(shí)間就會變長，甚至導(dǎo)致索引不可用，因此數(shù)據(jù)檢索操作就慢下來了。 有兩種類型的索引碎片：內(nèi)部碎片和外部碎片。 內(nèi)部碎片：為了有效的利用內(nèi)存，使內(nèi)存產(chǎn)生更少的碎片，要對內(nèi)存分頁，內(nèi)存以頁為單位來使用，最后一頁往往裝不滿，于是形成了內(nèi)部碎片。 外部碎片：為了共享要分段，在段的換入換出時(shí)形成外部碎片，比如5K的段換出后，有一個(gè)4k的段進(jìn)來放到原來5k的地方，于是形成1k的外部碎片。 如何知道是否發(fā)生了索引碎片? 執(zhí)行下面的SQL語句就知道了(下面的語句可以在SQL Server 2005及后續(xù)版本中運(yùn)行，用你的數(shù)據(jù)庫名替換掉這里的AdventureWorks)： SELECT object_name(dt.object_id) Tablename,si.name IndexName,dt.avg_fragmentation_in_percent AS ExternalFragmentation,dt.avg_page_space_used_in_percent AS InternalFragmentation FROM ( SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats (db_id(''AdventureWorks''),null,null,null,''DETAILED'' ) WHERE index_id <> 0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10 AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC 執(zhí)行后顯示AdventureWorks數(shù)據(jù)庫的索引碎片信息。 圖 1 索引碎片信息 使用下面的規(guī)則分析結(jié)果，你就可以找出哪里發(fā)生了索引碎片： 1)ExternalFragmentation的值>10表示對應(yīng)的索引發(fā)生了外部碎片; 2)InternalFragmentation的值<75表示對應(yīng)的索引發(fā)生了內(nèi)部碎片。 如何整理索引碎片? 有兩種整理索引碎片的方法： 1)重組有碎片的索引：執(zhí)行下面的命令 ALTER INDEX ALL ON TableName REORGANIZE 2)重建索引：執(zhí)行下面的命令 ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON) 也可以使用索引名代替這里的“ALL”關(guān)鍵字重組或重建單個(gè)索引，也可以使用SQL Server管理工作臺進(jìn)行索引碎片的整理。 圖 2 使用SQL Server管理工作臺整理索引碎片 什么時(shí)候用重組，什么時(shí)候用重建呢? 當(dāng)對應(yīng)索引的外部碎片值介于10-15之間，內(nèi)部碎片值介于60-75之間時(shí)使用重組，其它情況就應(yīng)該使用重建。 值得注意的是重建索引時(shí)，索引對應(yīng)的表會被鎖定，但重組不會鎖表，因此在生產(chǎn)系統(tǒng)中，對大表重建索引要慎重，因?yàn)樵诖蟊砩蟿?chuàng)建索引可能會花幾個(gè)小時(shí)，幸運(yùn)的是，從SQL Server 2005開始，微軟提出了一個(gè)解決辦法，在重建索引時(shí)，將ONLINE選項(xiàng)設(shè)置為ON，這樣可以保證重建索引時(shí)表仍然可以正常使用。 雖然索引可以提高查詢速度，但如果你的數(shù)據(jù)庫是一個(gè)事務(wù)型數(shù)據(jù)庫，大多數(shù)時(shí)候都是更新操作，更新數(shù)據(jù)也就意味著要更新索引，這個(gè)時(shí)候就要兼顧查詢和更新操作了，因?yàn)樵贠LTP數(shù)據(jù)庫表上創(chuàng)建過多的索引會降低整體數(shù)據(jù)庫性能。 我給大家一個(gè)建議：如果你的數(shù)據(jù)庫是事務(wù)型的，平均每個(gè)表上不能超過5個(gè)索引，如果你的數(shù)據(jù)庫是數(shù)據(jù)倉庫型，平均每個(gè)表可以創(chuàng)建10個(gè)索引都沒問題。 該文章在 2020/11/13 9:08:04 編輯過 |
關(guān)鍵字查詢
相關(guān)文章
正在查詢... 
|
400 186 1886
