June 11, 2009 at 3:24 pm
I have written a script that uses the dynamic management view sys.dm_db_index_physical_stats to identify the indexes for a particular database that need to be rebuilt, then rebuilds them and generates a report of the before and after fragmentation percentages.
Here is my question, why do the indexes with a lower page_count sometimes end up with a higher avg_fragmentation_in_percent after a alter index xxxx on dbo.xxxx rebuild is run?
I also tried alter index xxxx on dbo.xxx reorganize, same results, lower page count indexes didn't change their fragmentation percentage and sometimes the percentage of fragmentation went up????
How weird is that?
June 12, 2009 at 4:38 am
Hey Rob,
Can you post the results that show you that fragmentation is increasing together with the query that generated the results please?
I want to make sure that you are comparing leaf-level fragmentation for instance.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 12, 2009 at 9:00 am
Paul White (6/12/2009)
Hey Rob,Can you post the results that show you that fragmentation is increasing together with the query that generated the results please?
I want to make sure that you are comparing leaf-level fragmentation for instance.
Paul
From the dynamic management view sys.dm_db_index_physical_stats, I am comparing the column avg_fragmentation_in_percent.
From BOL, the column avg_fragmentation_in_percent shows:
"Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit."
The script I wrote excludes HEAPS, it queries through the database and identifies the indexes that have a value greater than 0 in this column.
here is the SQL:
SELECT db_name(st.database_id) as dbname,so.name as tbname,si.name as idxname,st.index_type_desc,si.origfillfactor,st.[object_id],st.index_id,st.avg_fragmentation_in_percent,st.avg_fragment_size_in_pages,st.page_count,st.fragment_count,cast(0.00 as float) as after_frag_percent FROM sys.dm_db_index_physical_stats (7,NULL,NULL,NULL,NULL) st join HRTEST91.dbo.sysindexes si on st.[object_id] = si.id and st.index_id = si.indid join HRTEST91.dbo.sysobjects so on si.id = so.id and so.xtype = 'U' WHERE st.avg_fragmentation_in_percent > 0 AND st.index_type_desc 'HEAP'
I stick the results in a temp table then loop through them and run an alter index rebuild on them individually.
After each is rebuilt I query the dm again for the value in avg_fragmentation_in_percent. When page_count is less than 15, sometimes the avg_fragmentation_in_percent stays the same, sometimes it goes up.
The results are in a text file, so they really aren't very readable when I try to copy and paste them here.
Any ideas?
June 13, 2009 at 12:24 am
rob.patterson (6/12/2009)
When page_count is less than 15, sometimes the avg_fragmentation_in_percent stays the same, sometimes it goes up.
The first eight pages for a structure are allocated from mixed extents (unless trace flag 1118 is in effect). This means that the first eight pages will generally be logically fragmented.
Once a structure (e.g. index or table) grows beyond eight pages, SQL Server only allocates uniform extents - even if the structure subsequently occupies less than eight pages.
Rebuilding the clustered index once a structure occupies more than eight pages will remove the data fragmentation. A similar consideration applies to indexes.
You can check whether a structure is using mixed extents using the DBCC EXTINFO command:
DBCC EXTINFO(database_id, table_name, index_name);
An ext_size of 1 indicates a page allocated from a mixed extent.
Finally, I would just say that you should really only be concerned about fragmentation on tables which are scanned (especially if read-ahead is used). Fragmentation has no impact on singleton selects (aside from concerns related to page fullness). Read-ahead is unlikely to occur on structures less than 500 or so pages (I forget the exact threshold).
Paul
P.S. If you are really interested in looking at the index structures in detail, you can use DBCC IND to list the pages, and DBCC PAGE to examine them.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 13, 2009 at 8:18 am
Paul White (6/13/2009)
rob.patterson (6/12/2009)
When page_count is less than 15, sometimes the avg_fragmentation_in_percent stays the same, sometimes it goes up.The first eight pages for a structure are allocated from mixed extents (unless trace flag 1118 is in effect). This means that the first eight pages will generally be logically fragmented.
Once a structure (e.g. index or table) grows beyond eight pages, SQL Server only allocates uniform extents - even if the structure subsequently occupies less than eight pages.
Rebuilding the clustered index once a structure occupies more than eight pages will remove the data fragmentation. A similar consideration applies to indexes.
You can check whether a structure is using mixed extents using the DBCC EXTINFO command:
DBCC EXTINFO(database_id, table_name, index_name);
An ext_size of 1 indicates a page allocated from a mixed extent.
Finally, I would just say that you should really only be concerned about fragmentation on tables which are scanned (especially if read-ahead is used). Fragmentation has no impact on singleton selects (aside from concerns related to page fullness). Read-ahead is unlikely to occur on structures less than 500 or so pages (I forget the exact threshold).
Paul
P.S. If you are really interested in looking at the index structures in detail, you can use DBCC IND to list the pages, and DBCC PAGE to examine them.
Yeah, I figured it out. Had to pull out my notes from dba class.....
I guess the only thing left is to determine whether or not there is any reason to even run the alter index rebuild/reorganzie on objects where the page_count value is less than 8? It runs really fast for the smaller ones, but is there any value?
What are your thoughts on that?
June 13, 2009 at 6:07 pm
rob.patterson (6/13/2009)
I guess the only thing left is to determine whether or not there is any reason to even run the alter index rebuild/reorganzie on objects where the page_count value is less than 8? It runs really fast for the smaller ones, but is there any value?
There's really no value for structures allocated entirely from mixed extents. There may be a small benefit from rebuilding a structure which has grown beyond eight pages since uniform extents will be allocated instead of mixed, but the difference will be extremely marginal.
Personally, I only worry about fragmentation for large structures which are scanned or for which read-ahead is used for key lookups for example.
On the other hand, since the operation is so fast, it may be a moot point for you - one could argue that it is more effort to exclude some structures from the process than to simply rebuild everything.
By the way, reorganize will only shuffle data between existing pages and extents - it never does new allocations (except for the one work page it needs).
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 14, 2009 at 6:28 pm
Thanks for your help, good information! Hopefully I can return the favor sometime.
June 14, 2009 at 6:38 pm
rob.patterson (6/14/2009)
Thanks for your help, good information! Hopefully I can return the favor sometime.
Glad to help. See you around the site. 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply