May 21, 2008 at 9:08 am
We have Databases on a Production Server. The tables of this Databases are getting fragmented the next day after their Indices were Rebuild/ Reorganised.
What could be the reason for this. This is leading to performance issue.
May 21, 2008 at 9:42 am
Are you saying that you rebuild the indexes on your table and you get performance issues because of fragmentation?
Do you perform any bulk data loads after the indexes have been rebuilt?
What fill factor do you use on the indexes?
How many indexes are on each table
Gethyn Elliswww.gethynellis.com
May 21, 2008 at 9:45 am
are you shrinking the database after the index rebuilds? that will fragment your data
---------------------------------------------------------------------
May 21, 2008 at 9:48 am
After Rebuilding Indexes on tables , tables are getting fragmented
next day automatically.
We are thinking of running Physical Disk Defragmentation.
I am sorry if i am wrong.
May 21, 2008 at 9:52 am
Keep in mind that your choice of clustered index will have a LOT of bearing on how quickly a table will fragment.
Remember that the clustered index dictates the physical order rows are stored in. If the records being inserted into the table occur over the entire range of the clustered index, and there's no space available in the data page where the new row needs to end up, then a page split occurs. Similarly - if there are updates causing a lot of rows to have updates in the Clustered Key, similarly that too can cause page splits. Heavy activity like that can cause a table to fragment quickly.
One solution might be to drop the FILLFACTOR of the clustered index somewhat, so that you allow for space in the data page to absorb some of the movement in the clustered key values without having page splits. How much space you should allow for would be dependent on how big your rows are, and how much of this kind of activity occurs.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 21, 2008 at 12:04 pm
we are not shrinking databases
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply