We have a large data warehouse database where we continuously get new rows inserted in 5 different tables, at the left-hand side of the b-tree (=at end of the table)

This cause a lot of page splits. Paul Randall calls these "good" page splits as in they do not cost much when inserting the data.

But 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!

The only way I know we can bring these extends together (in order to get better read-ahead), is to REBUILD the table (or table partition).

Question 1) Are there other options?

We have one table that in addition to the new inserts get a lot of updates to the newer rows. I.E we get 6 rows every hour, and once a week all rows for the entire week are recalculated (=updated). Fillfactor only works for index REBUILDs

Question 2) Is there any way I can get SQL Server to use a FillFactor look-alike when inserting rows on the left-most page in the B-Tree?

  • 1
    Why not change the table such that inserts never result in a page split? You could add a new IDENTITY(1,1) field that is the clustering key. Adding the table definition(s) to your question would help.
    – Hannah Vernon
    Oct 4 '13 at 5:21
  • Hi Max, We do indeed have such a column. But when you insert rows at the end of the table (left-hand of the B-Tree), you get an Extend allocated at a different place on the harddisk. The Extend is 8 pages of 8040 bytes each. When that it used you get another extend, but at a third place on the hard disk. This is what Paul Randall calls "Good" page splits. Never the less it results in a very fragmented table Oct 4 '13 at 6:04
  • If you have the table clustered on an IDENTITY(1,1) field, adding records to it will not result in a page split. New pages will be added to the clustered index (the table itself!) whenever a new row will result in the current last page being filled. Can you add your table definition to the question?
    – Hannah Vernon
    Oct 4 '13 at 6:06
  • 1
    I'm starting to consider my table not as a WORM (Write Once, Read Many), but a WOTURM (Write Once, Then Update, Read Many) table. The REBUILD index is my update. It means that I need good partitioning, so that my Index Maintenance does not incur too many I/Os and take too long. Oct 4 '13 at 13:19
  • @MaxVernon: please drop me an answer. Oct 7 '13 at 5:19

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.

  • Are you sure that it has no effect when using a SAN or SSD? That is not my findings. When the table is fragmented, SQL Server read-ahead will be disabled, and pages will not be in memory when needed. Then the SQL Statement will be transferred to the waiting queue. Oct 8 '13 at 7:46
  • It took a bit of time to read and understand the link to Brent Ozars homepage. But I agree with him. Only problem is that I cannot cache the tables in memory. They are +10 TB. Yes, we use Ola Hallengrens scripts. <<it gets around 200MB/sec for large sequential reads, but under <<2MB/sec for random reads. Ouch. Which is what we see. Oct 8 '13 at 8:44
  • Thank you very much for your help with solving this problem. On the system that is running Enterprise edition, we'll use partitioning, and aim for enough extra I/Os to keep the partitions updated. I'm not sure what we'll do on the other systems. Oct 10 '13 at 5:24

I'm answering my own question such that others can see what I have arrived at as a possible solution. Comments are most welcome.
Here it is:

I could create one filegroup per large table in our database. This would result in a lot of files. I will have to size them reasonable, and monitor closely, in order not to have too much unused space on the harddisks. Space that might be needed by other tables.

So if I set the file growth too small, I will have a lot of (external) file fragmentation. If I set it too high our database will have a lot of downtime, because it runs out of disk space elsewhere in other filegroups.

I will have to recode all ALTER TABLE T1 REBUILD to copy the entire table into a new filegroup instead of creating a copy within the existing filegroup. When the rebuild has finised the old filegroup must be dropped&deleted.

I will have to script the RESTORE DATABASE db statement, and keep the script at hand for Disaster Recovery.

Over time (years) the harddisk will be fragmented, and I may need downtime to defragment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.