Rebuilding Indexes For Contiguity

SQL Sasquatch throws out an interesting question:  why would you rebuild an index which is 0.44% fragmented?

NC_TABLE1 is 36 total extents.  288 eight k pages.  2.25 mb. It can be read in 5 reads – one read for each contiguous run.
CI_TABLE1 is comprised of 48 extents.  3 mb. It can be read in 11 reads – again, one for each contiguous run.
The SQL Server instance has the -E startup option enabled.  Without that startup option, proportional fill algorithm would distribute incoming data to the 8 data files with a potential smallest allocation of a single 64k extent before rotating round-robin to the additional files in the filegroup.  With the -E startup option, the smallest possible new allocation by proportional fill is sixty four 64k extents – 4 mb.
That means if I can rebuild the indexes into new space at the tail of the files, the contiguity should be improved considerably.

I had never considered that the scenario described here before, so this was definitely interesting.

Related Posts

Oddity With User Write Count In dm_db_index_usage_stats

Shaun J. Stuart looks at an oddity with the user_updates column on sys.dm_db_index_usage_stats: This pulls both reads and writes from the sys.dm_db_index_usage_stats dynamic management view. A read is defined as either a seek, scan, or lookup and a write is defined as an update. All seemed good until I noticed something strange. One of the top written to tables was, based on our naming convention, a […]

Read More

Safely Dropping Databases

Bob Pusateri notes a little issue when it comes to dropping databases: At a previous employer, we had a well-defined process when dropping databases for a client. It went something like this: Confirm in writing the databases on which servers/instances to be dropped Take a final full backup of databases Take databases offline Wait at […]

Read More

Categories

February 2016
MTWTFSS
« Jan Mar »
1234567
891011121314
15161718192021
22232425262728
29