May 11, 2015 at 9:48 am
We have nightly job running to reorg all in one of our prod database. But the index on one of the table fragmenting quickly by the morning showing 90% fragmentation.
What will be the reason?
May 11, 2015 at 9:50 am
you might be deleting and inserting new records.
check your data load process.
May 11, 2015 at 9:51 am
It will simply be down to the rate of change of data within the index. As a percentage of the rows in the table how much is deleted \ inserted everyday?
MCITP SQL 2005, MCSA SQL 2012
May 11, 2015 at 5:33 pm
The no of reads & writes on the table are equal. Every 15-20min there will be update on the table
May 12, 2015 at 4:00 am
The number of reads on the table is irrelevant when it comes to understand why the index is fragmenting. The issue is the number of inserts, updates and deletes on the index which cause the index to become more fragmented.
You need to look at the information returned for this index using the dmv's to see how much they are changing. As these are cumulative stats since the last time the sql server has restarted you should query the user_updates figure of the index using the dmv sys.dm_db_index_usage_stats immediately after the index maintenance is done, then again immediately before the next nights index maintenance. This will tell you how many updates happen to the table.
https://msdn.microsoft.com/en-us/library/ms188755.aspx
You have not provided any information on how big this table is, so it could be as simple as the table is pretty small say 1000 rows, but every day you delete 300 rows and insert a further 300 rows, that would be a change of 60% of the data in the table hence fragmenting the index. However with so few rows the fragmentation of the index would not really be an issue.
We really need more info such as whats the tables DDL, whats the index DDL, how big is the table, how big is the index, what stats does the DMV give you to provide a better answer.
MCITP SQL 2005, MCSA SQL 2012
May 12, 2015 at 5:19 am
RTaylor2208 (5/12/2015)
The issue is the number of inserts, updates and deletes on the index which cause the index to become more fragmented.
Deletes can't cause logical fragmentation. Inserts can and updates which increase the row size can. Deletes just leave empty space on a 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
May 12, 2015 at 6:39 am
Check If the table your Talking about is contain Primary key/index , if not then plzz create one, so effect of Insert may be reduced for fragmentation..
May 12, 2015 at 6:58 am
ramana3327 (5/11/2015)
We have nightly job running to reorg all in one of our prod database. But the index on one of the table fragmenting quickly by the morning showing 90% fragmentation.What will be the reason?
Firstly, check that the index rebuild actually runs each time, is it successful, can you see the before and after frag stats?
Updates to the rows could well be causing page splits, what fill factor are you using, have you thought of changing this?
There are so many factors to consider so you'll need to provide a lot more info
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 12, 2015 at 7:51 am
but the most important question will be , IS that fragmentation cost u so anything? is it impacting the performance ? as discussed earlier if the size of the table is less and the count of records is few then this wont hamper you
May 13, 2015 at 7:17 am
You have not provided any information on how big this table is
As has been said, you have not said anything about this. If the table is so small that it's pages are part of a mixed extant, the fragmentation may be high. But as the table is small it doesn't matter. If the table is large but lacks a clustered index, the non-clustered indexes can be fragmented more than you'd expect at the non-leaf level. I don't know what causes this, it's just something I've observed. A large table without a clustered index is not a good idea in any case.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply