September 8, 2009 at 3:47 am
Hi,
I cant get my head around something and I was wondering if someone could shed some light on this scenario;
Lets say I have a table called TableA and each night, the data from TableA is written to TableB and then TableA is updated.
TableB is in effect an archive table, a snaphot of TableA each night.
Assuming TableA is made up and id and a value, these values are then written into tableB, but TableB also has a default to store the date that the archive record was created.
Now, lets assume TableB has a unique id column, which is an identity column and has the clustered key on it, then
a second index is created on the id and date columns, the fill factor for this is the default (0), so no space remains.
Still with me I hope!
My issue is that this second index is fragmenting to nearly 99% on a daily basis (despite me reorganizing it), my first thought was it was to do with the job that was moving the data from TableA to TableB, so I decided to order that so the data is inserted in order of the id. This makes no difference to the fragmentation, so i'm thinking I could change the fill factor but I fail to see how this would make any difference, in the pages are only 80% full and i'm always going to be adding the data onto the end (as it's ordered by id and date) how can this change anything?
Any help would be great, i've attached a quick mock up of how the tables would appear if that helps to explain the situation.
Thanks,
September 8, 2009 at 4:10 am
Is autoshrink on? Is there a manual shrink operation occurring?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2009 at 4:13 am
Basically any index with fillfactor 0 or 100 makes no difference. In such case every new record insert will create a new page. Ideal is to have values between 50 and 80 depending on the insert / delete loads. If you want to avoid fragmentation in total, it's not possible. Any transaction table will undergo fragmentation over a period of time. What you can do is to go for rebuilding the index after resetting the fillfactor appropriately. In your case, the table undergoes daily bulk inserts. Have the fillfactor to 85 - 90%.(Even 100% if there is not going to be any insert / delete until the next day). Then, you can opt for rebuilding the index everyday after the bulk insert task is over.
Thanks
Suresh Ramamurthy
September 8, 2009 at 5:17 am
sureshr69 (9/8/2009)
Basically any index with fillfactor 0 or 100 makes no difference. In such case every new record insert will create a new page.
Huh?
An insert into a full page will create a new page, but unless the row size is over 4kb, subsequent inserts may not create a new page.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2009 at 5:20 am
Hi,
In answer to your question Gail, no it is not turned on. Sorry for the delay in getting back to you.
I was off the impression, lets say I have the index spread over 10 pages, and then I go and add some more data that is in the correct order of id and date, then because the pages are full this would create additional pages, and not fragment.
Surely the fragmentation indicates that the existing pages are being moved around.
Forgive me if I'm off the mark on that one.
September 8, 2009 at 5:41 am
MrT-SQL (9/8/2009)
In answer to your question Gail, no it is not turned on. Sorry for the delay in getting back to you.
Is there a shrink job? A shrink operation in a maint plan?
I'm asking, cause shrink causes massive fragmentation and it kinda fits the problem
I was off the impression, lets say I have the index spread over 10 pages, and then I go and add some more data that is in the correct order of id and date, then because the pages are full this would create additional pages, and not fragment.
Correct.
Surely the fragmentation indicates that the existing pages are being moved around.
Not necessarily been moved around (though shrink will do that), but that either new data is been added to existing pages or the row size on the existing pages is getting bigger (updating null column to non-null, increasing size of varchar data)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2009 at 7:58 am
Hi Gail,
Just double checked and there is no shrink operations what so ever.
Basically the only processes that happen are as follows;
1. 00:15 - A custom T-SQL job runs to find the top 10 fragmented indexes and then re-organize the index, so the fragmentation goes to 0 (or there abouts).
2. 02:00 - The job runs to copy the data from table A or B
Thats it. No other processes will hit the table during the day, i've just double checked the fragmentation stats I have for last night, it was scanned and found to be at 98.59, then the job ran and then after the 2am job has run I'm not showing the fragmentation at 98.60.
The only thing I can think of is that the reorganize is not reducing the fragmentation, I dont do a rebuild, I know I should but doing this on other tables would cause locking. Guess I need to change my job to assess the size of the table.
Thanks for your help with this, I really appreciate your time with this.
September 8, 2009 at 8:03 am
I'd suggest a rebuild to get into a known state, and then see what happens.
September 8, 2009 at 8:07 am
Steve Jones - Editor (9/8/2009)
I'd suggest a rebuild to get into a known state, and then see what happens.
Agreed, I've just a rebuild and verified that the fragmentation is at 0%, guess I'll just have to see what happens this evening. I'll keep you posted.
Again, thanks all.
September 8, 2009 at 8:16 am
Might be worth putting a trace in place, from the time you know the table is 0% fragmentation to when it's really high. See exactly what's happening to it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2009 at 8:40 am
GilaMonster (9/8/2009)
Might be worth putting a trace in place, from the time you know the table is 0% fragmentation to when it's really high. See exactly what's happening to it.
Good idea, i'll get a trace running on the box now.
September 8, 2009 at 11:38 am
hi MrT-SQL, please give us some more information:
- single row size in these table ?
- do you need that (id,date) index? maybe better will be (date,id) ?
- how are checking fragmentation (there are at least 2 different fragmentations) ?
September 8, 2009 at 2:38 pm
How large (# pages) is the secondary index? If it's only 10 pages (like in your example), than the fragmentation pct. reported will not be accurate.
September 9, 2009 at 5:27 am
Hi all,
Well having rebuilt the index yesterday the index is back at is 99% fragmentaion.
The trace I had running didnt pick anything of interest up, although I may broaden it to watch some other events as well.
In answer to the other questions raised;
single row size in these table ?
---------------------------------
The row consists 4 columns, and on average is 16 bytes in length, the table has just under 31k records in it.
do you need that (id,date) index? maybe better will be (date,id) ?
-------------------------------------------------------------------
Nope, I could swap it round, I suppose thinking about it makes a bit more sense to sort by the date and then the id.
how are checking fragmentation (there are at least 2 different fragmentations) ?
--------------------------------------------------------------------------------
I'm checking the fragmentation using sys.dm_db_index_physical_stats, removing anything with less than 100 pages
How large (# pages) is the secondary index?
-------------------------------------------
The index contains pages 145
One other thing i've spotted is that the default for the date the record was created is (CONVERT([varchar](12),getdate(),(101))), rather than
just a GETDATE(), this is to remove the time and just leave the date for easy reporting, maybe this is causing some issues.
The procedure that inserts the records is ordered by the id, so i'm wondering if thats causing an issue, maybe it would be best to either move the
data function into the procedure or swap the index round to use date and then id.
Thanks for your time with this matter.
September 9, 2009 at 11:02 am
Is the archiving process fragmenting the index? If so then try to disable (or drop) the index on Table B then add the records from Table A then lastely rebuild the index on TableB. Do this as part of the nightly process.
Francis
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply