CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2005t-sql
Bmw
Bmw

Reputation: 417

Deleting rows from a table cause index fragmentation?

I removed some rows from a very large table. Then I ran a query that usually runs within a few seconds and found it to be running very slowly after deleting the rows. I re-built my index and ran my query and found it to be fast again. Could deleting those rows caused the index to be fragmented?

Upvotes: 1

Views: 3240

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332591

Yes, deleting rows affects the index and maintenance should take place to keep the index relatively in sync with existing data.

Rebuilding an index was likely unnecessary - you only need to do this if the physical fragmentation is 30 percent or more according to MS documentation. REORGANIZE is usually a better choice - think of it as defraging the index.

This is a good article series on SQL Server Index Fragmentation.

Upvotes: 4

Related Questions