Erland Sommarskog (8/26/2013)
Eric1/2aB (8/26/2013)
6 - What would you suggest for fill factor on a table like this? 100%?{/quote]For indexes on GUIDs, an idea is to have a low fill factor, say 50%. New guids will fill in the holes, and reduce the amount of page split. As the pages start to get full, page splits will occur, so you should rebuild in due time.
It will mean a waste of empty space in the buffer cache, though.
I concur with Erland. I usually use somewhere between 50 and 70% fill factor for indexes at clients that lead off with a non-sequential GUID, depending on the index defrag interval and how fragmented stuff gets between intervals. I like to see you just hitting 5 or maybe 10% frag when you hit your index mx run. That seems to give a balance between having pages start out (and spend too much time) too empty and splitting pages/extents all over the place (with the corresponding tlog activities and other negative frag effects).
Speaking of sequential GUIDs, some systems can be configured to create them on the generating server. I HIGHLY recommend seeing if your systems can do that. HUGE win on many fronts if you can avoid full-range GUID values. Having 3 or 10 places where ranges split is way better than the alternative I think.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service