InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 15:46:37 GMT20http://www.sqlservercentral.com/Forums/Topic958572-291-1.aspxThank you very much!And yes....next time I will....Regards,KevMon, 13 May 2013 05:45:53 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic958572-291-1.aspxIt's a heap (index 0). You can't reorganise a heap and fragmentation has a very different meaning for a heap than for an index.Please post new questions in a new thread in future. ThanksMon, 13 May 2013 05:40:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic958572-291-1.aspxTo take this thread a little further, I have a problem reorganising the indexes on a particular table. This is the query I ran.....[b]SELECT object_id AS ObjectID, index_id AS IndexID, avg_fragmentation_in_percent AS PercentFragment, fragment_count AS TotalFrags, avg_fragment_size_in_pages AS PagesPerFrag, page_count AS NumPages FROM sys.dm_db_index_physical_stats( NULL, NULL, NULL, NULL , 'DETAILED') WHERE avg_fragmentation_in_percent > 30 and page_count > 1000 ORDER BY 3 desc;[/b].....and this is the output......[b]ObjectID IndexID PercentFragment TotalFrags PagesPerFrag NumPages1295343679 0 76.4705882352941 719 10.3588317107093 7448[/b]The number of pages is 7448 so a percentage fragmentation of 76.47 has meaning. Regardless of how often I reorg the index, the %fragmentation does not reduce and I am at a bit of a loss to understand why.I would be grateful for any input you may have!Thanks in advance.Regards,KevMon, 13 May 2013 05:31:11 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic958572-291-1.aspxUnder 24 pages, a rebuild will have little to no effect, due to the way SQL allocates pages for small indexes. The 1000 page threshold is usually specified as an aprox size for indexes where you should care about fragmentation for performance reasons.Mon, 26 Jul 2010 02:15:03 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic958572-291-1.aspxThe usual reason for this is the size of the index is so small that index fragmentation figures make little sense. I can't remember if it's 100 pages (800 KB), or 1000 pages (8MB), or 1000 rows (any size!), but I wouldn't worry about fragmentation for any indexes less than 1000 pages.Mon, 26 Jul 2010 01:40:47 GMTJim McLeodhttp://www.sqlservercentral.com/Forums/Topic958572-291-1.aspxhello experts, i have rebuilt all the indexes of my database but after that still some indexes have the same fragmentation like 80 to 99. wanna know why is this so???Sun, 25 Jul 2010 23:58:23 GMTZeal-DBA