Wednesday, March 10, 2010

FILLFACTOR is really not that complicated

I keep coming across code that explicitly sets the FILLFACTOR option on an index for no good reason. FILLFACTOR is not complicated - it leaves some percentage of index pages empty in order to prevent page splits and resulting fragmentation and low page density during inserts into the middle of a key range. It can be a useful option iff:

- the table will actually get rows inserted into it!
- there is no monotonically increasing index key (like a primary key identity column)
- the author is intimately familiar with the usage pattern of the table and knows how to balance the additional I/O required to read the extra pages with the I/O saved by preventing page splits

Otherwise you're just taking an automatic IO performance hit of n% (where n is 100-(FILLFACTOR)) for no good reason.

Particularly ridiculous is the code that I've found which does something like this:

CREATE TABLE #tempstuff (foo int, bar varchar(20))

INSERT INTO #tempstuff SELECT foo, bar FROM dbo.sometable

CREATE INDEX ix_reallyNotUseful ON #tempstuff (foo, bar) WITH FILLFACTOR = 90

First off, make the index clustered. If this table will not see any more inserts, there's no reason to leave it as a heap and then have to create surrogate keys in order to build the non-clustered index. Might as well physically order it by the index keys and gain the additional performance of the clustered index.

Second, the table is not getting any more inserts after this. Why would you care about leaving extra space on the pages? Set the FILLFACTOR to 100!

This has been a public service rant from your irritable neighbor database engineer.

35 comments:

春天來嚕 said...

nice job! waiting for your new artical. ........................................

念強 said...

性愛性交性愛線上性愛線上看女優性愛性愛照85成人文學85成人片免費觀看85c免費影城85c免費成人85c成人片免費85cc文章85cc成人片觀賞85cc成人影片免費成人片觀看!85cc免費片85cc免費卡通片85cc免費影片長片播放85cc免費觀看外國片85cc洪爺85cc無碼a片85cc無碼片觀看85cc無碼影片85cc影片觀看外國85cc論談85成人影城85成人論壇85街自拍85街短片85街歐美影城85貼圖區85遊戲凹凸色色卡通圖片免費即時通視訊85cc成人片觀看交友戀愛進行室

王志竹 said...

乳交挑逗淫婦色情俱樂部成人色情成人影片視訊網愛聊天室免費色情網情色區火辣美女情色性愛成人影音聊天成人色情網站全裸美女圖片成人裸照sex辣妹裸體美女全裸圖成人區av女情色內容情色自拍貼圖成人色情網性愛論壇一對多性伴侶成人聊天室撫摸淫美成人論壇女人奶頭女生自慰影片台灣女優美女視訊一絲不掛一夜正妹成人影像巨乳大奶子情色性愛貼圖情色王國做愛視訊火辣情色台灣情色網情色聊天網性愛技巧淫娃情色成人巨乳辣妹性愛知識性經驗

李惠玲 said...

Rome was not built in a day......................................................

LupeCristoba021774 said...

舊書不厭百回讀,熟讀深思子自知。..................................................

BurtonClary031 said...

I love readding, and thanks for your artical. ........................................

士弘YajairaC_Tai0731 said...

謝謝您的分享~感恩唷!!..................................................

燕香criste0609opherc_hippl said...

thank you for you to make me learn more,thank you∩0∩

偉誠 said...

成人影視 台灣論壇女生 彩虹sexmovie影城 八國聯軍成人成人短片 台南視訊,080情人網 免費試看av,論壇區,色情 網站 下載色情短片 北台灣視訊援交聊天室 視訊聊天室90739 黑澀會 視訊 性福免費影片分享 成人交友mmshow avhigh視訊聊天 5i0美色女影城 ut影音視訊聊天室13077 情色網站live 南台灣美女視訊 性愛貼圖區 成人聊天室ut ut網際空間聊天 kk視訊,色情 a 片 一夜情 免費成人 免費視訊聊天 msn 免費成年人影片 777成人區 亞洲藝人辣妹自拍 85cc成人片觀看 完美情人視訊聊天室 新 avhigh視訊交友 性愛俱樂部 一夜情sex999免費影片 免費a片卡通 捷克論壇 s383情色大網咖情人視訊網 111av影片 美眉 線上LIVE 美女視訊聊天網 微風成人免費成人遊戲 免費影音hibb 5278cc免費影片 高雄視訊辣妹自拍 免費聊天 d760 免費e爵娛樂 a片免費試看avdvd 色漫畫帝國sex888免費看影 台南視訊,免費18遊戲 plusz色女人專用 色妹妹天堂免費情慾影片觀賞 919sex色片直播網 免費 aa 片試看,成人影片分享 情色論壇sex貼片

怡君 said...

一個人思慮太多,就會失去做人的樂趣 ..................................................

家谷 said...

志不立,天下無可成之事。......................................................

宥妃 said...

Judge not of men and things at first sight.............................................................

renew said...

很用心的blog,推推哦 ..................................................

雅馨 said...

好的blog需要我們一起努力!.................................................................

王邦鈺 said...

閒來無聊逛逛blog~~跟您打聲招呼~~.................................................................

江婷 said...

河水永遠是相同的,可是每一剎那又都是新的。......................................................................

貴寶 said...

向著星球長驅直進的人,反比踟躕在峽路上的人,更容易達到目的。............................................................

吳婷婷 said...

向著星球長驅直進的人,反比踟躕在峽路上的人,更容易達到目的。............................................................

雲亨雲亨雲亨 said...

來幫推 你個blog影d相真係好靚,係我至愛~ ..................................................................

陳韋夏陳韋夏益東富益東富 said...

喜歡看大家的文章,每篇都是一個故事,都是一種心情~~祝大家開心愉快............................................................

幸雨幸雨 said...

部落格很棒唷~ 支持你歐^^..................................................................

李志杰 said...

All good things come to an end...................................................................

陳登陽 said...

你不能左右天氣,但你可以改變心情............................................................

林彥以林彥以 said...

真正仁慈的人,會忘記他們做過的善行,他們全心投入現在的工作,過去的事已被遺忘。..................................................

蕙春蕙春 said...

很以有啟發性的故事阿~感謝大大分享^^............................................................

曹初帆張武茜 said...

認識自己,是發現妳的真性格、掌握妳的命運、創照你前程的根源。............................................................

盈甄賴賴盈甄賴賴 said...

聰明人之所以不會成功,是因為他們缺乏了堅忍的毅力。.................................................

文王廷 said...

It is easier to get than to keep it.......................................................................

偉曹琬 said...

人生像一杯茶,若一飲而盡,會提早見到杯底..................................................

家唐銘 said...

人生的價值以及他的快樂,都在於他有能力看重自己的生存................................................

淑君韓淑君韓淑君韓 said...

這麼用心的經營你的文章, 當然值得我們留連拜訪的!...............................................................

said...

好看耶~一定每天有空給你支持..................................................

建枫 said...

要經常發表文章 最愛你了呦...............................................................

黃怡君 said...

肯定與支持你!!!加油囉~..................................................

华瑗泽 said...

來給你加油打氣!!!保重!!!............................................................