1

I have the following table that has a couple of million rows in it and is 99% fragmented virtually all of the time. My plan was to insert a IDENTITY field as a surrogate key to replace the current composite 6 field primary, then make the current key a unique key for referential integrity and recreate the indexes.

    CREATE TABLE [dbo].[Autocompleter](
        [CountryId] [int] NOT NULL,
        [ProvinceId] [int] NOT NULL,
        [LocationId] [int] NOT NULL,
        [PlaceId] [int] NOT NULL,
        [EstabId] [int] NOT NULL,
        [LocaleId] [int] NOT NULL,
        [Title] [varchar](400) NOT NULL,
        [Hotels] [int] NULL,
        [AlternateTitles] [varchar](4000) NULL,
        [EnableHotels] [bit] NOT NULL,
        [EnableHolidays] [bit] NOT NULL,
        [DisplayPriority] [int] NOT NULL,
     CONSTRAINT [PK_autocompleter_1] PRIMARY KEY CLUSTERED 
    (
        [CountryId] ASC,
        [ProvinceId] ASC,
        [LocationId] ASC,
        [PlaceId] ASC,
        [EstabId] ASC,
        [LocaleId] ASC
    )

Any gotchas I should be looking out for ? if it is an identity field I am thinking this should not break code that inserts into the table (as long as it specifies the columns explicitly)

I plan to create a new clustered index on the surrogate key and then make the current clustered index on the 6 fields a NC index.

Improve this question
5
  • after 3 days of searching I found the developer who wrote it ! it gets fully populated every morning after the my index optimization job, so I can easily solve my problem. I'll leave the question though as interested on thoughts – DamagedGoods Sep 11 '12 at 8:31
  • 1
    Four questions: Does this table have any child tables via FK? Are there any other NC indexes defined? Does this table have an FKs to parent tables? Are there many writes after this "fully populated every morning"? – gbn Sep 11 '12 at 9:23
  • it is a de-normalized table that contains information for a website autocompleter to return results. it is not updated after the full population (the job I have added to re-index it only takes 7 secs, so im happy with that). There are 2 additional NC indexes. There are no foreign keys on these any of the rows – DamagedGoods Sep 11 '12 at 9:32
  • 2
    What's the compelling reason to add an integer and make the existing primary key a nonclustered index instead of just making the existing primary key a nonclustered primary key? – Mike Sherrill 'Cat Recall' Sep 11 '12 at 11:18
  • 3
    What problem are you solving? – A-K Dec 10 '12 at 14:59
1

Reading up on Kimball dimensional modeling, use of a surrogate key, in particular an IDENTITY, will help reduce fragmentation caused by page splits as you'll be appending rows at the end of leaf pages vs. attempting to insert them in the middle if the keys are not in the order of the index (ascending or descending depending on how the index was defined).

But, if the surrogate key will not be used in joins with other tables or in WHERE criteria, use of the surrogate key as a clustered index might not provide any additional benefit after the initial data import. If you keep the composite clustered key, sorting the source data in the order of the six-column composite key prior to import should avoid the page split fragmentation you're witnessing.

As for a choice of clustered index, the best candidate may be the column(s) that would be used in your most common/critical queries for WHERE criteria or joins. Use of a NCI will require a bookmark lookup to obtain the remaining values in the table if needed in the resultset.

Improve this answer
-1

At the storage level in SQL Server (probably in all dbms), there aren't any tables. At the storage level in SQL Server you have one of two things. (I think.) You have

  • the heap plus some other things, or
  • the clustered index plus some other things.

Heaps get fragmented by randomly distributed inserts, updates, and deletes. AS far as I know, Microsoft doesn't provide any direct way to defragment the heap.

But create clustered index rewrites the heap. So you could create a clustered index to rewrite the heap, then drop the clustered index. I don't think of this as a direct way to defragment the heap, because rewriting the heap is a side-effect of creating a clustered index.

Here's what I don't understand yet. (And it might be pretty basic knowledge, but I don't work on SQL Server a lot.)

The entire table gets reloaded every day. It's never updated, apart from that daily reload. So where does the fragmentation come from? Bad choice of fill factor? Would a fill factor of 100 be a defensible choice for a table like this?

Improve this answer
1
  • For the record your generalization to other dbs is somewhat wrong. PostgreSQL for example, stores separate relations in separate (sometimes split) heap files. – Chris Travers Feb 14 '13 at 9:57

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.