March 15, 2009 at 6:02 pm
There was a recent thread about small tables not defragmenting, But I seem to have a large table with indexes that won't defragment. I've just moved some databases to 2005 servers on a SAN. I read the recommendations to rebuild indexes after the migration. I ran [font="Courier New"]dm_db_index_physical_stats [/font]and found that a table with 3 million records had indexes 77% fragmented.
I ran:
ALTER INDEX CIF_Ind ON [dbo].Call_Detail
REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
And then if i re-run [font="Courier New"]dm_db_index_physical_stats [/font]I get the same results. Since the data is spread widely over arrays of disks in a SAN, is defragmenting a different animal ?
March 15, 2009 at 9:13 pm
default fillfactor?
DBCC showcontig give you similar results? I almost suspect the DMV is not updating correctly after the rebuild.
March 15, 2009 at 10:28 pm
Could you list the output of the DMV here please for the given table?
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 16, 2009 at 7:35 am
Does your table have a clustered index? If not, the table won't defragment. You could only defrag the nonclustered indexes in this case.
March 16, 2009 at 1:40 pm
Thanks for the replies. Most of the tables do not have clustered indexes, so I guess the HEAP cannot be defragmented. As for the others,... I had built a query using dm_db_index_physical_stats and joing to system tables to get object names and other info. I think I misunderstood some of the results, because on closer looking I think I am seeing some improvement. I've been too busy to dig much deeper yet.
So what does it mean if HEAP shows 98% fragmentation ?
March 16, 2009 at 2:10 pm
homebrew01 (3/16/2009)
Thanks for the replies. Most of the tables do not have clustered indexes, so I guess the HEAP cannot be defragmented. As for the others,... I had built a query using dm_db_index_physical_stats and joing to system tables to get object names and other info. I think I misunderstood some of the results, because on closer looking I think I am seeing some improvement. I've been too busy to dig much deeper yet.So what does it mean if HEAP shows 98% fragmentation ?
In my understanding it tells how many records have gown out of order because of updates; in a heap allocation SQL Server doesn't have any linking information from one page to another so when a record is updated if it is bigger the current then the forward-look up is placed in its place and the record gets moved foward.
So .. Lets say we have following Entires in our Heap...
01:Entry100001
02:Entry100002
03:Entry100003
04:Entry100004
We update entry number 2, to Entry1000021. It cannot fit at location #2 because it is too big .. so it gets thrown at the end of the table in reserved page and we get:
01:Entry100001
02:FWDLKU05
03:Entry100003
04:Entry100004
05:Entry1000021
This is done so they don't have to go update all the non-cluster indexes RID information to repoint it. So I think when it has the 98% fragmentat I think your table is probably filled with Foward Lookups and there is no grantee that the lookup are in proper sort key order as heap entries are in order they were insereted...
I hope that gives yaa an idea why Heaps can't be defragmented. Becuase there is no way for SQL Server to know what order to sort them in ...
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply