資源描述:
《十步優(yōu)化sql server中的數(shù)據(jù)訪問》由會(huì)員上傳分享,免費(fèi)在線閱讀,更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫(kù)。
1、第三步:整理索引碎片 你可能已經(jīng)創(chuàng)建好了索引,并且所有索引都在工作,但性能卻仍然不好,那很可能是產(chǎn)生了索引碎片,你需要進(jìn)行索引碎片整理?! ∈裁词撬饕槠? 由于表上有過度地插入、修改和刪除操作,索引頁(yè)被分成多塊就形成了索引碎片,如果索引碎片嚴(yán)重,那掃描索引的時(shí)間就會(huì)變長(zhǎng),甚至導(dǎo)致索引不可用,因此數(shù)據(jù)檢索操作就慢下來(lái)了?! ∮袃煞N類型的索引碎片:內(nèi)部碎片和外部碎片。 內(nèi)部碎片:為了有效的利用內(nèi)存,使內(nèi)存產(chǎn)生更少的碎片,要對(duì)內(nèi)存分頁(yè),內(nèi)存以頁(yè)為單位來(lái)使用,最后一頁(yè)往往裝不滿,于是形成了內(nèi)部碎片?! ⊥獠克?/p>
2、片:為了共享要分段,在段的換入換出時(shí)形成外部碎片,比如5K的段換出后,有一個(gè)4k的段進(jìn)來(lái)放到原來(lái)5k的地方,于是形成1k的外部碎片?! ∪绾沃朗欠癜l(fā)生了索引碎片? 執(zhí)行下面的SQL語(yǔ)句就知道了(下面的語(yǔ)句可以在SQLServer2005及后續(xù)版本中運(yùn)行,用你的數(shù)據(jù)庫(kù)名替換掉這里的AdventureWorks): SELECTobject_name(dt.object_id)Tablename,si.name IndexName,dt.avg_fragmentation_in_percentAS Ext
3、ernalFragmentation,dt.avg_page_space_used_in_percentAS InternalFragmentation FROM ( SELECTobject_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent FROMsys.dm_db_index_physical_stats(db_id('AdventureWorks'),null,null,null,'DETAILED
4、' ) WHEREindex_id<>0)ASdtINNERJOINsys.indexessiONsi.object_id=dt.object_id ANDsi.index_id=dt.index_idANDdt.avg_fragmentation_in_percent>10 ANDdt.avg_page_space_used_in_percent<75ORDERBYavg_fragmentation_in_percentDESC 執(zhí)行后顯示AdventureWorks數(shù)據(jù)庫(kù)的索引碎片信息?! D3
5、索引碎片信息 使用下面的規(guī)則分析結(jié)果,你就可以找出哪里發(fā)生了索引碎片: 1)ExternalFragmentation的值>10表示對(duì)應(yīng)的索引發(fā)生了外部碎片; 2)InternalFragmentation的值<75表示對(duì)應(yīng)的索引發(fā)生了內(nèi)部碎片?! ∪绾握硭饕槠? 有兩種整理索引碎片的方法: 1)重組有碎片的索引:執(zhí)行下面的命令 ALTERINDEXALLONTableNameREORGANIZE 2)重建索引:執(zhí)行下面的命令 ALTERINDEXALLONTableNameREBUILD
6、WITH(FILLFACTOR=90,ONLINE=ON) 也可以使用索引名代替這里的“ALL”關(guān)鍵字重組或重建單個(gè)索引,也可以使用SQLServer管理工作臺(tái)進(jìn)行索引碎片的整理?! D4使用SQLServer管理工作臺(tái)整理索引碎片 什么時(shí)候用重組,什么時(shí)候用重建呢? 當(dāng)對(duì)應(yīng)索引的外部碎片值介于10-15之間,內(nèi)部碎片值介于60-75之間時(shí)使用重組,其它情況就應(yīng)該使用重建。 值得注意的是重建索引時(shí),索引對(duì)應(yīng)的表會(huì)被鎖定,但重組不會(huì)鎖表,因此在生產(chǎn)系統(tǒng)中,對(duì)大表重建索引要慎重,因?yàn)樵诖蟊砩蟿?chuàng)建索引可能會(huì)
7、花幾個(gè)小時(shí),幸運(yùn)的是,從SQLServer2005開始,微軟提出了一個(gè)解決辦法,在重建索引時(shí),將ONLINE選項(xiàng)設(shè)置為ON,這樣可以保證重建索引時(shí)表仍然可以正常使用?! ‰m然索引可以提高查詢速度,但如果你的數(shù)據(jù)庫(kù)是一個(gè)事務(wù)型數(shù)據(jù)庫(kù),大多數(shù)時(shí)候都是更新操作,更新數(shù)據(jù)也就意味著要更新索引,這個(gè)時(shí)候就要兼顧查詢和更新操作了,因?yàn)樵贠LTP數(shù)據(jù)庫(kù)表上創(chuàng)建過多的索引會(huì)降低整體數(shù)據(jù)庫(kù)性能?! ∥医o大家一個(gè)建議:如果你的數(shù)據(jù)庫(kù)是事務(wù)型的,平均每個(gè)表上不能超過5個(gè)索引,如果你的數(shù)據(jù)庫(kù)是數(shù)據(jù)倉(cāng)庫(kù)型,平均每個(gè)表可以創(chuàng)建10個(gè)索引
8、都沒問題?! ≡谇懊嫖覀兘榻B了如何正確使用索引,調(diào)整索引是見效最快的性能調(diào)優(yōu)方法,但一般而言,調(diào)整索引只會(huì)提高查詢性能。除此之外,我們還可以調(diào)整數(shù)據(jù)訪問代碼和TSQL,本文就介紹如何以最優(yōu)的方法重構(gòu)數(shù)據(jù)訪問代碼和TSQL?! 〉谒牟剑簩SQL代碼從應(yīng)用程序遷移到數(shù)據(jù)庫(kù)中 也許你不喜歡我的這個(gè)建議,你或你的團(tuán)隊(duì)可能已經(jīng)有一個(gè)默認(rèn)的潛規(guī)則,那就是使用ORM(ObjectRelationalMappi