sql server ...fragmentation after data deletion

Question

if we delete a larege number of rows from a table, holes are created within the table causing it to be fragmented.
will a reindex operation get rid of the holes and align the data pages again.

i have always had this confusion. Pls clarify.

thanks
Nik

Answer 1

Hi Nik,

YEs you are right.

Index fragmentation is a phenomena where index contents are no longer stored continuously in the storage.
If you want to see the fragmentation level of an index, you can use the system function called sys.dm_db_index_physical_stats() like this,
SELECT * FROM sys.dm_db_index_physical_stats(
   database_id, table_id, index_id, DEFAULT, DEFAULT
)
Index fragmentation is usually caused by deleting of existing rows , updating existing values of the indexed column or truncating table.You can see the average index fragmented percentage from the above DMv and then run

ALTER INDEX with REBUILD to remove them .

If you intend to rebuild index , do it on a non- prodcution schedule.

---

Thanks, Leks

Answer 2

Note that rebuild does nothing for data pages on a heap table.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 3

Lekss is perfectly correct from an Index Point Of View; however another cause of fragmentation is where you are deleting data (not index data) from the memory pages and thereby leaving gaps within these pages.  After performing Index Rebuilds, reorganise the pages.  This can be a lengthy process and should be performed out of hours.

---

Tony C

Answer 4

then how do i get rid of the gaps that are created with the deletes in a heap(table without index)

Answer 5

Hi ,

If you are planning to do a delete on a heap , you should choose any of the methods like 
* TABLELOCK hint when deleting the data. This uses a shared lock on the table as opposed to a row or page lock, releasing all the unused space.
* Use TRUNCATE statement in case if nuking all the rows. 
* Create a Clustered Index before deleting the records and dropt it once the delete completes

If you have done a delete on a heap already , i dont think any way will get you out of the problem  as a bug is still open on this .
SQL BUG NO 219514

---

Thanks, Leks

Answer 6

What version of SQL Server?

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 7

SQl server 2000 , does the versio of sqlserver make a differenve to the gaps created

Answer 8

You would end up doing a larger number of logical reads while executing a query. However, the fragmented table will not affect the query execution time as much as outdated statistics will. The most significant way a heavily fragmented table can hurt you is in terms of disk space usage and time taken to perform maintainence operations on the database.

In case, you have a table which has millions of rows which are being deleted at the same time and the table holds BLOB objects, you shouldn't be facing a very big problem. You can also perform a DELETE with a TABLOCK hint to make sure the space allocated to the object is released. So, if you are having an issue with high percentage of unused space in your table, then you should have a look at:
http://support.microsoft.com/kb/924947

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 9

Removing free space for a heap is not that straight-forward (until 2008 where we have ALTER TABLE ... REBUILD). Prior to 2008 we can create a cluetsered index and then remove it, export and re-imnport the data ir some similar operation. No  straight-forward command.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi