Fragmented Index - Zero Usage - Zero Updates

Question

Post reply

Fragmented Index - Zero Usage - Zero Updates

ReamerXXVI

Ten Centuries

Points: 1379
More actions
December 14, 2014 at 8:44 pm

#293572

Howdy,

This is a question more out of curiosity than anything else.

I have produced a report on several Indexes in regards to their fragmentation levels and several have no seeks, scans lookups or updates but are fragmented. Just wondering how an index could become fragmented it it's never used, including updates.

Yes I know there is no point having these indexes but I can't remove them as it would void warranty as it's a vendor supplied DB. I'm just interested to know how them became fragmented with zero usage. Internal operations on the datafile maybe? I have not shrunk the datafile btw, as I know this causes indexes to become fragmented.

Thanks,

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

ReamerXXVI (12/14/2014)

Howdy,

This is a question more out of curiosity than anything else.

I have produced a report on several Indexes in regards to their fragmentation levels and several have no seeks, scans lookups or updates but are fragmented. Just wondering how an index could become fragmented it it's never used, including updates.

Yes I know there is no point having these indexes but I can't remove them as it would void warranty as it's a vendor supplied DB. I'm just interested to know how them became fragmented with zero usage. Internal operations on the datafile maybe? I have not shrunk the datafile btw, as I know this causes indexes to become fragmented.

Thanks,

In such cases, the indexes were fragmented prior to the last bounce of the SQL Server service for whatever reason because all of those counters are reset when the service starts back up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions