April 25, 2013 at 1:16 am
Hi all.
I found there was fragmentation in my server and i rebuild the indexes to remove fragmentation. But even after rebuilding the indexes the fragmentation is still there. How to resolve this issue.
April 25, 2013 at 1:38 am
SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC
I used this script to found Fragmentation before and after rebuilding indexes.
April 25, 2013 at 1:53 am
m.rajesh.uk (4/25/2013)
Hi all.I found there was fragmentation in my server and i rebuild the indexes to remove fragmentation. But even after rebuilding the indexes the fragmentation is still there. How to resolve this issue.
Hi
That means that some of your indexes get fragmented very fast. You'd better decrease the FillFactor for those indexes.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
April 25, 2013 at 1:58 am
m.rajesh.uk (4/25/2013)
Hi all.I found there was fragmentation in my server and i rebuild the indexes to remove fragmentation. But even after rebuilding the indexes the fragmentation is still there. How to resolve this issue.
If the indexes that remain fragmented only have a small number of rows, don't worry. Fragmentation only starts to be a problem when your index is between, say, a few hundred and a thousand pages in size. Don't go changing your fill factors unless you fully understand what you're doing.
John
April 25, 2013 at 2:20 am
Thanks for your suggestions
April 25, 2013 at 2:23 am
m.rajesh.uk (4/25/2013)
I found there was fragmentation in my server and i rebuild the indexes to remove fragmentation. But even after rebuilding the indexes the fragmentation is still there. How to resolve this issue.
Probably by ignoring those indexes completely. If I had to guess, I'd say they were under 50 pages in size. About right?
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
April 25, 2013 at 2:33 am
IgorMi (4/25/2013)
m.rajesh.uk (4/25/2013)
Hi all.I found there was fragmentation in my server and i rebuild the indexes to remove fragmentation. But even after rebuilding the indexes the fragmentation is still there. How to resolve this issue.
Hi
That means that some of your indexes get fragmented very fast. You'd better decrease the FillFactor for those indexes.
Regards
IgorMi
Hi. Yes, I forgot to suggest to pay attention on bigger indexes. Decreasing of FillFactor depends and may affect other stuff. However changing it from 100% to 95% will change the index's size for about 5% and help you not having higher fragmentation.
If a 5% change does not resolve your issue, then for decreasing FF still lower you'll have to compare the fragmented part size with the index's size increase at least and consider other aspects of it. You can read some good articles of fragmentation on this site.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
April 25, 2013 at 3:28 am
yes Gail, mostly less than 100 pages .can you explain what it actually mean. rebuilding indexes are not effective for indexes with less number of pages.
April 25, 2013 at 4:42 am
Not worth while, mostly a waste of time. Fragmentation affects large range scans, you can't have large range scan on a table with 100 pages.
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
April 25, 2013 at 5:21 am
Thanks for information. I got the point.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply