0

I have a fragmentation problem on my production database. One of my main data tables is about 6GB(3GB Indexes) (about 9M records) in size and has 94%(!) index fragmentation.

I know that reorganizing indexes will solve this problem BUT my database is on SQL Server 2008R2 Express which has 10GB database limit and my database is already 8GB in size.

I have read few blog posts about this issue but non gave answer to my situation.

My Question1 is: How much size(% or in GB) increase can I expect after reorganizing indexes on that table?

Question2: Will Drop Index -> Build same index take less space? Time is not a factor for me at the moment.

Extra question: Any other suggestions for database fragmentation? I know only to avoid shrinking like a fire ;)

Rafał.S
  • 186
  • 2
  • 14
  • 1
    Avoid using `GUID` (uniqueidentifier) as your **clustering key** - that would very quickly lead to 94% index fragmentation .... – marc_s Aug 01 '14 at 07:56

1 Answers1

0

Having INDEX on key columns will improve joins and Filters by negating the need for a table scan. A well maintained index can drastically improve performance.

It is Right that GUID's makes poor choice for indexed columns but by no means does it mean that you should not create these indexes. Ideally a data type of INT or BIGINT would be advisable.

For me Adding NEWID() as a default has shown some improvement in counteracting index fragmentation but if all alternatives fail you may have to do index maintenance (Rebuild, reorganize) operations more often than for other indexes. Reorganize needs some working space but in your scenario as time is not a concern, I would disable index, shrink DB and create index.

Gouri Shankar Aechoor
  • 1,561
  • 1
  • 8
  • 8
  • This table has a GUID as a clustered index and as I have observed it went from close to 0 fragmentation to 40% in 4 days (2 "work" days). It's a shame that there is nothing I can do since data stored in this table and related tables are of utmost importance and I won't even start to introduce some other way to identify records. Thanks for answer! – Rafał.S Aug 06 '14 at 07:36