January 22, 2013 at 11:16 am
Ive got a large transactional table. its has a clustered index on the 'rowID' column (bigint), which is in great shape (as data isnt ever deleted from the db, but thats another issue for another day).
the non-clustered index on 'fkId' and 'trnDate' is horribly fragmented, at ~90%.
I rebuild it, and the resulting fragmentation is still ~90%. I tried again adjusting the fill-factor and its higher yet. interesting, and the table DOES have 4 'bigint' columns in it. pages show avg free space of 789Kb (which is about 10%, so good to work with)
I am getting this from dm_db_index_physical_Stats, and comparing with results of dbcc showcontig. for good measure I ran a dbcc updateusage (which I know I shouldnt need on sql 2008, but...)
everything I've searched on along these lines leads me to generic stuff about tables with few pages, (this one has over 200k pages) and not quite what im after.
any useful links or tips?
thanks in advance!
January 22, 2013 at 11:25 am
How many pages does the nonclustered index have?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2013 at 11:27 am
over 218k
January 22, 2013 at 11:34 am
Can you show the output from sys.dm_db_index_physical_stats for the index? I suspect you may be looking at intermediate levels and not the leaf level.
January 22, 2013 at 11:51 am
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'dbName');
SET @object_id = OBJECT_ID(N'tableName');
IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 11, NULL , 'LIMITED');
END;
GO
database_idobject_idindex_idpartition_numberindex_type_descalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentfragment_countavg_fragment_size_in_pagespage_countavg_page_space_used_in_percent
51963870063111NONCLUSTERED INDEXIN_ROW_DATA4094.01368771129372062721.05830165994415218298NULL
edit: heres a link ot the screenshot, thats impossible to read
thanks! 🙂
January 22, 2013 at 12:15 pm
for giggles, I rebuilt with a 50% fill factor:
avg_fragmentation_in_percent changed to 94.691...
Fragment_count up to 380190
avg_fragment_size_in_pages a touch over 1.03
and page_count is up (as expected) to 394577
January 22, 2013 at 12:21 pm
You have autoshrink on by any chance?
btw, you can attach images to the posts. I'm not clicking on an obfuscated link that could go anywhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2013 at 12:24 pm
its a link to my dropbox, but understand your hesitation. dont have another place handy to stick that image at the moment, so sorry...
I DO NOT have auto-shrink enabled. (just double checked to be positive, whew!)
January 22, 2013 at 12:25 pm
You are definitely looking at the right level of the index. There must simply not be enough contiguous free space for it to rebuild it unfragmented.
January 22, 2013 at 12:27 pm
LAW1143 (1/22/2013)
its a link to my dropbox, but understand your hesitation. dont have another place handy to stick that image at the moment, so sorry...
btw, you can attach images to the posts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2013 at 12:29 pm
Robert Davis (1/22/2013)
There must simply not be enough contiguous free space for it to rebuild it unfragmented.
I've seen indexes rebuild fragmented before, but never this bad.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2013 at 12:36 pm
There was 10gb free in the data file. I was thinking in that direction myself.
219k pages @ 8k/page = 1,752,000 kb = 1.7gb... I just added a few gb more and will try the rebuild again and see what gives...
thanks!
January 22, 2013 at 12:37 pm
Doh, there we go... attachments!
scroll bar is my friend :w00t:
January 22, 2013 at 12:47 pm
I added 20gb to the data file, and immediately ran the rebuild.
same, 94.1932% avg_fragmentation_in_percent...
might this have anything to do with the datatypes of the index columns?
also, my first post had an inaccuracy in it. the non-clustered index is based on TWO big-ints and a datetime, not one...
January 22, 2013 at 12:54 pm
GilaMonster (1/22/2013)
I've seen indexes rebuild fragmented before, but never this bad.
Same here.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply