March 12, 2009 at 9:16 am
Hi,
A quick question:-
Besides, by looking upon 'avg_fragmentation_in_percent' in sys.dm_db_index_physical_stats function, what are the other columns or factors to determine that index is fragmented. i know if the index size is too small...rebuild or reorganize wont work even though the fragmentation level is greater than 10%. Then what is the maximum size the index has to reach in order to be rebuilt/reorganized?
Also I read in one of the forums here ( in sqlservercentral) that if the index has upto 1000 pages, rebuild/reorganize wont work..
in order to rebuild/reorganize are these factors together have to be true?
Thanks
Joseph
March 12, 2009 at 9:21 am
MS says that less than 1000 pages defragging won't help... that might be true, but if it's less than 8 pages, you can't defrag at all. Setting a fragmentation percentage and making sure that the index is over 8 pages are the two measures I use.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 12, 2009 at 11:02 am
Another thing I use in addition to page information provided by Grant is .. avg_fragmentation levels. 1-25% re-org, 25% rebuild.
But again rebuild I only consider on very large tables.
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]
March 12, 2009 at 11:13 am
Hi Mohit,
Can you please mention from which column in sys.dm_db_index_physical_stats functions we get that information the one you mentioned (avg fragmentation level...)
March 12, 2009 at 11:16 am
ok Mohit, i think I got it what you mean...the column 'avg_fragmentation_in_percent' right?....but other than this and index page number, is there any third factor to determine fragment level of index
March 12, 2009 at 11:42 am
Table Size / Avg Index Fragmentation Percentage are two main ones.
The other ones to look at is Fill Factor & Pad Index; you should look at how your indexes are performing. Aka if your Avg Index Fragmentation Percentage goes from less then 5% to over 30% in one day you'll have to check out fill factor and see if it needs changing.
I would start with table size/avg index fragmentation to start with :).
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 12, 2009 at 12:20 pm
ok ...but can you please specify the term 'avg index fragmentation percent'..let say how do we find when we run as follows:
select * FROM sys.dm_db_index_physical_stats(db_id(),object_id,index_id,NULL,NULL)....
can you please clarify regarding table size...like normally how much big it has to go in order to consider defragmentation.
March 12, 2009 at 1:09 pm
I execute:
SELECT * FROM sys.dm_db_index_physical_stats(db_id, NULL, NULL,NULL,'LIMITED')
This gives me information for a Single Database, all tables, and all indexes. The 'LIMITED' gives be very brief information about Index depth, avg_fragmentation_in_percentage, and page_count.
BOL state that if avg_fragmentation_in_percentage is 30%+ you should rebuild if it is less then 30% you should reorganize. When you reorganize an index on the leaf node pages get adjusted and fragmentation fixed. When you rebuild the index, it destorys the index and rebuilds it making sure all pages go in proper order. You can do either operation to both Clustered or Non-Clustered Indexes. When you work on a non-cluster index it only affects that index; but when you change clustered index it rebuilds the entire cluster index and all of the non-cluster indexes also. While the index rebuild is taking place on cluster index the table becomes inaccessible. In SQL Server Enterprise Edition you can do index rebuilds online.
BOL provide a script on rebuilding/reorganizing index based on that percentage, ref link.
If the number of pages in an index is less then 8 you will never eliminate the index. Other I base my decision on how active the index; aka how quickly it becomes fragmented because of day-to-day activity. The reason you have to take table size into consideration is because the larger the table the longer it will take to rebuild the index. So for larger tables which usually have lots of history and very few active records you don't want to rebuild everyday.
I hope that helps a bit... Grant is alot better at this then me :P.
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 12, 2009 at 3:34 pm
thanks Mohit...thanks much for sharing thoughts..
one final little question....you mentioned index_depth...what will be be decision point whther index is fragmented in terms of index_depth...
thanks
Joseph
March 12, 2009 at 3:39 pm
I am not sure what you are asking? Are you wondering can Index be fragmented at high depths? Yes, but it will not show affect unless it is on a large table. On a large table where the fragmentation is on higher levels you'll notice after re-org of the indexes with alter index it doesn't eliminate the fragmentation.
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 12, 2009 at 3:52 pm
You should also look at avg_fragment_size_in_pages - the higher the better. Because SQL reads extents (8 pages) a low value can indicate that more extents are being read than necessary, especially with scans.
March 12, 2009 at 4:22 pm
hi Mohit - in your previous reply you mentioned index_depth some part of your previous reply:-
I execute:
SELECT * FROM sys.dm_db_index_physical_stats(db_id, NULL, NULL,NULL,'LIMITED')
This gives me information for a Single Database, all tables, and all indexes. The 'LIMITED' gives be very brief information about Index depth, avg_fragmentation_in_percentage, and page_count.
Hi Matt - i checked most of the indexes, the avg_fragment_size_in_pages is 1, 1.5,1.383 like this.. can you tell me what does that mean...is there any certain point to which avg_fragment_size_in_pages values should come up?
March 12, 2009 at 5:06 pm
index_depth is not an indicate on how fragementation it is an indication on how big is your index. An index is a b+ tree. So if you have an index that is dept of 2 that is implying following style stucture like in the image in http://en.wikipedia.org/wiki/B%2B_tree site :).
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 13, 2009 at 8:51 am
we only do index rebuilds, this way we don't have to run update statistics
March 13, 2009 at 9:37 am
Index rebuild is a very expensive operation; on small databases it is fine. On larger databases doing full index rebuild can take very long time. So unless you have huge mainteance window it wouldn't be adviced. Thanks.
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 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply