continuously extending a table in SQL Server
when we query sys.dm_db_index_physical_stats we see an avg_fragment_size_in_pages just under 8, namely the 8 8kB pages that can fit into one 64kB extend. A table with 50000 pages has 6300 fragments. Not very "good" page splits anymore!


If your server has a RAID disk system, or is using SAN-based storage, or has SSD storage, index fragmentation will have no appreciable effect, either way.  If you are storing the database on a single hard drive, then table fragmentation will have an effect that is most noticeable when scanning an index sequentially that spans multiple 64KB extents.  If your system is not querying large portions of the given table(s) sequentially, index fragmentation will have a low impact.

You are much more likely to see gains in performance by ensuring you have the most efficient indexes for the actual work load present on the system.  Oh, and for less than 1/5th the cost of the typical SQL Server Enterprise license you can have the highest performing PCI-attached SSD cards commonly available that can pump hundreds of thousands of I/Os per second.

Look at http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/ for more information about the index fragmentation issue.