Monday, October 5, 2009

Does the fragmentation have impact on performance on Heaps?

I have been working on some project that involves using very huge tables. Actually most of tables are heaps and to reduce IO we decided to compress those tables on PAGE level because the database is READ-ONLY. Fortunately in SQL Server 2008 we can issue ALTER TABLE... REBUILD.. to rebild the heaps to remove forwarding pointers ,please read Tibor's article (http://sqlblog.com/blogs/tibor_karaszi/archive/2009/08/28/heaps-forwarding-pointers-alter-table-rebuild-and-non-clustered-indexes.aspx)

Well ,after running the scripts we ended up with almost 500GB empty space. Usually it is not good to shrink the database as you gain nothing (it will grow again) and that caused fragmentation which does not help in terms of performance. Shrinking VLDB is another topic but what I did NOT know if your queries are doing INDEX SEEK SQL Server can get to any row easily no matter how fragmented the table is.
In that case fragmentation has no impact on performance.

If you think about it does perfect sense. Fragmentation is a measure of how closely the logical order matches the physical order. So if you do a scan (following the logical order) on a fragmented table, you can be jumping all over the to get the rows you need,instead of accessing them nice and neat and contiguously.

Many thanks to Kalen Delaney.

No comments: