CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2000database-administration
user1254579
user1254579

Reputation: 4009

Need to reorganize or recreate index after insert?

I have inserted a million rows into a table(on sql 2000), which already has several million rows in it. There are indexes on the target table. It was not dropped before the new insertion. So, do I have to rebuild or reorganize the target table's indexes, after every new insertion?? Or does SQL server 2000 already perform an auto arrangement of the indexes after the load??

Dbas /Sql specialist please reply..these indexes are confusing me a lot

Upvotes: 3

Views: 4109

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172528

Yes you should reorganize it since after such a huge insertion your indexes will be fragmented.

To check the percentage fragmentation you can do this:-

 DBCC SHOWCONTIG

or 

 select i.name,avg_fragmentation_in_percent 
 from sys.dm_db_index_physical_stats(db_id(),null,null,null,null) 
 S inner join sys.indexes I on s.object_id=I.object_id and s.index_id=i.index_id

Check this site

Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

Upvotes: 4

Related Questions