October 2, 2014 at 8:28 pm
When I rebuild indexes, I expected some of the smaller ones to fully defragment, but it seems that they are left untouched. Why is that? This is more of a semantic question, since I know they will generally be in memory (where fragmentation means squat) but I'm curious why REBUILD still doesn't clean up those fragments. I told it to, dang it!
-----------------------------
I enjoy queries!
October 3, 2014 at 1:46 am
How are you rebuilding the indexes?
Say for an index with 2 pages, if the rebuild does not put those two pages next to each other (for whatever reason) you'll end up with 50% fragmentation.
I generally wouldn't worry about fragmentation on indexes that have < 1000 pages.
October 3, 2014 at 12:00 pm
If an index is small it can be in mixed extents which means you'll have some fragmentation.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 3, 2014 at 12:37 pm
Jack Corbett (10/3/2014)
If an index is small it can be in mixed extents which means you'll have some fragmentation.
Why doesn't Rebuild put the index into its own extent then?
-----------------------------
I enjoy queries!
October 3, 2014 at 12:39 pm
DBA From The Cold (10/3/2014)
How are you rebuilding the indexes?Say for an index with 2 pages, if the rebuild does not put those two pages next to each other (for whatever reason) you'll end up with 50% fragmentation.
I generally wouldn't worry about fragmentation on indexes that have < 1000 pages.
I guess this is the guts of my question.
If rebuild against large indexes goes ahead and moves the index so its fragments will be in sequential pages, why don't small indexes (especially PKs) get the same treatment?
-----------------------------
I enjoy queries!
October 6, 2014 at 7:00 am
If the table itself is less than 8 pages (an extent) by default it will stay in mixed extents. See this BOL Page for how how SQL Server allocates pages. You don't really get a performance improvement in most cases by defragmenting small indexes because they are likely in memory all the time anyway, and because having mixed extents saves storage space. There is a trace flag to force SQL Server to use uniform extents, TF 1118, but it is mainly designed to help tempdb contention.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 6, 2014 at 7:05 am
why don't small indexes (especially PKs) get the same treatment?
In short, because there's no point in doing so.
There's no reason to rebuild small indexes. They won't benefit and the queries that use them won't suffer if there's excess fragmentation.
Fragmentation affects large range scans from disk. Emphasis 'large' and 'disk'
Small indexes can't have large scans running against them and will likely be in memory anyway, hence rebuilding does nothing.
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
October 6, 2014 at 7:55 am
Thanks for your responses! It may seem like minutiae, but it's things like this I need to know before building on that knowledge to something more important. 🙂
-----------------------------
I enjoy queries!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply