index maintenance strategy if few insertion points relative to # of rows

In SQL Server 2008 R2, I have a nonclustered covering index on multiple tables with 100M+ rows. The table has a few thousand "insertion points" where all new inserts happen. This means that regardless of fill factor, I'll quickly end up with page splits and fragmentation at every insertion point, and no fragmentation or splits anywhere else in the table. Unfortunately, queries always include new rows and hence fragmented areas of the index.

• what happens when there's a page split but inserts continue sequentially after the split? Is there a way to tell SQL Server to do the split with lots of extra room for subsequent inserts, without wasting space on existing pages with a large fill factor that for most pages will never be filled?

• what are good index maintenance strategies to use for indexes like this?

• is there a good automated way to identify tables like this where fragmentation is severe but not uniform? These tables don't show up as more than 5% fragmented overall.

• are there index schema changes I should be considering?

Here's more info about the problem. The indexes all look like this pattern (simplifying for clarity below) :

CREATE TABLE Foo (
    id int identity(1,1) PRIMARY KEY CLUSTERED, 
    foreign_key int, 
    log_time datetime, 
    ...)     
CREATE NONCLUSTERED INDEX on Foo (foreign_key, log_time) INCLUDE (...)

Queries on this table are always in this form:

WHERE log_time > getdate()-70 AND foreign_key IN (select ...)

Other facts:

• there are about 5,000 foreign_key values, each with 10,000's of rows for each.

• average row size is 55 bytes, meaning around 150 rows per page

• the IN filter usually includes 10%-50% of foreign_key values rows and the date filter includes 20%-40% of the rows. The average is about 15% of total rows selected.

• the index is a covering index for the queries, so no clustered index access is needed.

A page split is a page split. You can't control what SQL Server does when it requires a page split. That is not a variable and configurable setting. When SQL Server needs to split a page, it'll put roughly half of the data in one page and half in the new page.

If you are having "multiple" insertion points into the index, causing hot spots in the index to undergo many page splits and much fragmentation then there isn't much you can do to alleviate that with the current design. It sounds like the way your data is dispersed that could be the root of your problem. If you insert into the middle of an index, you will cause page splits/fragmentation.

There are a couple of things you can do, depending on how predictable your "insertion points" are. You could utilize table partitioning, and this would allow you to rebuild the indexes on a particular partition (or partitions). This will keep cold data from receiving the maintenance, and minimize the duration of the rebuild to the hot and fragmented partitions.

Depending on your edition, you could also utilize online index rebuild operations to reduce the availability impact on this maintenance procedure.

StackExchange Database Administrators Q#25642, answer score: 5