0

I have a table (a heap table) with a nonclustered index that frequently becomes highly fragmented. The data in column ID comes from (is imported from) data from a csv file and the ID is thereafter used in other table relations for reporting purpuses. The table is updated (data is inserted) from a csv several times a day. I frequently run INDEX REORGANIZATION to reduce the fragmentation.

Do you have any other ideas to help keep fragmentation from occurring so frequently?

The following is a sample script of the table:

CREATE TABLE [dbo].[MyTable](
[ID] uniqueidentifier NOT NULL,``
[EventID] uniqueidentifier NOT NULL,
[AssemblyID] uniqueidentifier NOT NULL,
[TimeStamp] [smalldatetime] NOT NULL,
[IsTrue] [bit] NOT NULL,
[IsExempt] [bit] NOT NULL CONSTRAINT [DF_IsExempt] DEFAULT ((0)),
CONSTRAINT [UQ_MyTable_ID] UNIQUE NONCLUSTERED ([ID] ))
GO

Bob Richards
  • 1

1 Answers1

0

Do you have any other ideas to help keep fragmentation from occurring so frequently?

Another idea would be to lower the fill factor of that particular table. While the fill factor does not affect the heap itself, the index is affected.
Also see: Intro to fill factor.

  1. SQL Server only uses fillfactor when you’re creating, rebuilding, or reorganizing an index. It does not use fillfactor if it’s allocating a fresh new page at the end of the index.

Let’s look at the example of a clustered index where the key is an increasing INT identity value again. We’re just inserting rows and it’s adding new pages at the end of the index. The index was created with an 70% fillfactor (which maybe wasn’t a good idea). As inserts add new pages, those pages are filled as much as possible– likely over 70%. (It depends on the row size and how many can fit on the page.)

Having a lower fill factor will let SQL Server insert more rows randomly across your table and somewhat lower the amount of page splits. You'll have to test what works best for your data insertion patterns.

DrTrunks Bell
  • 46
  • 3