November 28, 2011 at 5:08 pm
Hi,
We are not able to defragment some indexes in one of our databases with the Index rebuild command. The current defragmentation level is above 95% (avg_fragmentation_in_percent in sys.dm_db_index_physical_stats)
The tables are very small at the moment (around 200KB) but they are going to grow very large in near future and we are anticipating performance issues. Please let us know if you have experienced similar issues or have any ideas on how to get rid of fragmentation. Some more details are:
1) If we run the SHOWCONTIG , we see that that pages from a lot of different extents are being used (i.e. database pages are not allocated from same extent). This is the cause for clustered index (or table) fragmentation.
DBCC SHOWCONTIG([dbo.tblClient])
GO
Results:
- Extent Switches..............................: 23
- Avg. Pages per Extent........................: 2.6
- Scan Density [Best Count:Actual Count].......: 16.67% [4:24]
2) If we run the DBCC IND for the fragmented index, we see that the pages allocated to clustered index are not contiguous, they are very fragmented. Even after running the REBUILD, the new pages that are allocated to index are again NOT contiguous.
USE DATABASENAME
GO
DBCC IND([DBNAME],[TABLENAME],-1)
GO
Let me know if you know a way to make the pages contiguous to get rid of fragmentation.
November 28, 2011 at 5:26 pm
This is insignificant with that little data.
November 28, 2011 at 5:31 pm
I'm interested in this topic as well actually.
What would you do if you had this problem and had large tables?
November 28, 2011 at 5:34 pm
Never had fragmentation issues with this => http://sqlfool.com/2011/06/index-defrag-script-v4-1
November 28, 2011 at 5:36 pm
Second vote for the SQLFool script.
Small tables don't defragment well because there just isn't enough extents/pages. Once they grow and you start to get hundreds or thousands of pages, fragmentation becomes more of an issue and you want to defragment the data.
November 28, 2011 at 6:04 pm
If the table\index fits on less than 8 pages then reindex procedures will ignore it because it is on a mixed extent.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply