InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 21:07:46 GMT20http://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxI did a quick comparison against a few other databases (of varying sizes) with the same table/index definitions and found similar results.. short term, I'll just exclude this from the automated indexing routine i am working up, but I'd still like to understand why this index is so fragmented, even immediately after a rebuild.thanks again for everyone's help and time!: )Wed, 23 Jan 2013 07:34:00 GMTLAW1143http://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxFinal update of the day from me... my brain hurts and im going home...I just did a Alter Index ALL on this table to see what came of it... Index_id 11 is my problem child:index_id avg_fragmentation_in_percent1 0.1795208382034732 0.5321459600347524 0.51841803632080911 93.792007765141116 0.89879248101581:crazy:Tue, 22 Jan 2013 14:39:57 GMTLAW1143http://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxThe highlighted index is the one we're dealing with...and I'm not above making stupid mistakes... in fact, I usually dont recognize them as such untill I post here or ask someone else to explain something that doesnt make sense... I just double (er, triple) checked, and im looking at the right server, db, table, index statistics as the one im rebuilding... thanks again for your time and help! :)Tue, 22 Jan 2013 13:48:54 GMTLAW1143http://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspx[quote][b]LAW1143 (1/22/2013)[/b][hr]might this have anything to do with the datatypes of the index columns?[/quote]No. Nothing strange about bigint columns.Just checking... You are rebuilding the same index that you're checking (same table, same database, same server)? Not trying to be insulting, but I've seen many times people working on different servers without realising.Can you post the table and index definitions?Tue, 22 Jan 2013 13:03:50 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspx[quote][b]GilaMonster (1/22/2013)[/b][hr]I've seen indexes rebuild fragmented before, but never this bad.[/quote]Same here.Tue, 22 Jan 2013 12:54:03 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxI 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...Tue, 22 Jan 2013 12:47:30 GMTLAW1143http://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxDoh, there we go... attachments! scroll bar is my friend :w00t:Tue, 22 Jan 2013 12:37:46 GMTLAW1143http://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxThere 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!Tue, 22 Jan 2013 12:36:17 GMTLAW1143http://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspx[quote][b]Robert Davis (1/22/2013)[/b][hr]There must simply not be enough contiguous free space for it to rebuild it unfragmented.[/quote]I've seen indexes rebuild fragmented before, but never this bad.Tue, 22 Jan 2013 12:29:52 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspx[quote][b]LAW1143 (1/22/2013)[/b][hr]its a link to my dropbox, but understand your hesitation. dont have another place handy to stick that image at the moment, so sorry...[/quote][quote]btw, you can attach images to the posts.[/quote]Tue, 22 Jan 2013 12:27:33 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxYou 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.Tue, 22 Jan 2013 12:25:41 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxits 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!)Tue, 22 Jan 2013 12:24:18 GMTLAW1143http://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxYou 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.Tue, 22 Jan 2013 12:21:53 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxfor giggles, I rebuilt with a 50% fill factor:avg_fragmentation_in_percent changed to 94.691...Fragment_count up to 380190avg_fragment_size_in_pages a touch over 1.03and page_count is up (as expected) to 394577Tue, 22 Jan 2013 12:15:21 GMTLAW1143http://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxDECLARE @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;ELSEBEGIN; SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 11, NULL , 'LIMITED');END;GOdatabase_id object_id index_id partition_number index_type_desc alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent5 1963870063 11 1 NONCLUSTERED INDEX IN_ROW_DATA 4 0 94.0136877112937 206272 1.05830165994415 218298 NULLedit: heres a link ot the screenshot, thats impossible to read[url]http://bit.ly/WdEMNW[/url]thanks! :)Tue, 22 Jan 2013 11:51:23 GMTLAW1143http://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxCan 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.Tue, 22 Jan 2013 11:34:44 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxover 218kTue, 22 Jan 2013 11:27:34 GMTLAW1143http://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxHow many pages does the nonclustered index have?Tue, 22 Jan 2013 11:25:13 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1410185-391-1.aspxIve 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!Tue, 22 Jan 2013 11:16:22 GMTLAW1143