May 31, 2016 at 1:17 pm
Andre 425568 (5/26/2016)
I got fragmentation stats after the Table rebuild and then after index rebuild. Still had a lot of fragmentation.
I believe what Jeff Moden was trying to say is that this is an expected outcome if the indexes are small.
From MSDN:
"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."
May 31, 2016 at 1:34 pm
cyp901 (5/31/2016)
Andre 425568 (5/26/2016)
I got fragmentation stats after the Table rebuild and then after index rebuild. Still had a lot of fragmentation.I believe what Jeff Moden was trying to say is that this is an expected outcome if the indexes are small.
From MSDN:
"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."
As a work around, you can add dummy rows to the table until it occupies at least 8 pages of space, at which point SQL will switch from mixed extents to uniform extents. Then delete the extra rows. Once a table uses uniform extents, it does not go back to using mixed extents, even if it shrinks back in size to under 8 pages (at least afaik SQL still works that way).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 31, 2016 at 2:40 pm
ScottPletcher (5/31/2016)
As a work around, you can add dummy rows to the table until it occupies at least 8 pages of space, at which point SQL will switch from mixed extents to uniform extents. Then delete the extra rows. Once a table uses uniform extents, it does not go back to using mixed extents, even if it shrinks back in size to under 8 pages (at least afaik SQL still works that way).
Interesting... I was not aware of that but it makes sense.
To clarify for [Andre 425568]... You seem very determined to get rid of fragmentation, with that being said, I would not go down this road every time you create a new table. 🙂
June 1, 2016 at 7:49 am
I am determined to get fragmentation down, with some tables still over 70 % fragmented I need to do something, as stated previously the company is still fighting for some reason against Clustered Indexes. That is a fight for another day.
Table rebuilds are really great as a start, even with the index stats and execution plans the first jun of a job came down from 4.5 hours to 3.5 hours. I am hoping it will come down further
June 1, 2016 at 7:53 am
Scoot, thanks, I will investigate what you said
June 1, 2016 at 8:01 am
The best clustered index is almost always the single most critical performance factor for a table. You could gain some mild efficiencies looking at other things, but a far, far bigger improvement would come from clustering the tables properly.
Edit: I understand that you are restricted to what you can do, and it's not your choice. But whoever administers the databases needs to learn about the importance of clustering in table performance, esp. for SQL Server.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 3, 2016 at 2:19 am
Thanks to all the guys for their input, Looking at each of them, and I definitely have a lot to work towards, step by step though
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply