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% offoreign_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.