CODEWITHSUNDEEP
sql-serverindexing
Igor Gatis
Igor Gatis

Reputation: 4898

How to design SQL Server index to avoid fragmentation for columns that behave like partitions?

Consider the SQL Server table created by this command:

create table Foo 
(
  Id identity(1, 1) primary key clustered,
  Time datetime2,
  Host varchar(64),
  Client varchar(64),
  ... Bunch of columns ...
);

create nonclustered index ix_foo_time on foo (time);

Id and Time columns make good indexes because they are immutable and ever increasing. (Almost) no fragmentation happens on these two.

Now consider that I need queries to work fast for both Client and Host columns. I created nonclustered indexes for each one of them. After a while, these indexes become very fragmented.

The nature of these columns is well known. There are a few hundred values for each. It is as if data is "partitioned" based on these columns.

Is there a way to tell SQL Server how it should behave to prevent index fragmentation?

Upvotes: 2

Views: 511

Answers (2)

Bryan
Bryan

Reputation: 3451

Fragmentation is part of index management as data changes, some smaller table indices will retain their fragmentation regardless of a rebuild.

  • The below script links will help you manage index fragmentation extremely well.

I advise against static index maintenance plan tasks as the other author suggests, as maintenance plans in SSMS/SSIS rebuild index(s) regardless of fragmentation percentages, and are thus wasteful on IO, causing contention on your ETL or end users. If a DBA sees you using static index maintenance plan tasks to rebuild indexes across databases they will probably replace them with the first link below.

Upvotes: 1

usr
usr

Reputation: 171206

You have correctly diagnosed the reason for the fragmentation. Although there are just a few insertion points fragmentation will occur on newly inserted data.

Fillfactor will not help here because the free space will be consumed almost immediately at the few hundred insertion points. All other pages in the table will have useless free space in them.

Unfortunately, there is no way to avoid the fragmentation for newly inserted data here. You will need to install a maintenance plan.

Note, that existing data (that predates an index build) will not become fragmented. The page splits will be localized at the few hundred insertion points. Therefore fragmentation will become less when expressed as a percentage of the table size when the table grows.

Upvotes: 0

Related Questions