why is my fragmentation so high on non-leaf index levels

Question

why is my fragmentation so high on non-leaf index levels

Viewing 6 posts - 16 through 20 (of 20 total)

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

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

Marios Philippopoulos (1/29/2008)
Slightly off topic, but if you are planning to re-define your clustered index on one or more non-identity columns, choosing keys found in GROUP BY/ORDER BY clauses is going to give you the greatest performance benefit. Clustered indexes are most beneficial when used to return a ***range*** of records.

that's exactly what we were looking at doing

we did some informal testing 7 months ago and the performance improvement was very nice

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

our index redesign is done and i'm still seeing these issues

next step i'm going to try the pad index option

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

Wait a minute, folks... does the table have a clustered index anywhere on it or not?

--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)

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 4

yes

we used to have a clustered index on the PK column which is just an identity column and now we switched it to a non-PK column which is a bigint and used to keep records of process id's.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

That's likely the problem then... Not 100% sure, but I believe that if you don't actually have a clustered index on the table, it will never show defragmentation correctly.

Good candidate for the clustered index can be that IDENTITY column that you changed to BIGINT.

--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)

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 6

we do have a clustered index and always did

it used to be the identity column that's the PK and we changed it the index to be on the non-pk column

PK is bduidid and the clustered index is now on bduidbillid

reason being is the PK is used in some applications but not much. the non-pk column has instances where some of the values are in over 1 million rows and in up to 7 million rows in some cases.

so a select * from thistable where bduidbillid = 123456789 returns millions of rows in some cases. not a select *, but i'm too lazy to write in the other 15 columns in the statement.

used to be the select would go to an clustered index scan at around the 1.5 million row level. during testing using bduidbillid as the column with the clustered index 7 million row selects would still be a clustered index seek

the table is around 200 million rows but used to be over 300 million. we just archived a lot of the data before we went to bigint and the clustered index to make it faster. it should be back to 400 million rows in 2 months.

i'm seeing around 1500 pages on the non-leaf levels

i'm trying to improve replication performance for our billing processes where the command count runs into the tens of millions and noticed with a profiler sessions that i'm seeing around 11 reads and usually a lot more per repicated command on the subscriber