Elisabeth Rédei (11/7/2008)
Hi,I can see how it would be true for a handful of pages (less than 8) but since the index is created from scratch, I for one cannot see why the pages would be allocated from all over the place.
You mentioned it happens for ALL tables (with more or equal than 3 levels) and always index level 2. Is it so that if there are more levels, it is still just level 2? I suppose some of them have clustered indexes and some are heaps, or?
I am still very puzzled, do you get the same result if you do CREATE INDEX .... WITH DROP_EXISTING?
Which service pack level are you on?
/Elisabeth
I know - it was a bit surprising for me too. But Paul Randal was once in charge of the team that wrote all of this stuff (as in - the DB engine portion of SQL Server), so he's been a good resource. His point was - they're so small, the fragmentation doesn't affect performance (keep in mind 1,000 pages = 8K * 1000 or roughly 8MB, so we are talking small).
Also - from the "Reorganizing and rebuilding indexes" topic in BOL:
In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index. For more information about mixed extents, see Understanding Pages and Extents.
(the link to that is http://msdn.microsoft.com/en-us/library/ms189858.aspx)
Still - the only way to really know is to try it for yourself.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?