Index frangmenting

Question

Index frangmenting

Viewing 7 posts - 16 through 21 (of 21 total)

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

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

p.s. I contacted the Web Master to restore the post I accidentally hit "Report" on .

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

First, what index level are you looking at in sys.dm_db_index_physical stats? If it's not level "0" and it's not "IN ROW DATA", you can mostly forget about it.

Before we move on from your trigger, have you actually testing it to see if it actually works and it actually sends you an email? If you have and that works, then you're nothing is doing anthing but SELECTs in your table and either something is being reported incorrectly or you're reading something wrong from somewhere.

And I'm not saying that in a malicious way.

Fragmentation isn't possible without data adds or mods. Backups and the like aren't going to cause it either. I'm thinking the trigger may not be working but don't know for sure.

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

schleep SSChampion Points: 12585 More actions · Answer 3

Confirmed: 0 for index level, In-Row-Data, and trigger generated the expected email for each of an actual Insert, Update and Delete.

FWIW: This is not a end of the world scenario - this is a rarely used - 1 select every few minutes or so.

I just got curious when I noticed something that made me go "Hmmmmm?"

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

schleep wrote:

FWIW: This is not a end of the world scenario - this is a rarely used - 1 select every few minutes or so.
I just got curious when I noticed something that made me go "Hmmmmm?"

It made me go "Hmmmmm?" as well. If your trigger isn't picking up on any Inserts, Updates, or Deletes but the logical fragmentation is increasing, I'm insanely curious about now that could be. It just doesn't seem possible but there it is. If it were me, I'd be driven to setup an SQL Profiler run looking for the table or an EE session or something to find out how in the world such a thing is happening. I'd REALLY like to know what's going on with this.

And part of me requesting all the confirmations is that I have seen folks make a mistake and thing that the average percent of page space used is the column for logical fragmentation. I have a presentation on how page density may be in the toilet even after Reorg/Rebuild and what to do about it. I was trying to make sure that wasn't the case here.

Another way of checking is to check the page allocations for a total count of pages. It won't tell you if index keys have been modified and it won't tell you anything about non-expansive updates but a change in page counts would tell us if there are most types of changes being made that would cause fragmentation.

Like you said, not an end of the world scenario but damned interesting because it's supposed to be impossible for this to happen (as least in my understanding... might be an "oolie" I don't know about).

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

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 5

I know that it is a shot in the dark, but did you check if the table was drop and recreated between the time that you dropped it and created it and the time that you checked the fragmentation? Maybe there is another process that you are not aware of that does it, and it doesn't rebuild the index after the load.

Like other recommended I would create an extended event or a server side trace that will catch the update, delete, insert and merge operations on this table. I prefer it on a trigger.

Adi

schleep SSChampion Points: 12585 More actions · Answer 6

Exceptionally busy next few days coming, but I did rebuild this morning, set a trace, and did check once to find 0.04% fragmentation - not the usual leap to 66.-whatever% it always is, but more than 0.

Nothing suspicious in the trace.

Will follow up next week.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

schleep wrote:

Exceptionally busy next few days coming, but I did rebuild this morning, set a trace, and did check once to find 0.04% fragmentation - not the usual leap to 66.-whatever% it always is, but more than 0.
Nothing suspicious in the trace.
Will follow up next week.

It's a rare thing to actually get absolutely 0.000000 fragmentation because it's also a rare thing that all pages of even new tables are physically contiguous and in the same perfect order physically as they are logically.

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