October 27, 2010 at 10:41 am
Hi,
Can anyone pls explain how index get fragmented on frequent insert, update and delete operation. Though SQL Server itself updates indexes whenever we modify the data, then why do we need to rebuild or reorganize index periodically to improve performance?
Indepth explanation would be greatful. Thanks in Advance.
October 27, 2010 at 11:00 am
http://www.sql-server-performance.com/articles/per/index_fragmentation_p1.aspx
http://www.mssqlcity.com/Articles/Adm/index_fragmentation.htm
Though these links relate to 2000, they explain why fragmentation occurs and use dbcc showcontig to find fragmentation; in 2005 and above you have the dmv sys.dm_db_index_physical_stat which you can query to find out fragmentation level of your indexes
October 27, 2010 at 11:18 am
Pradeep, Thanks for the update. The link you provided was very useful.
October 27, 2010 at 4:45 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply