Percentage of Fragmentation increased after 6 hours

Question

Percentage of Fragmentation increased after 6 hours

Viewing 4 posts - 31 through 33 (of 33 total)

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

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

coolchaitu (9/11/2015)
You are correct. It refers to the index in the attached image. Its definition is as below:
INDEX = IX_tblGPSDataDetail_DeviceID_GPSDateTime ON dbo.tblGPSDataDetails
(
DeviceID {udt_DeviceID 4}
, GPSDateTime {varchar 50}
, DataReceviedDateTime {udt_date 8}
)
INCLUDE
(
MeterStatusID {udt_tinyId 1}
)
I tried rebuilding the index with fillfactor=80 yesterday night. I checked the fragmentation and it is 98%. I will rebuild the index with fillfactor=70 tonight.

Wait a minute. I missed the fact that GPSDateTime is a VARCHAR. What format is that column stored in?

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

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 2

Jeff Moden (9/11/2015)
What format is that column stored in?

I'm guessing again ... but a normal GPS message starts with a code (which might not change) immediately followed by the time hhmmss; the date is much further along the message (but is ddmmyy). There is also a Lat/Long in the middle which might be changing ...

So if my guess is right there is plenty of stuff at the left-hand-end that will change significantly during the day 🙁

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

Kristen-173977 (9/11/2015)
Jeff Moden (9/11/2015)
What format is that column stored in?
I'm guessing again ... but a normal GPS message starts with a code (which might not change) immediately followed by the time hhmmss; the date is much further along the message (but is ddmmyy). There is also a Lat/Long in the middle which might be changing ...
So if my guess is right there is plenty of stuff at the left-hand-end that will change significantly during the day 🙁

Exactly! No wonder the fragmentation is going nuts. It's expected! 😀

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

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 4

Previously I was hoping that it was hi-lo, e.g. YYYYMMDD HHMMSS.SSS, so although changing was being moved "close by" in the index page and would be self correcting as maybe only 1 extent to the index page.

But checking how GPS handles date/time it looks like it is more likely to be lo-hi and millions of changes then the index extension pages are going to be huge ...

The answer might be to change the format of GPSDateTime so that it is arranged Hi-Lo (or split and stored as native data types) so that the index changes less dramatically. Its still going to change though ... might be possible to only index by Year, Month, Day DATE part, excluding Time [i.e. that might be good enough for reporting purposes]. Perhaps using a computed column, or an indexed view ...

Another good reason, which I hadn't encountered before, not to store dates as text!

(but I'm still guessing that this is lo-hi datetime and/or time-first)

Might also be worth checking the DMV that this blessed index is actually used! The STATS are going to be shot after a couple of milliseconds!!