Index rebuild and fragmentation level

Question

Index rebuild and fragmentation level

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

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?

MW-309385 Old Hand Points: 341 More actions · Answer 2

Here is a question to the SQL_DBA who presented us with how to run the DBCC command.

As you know there are 2 types of fragmentation.

1. External - results when SQl server has to produce a new page and store it some where (without storing it adjacent to the other pages )

2. Internal - Pages are not full as a result of Page Splitting.

Now, if I run the DBCC command, what info represents what ?

I am not sure what logical fragmentation is ? Can you explain ?

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 3

OK, let's find out what it really looks like; you can run DBCC IND on the index in question:

dbcc ind ( { 'dbname' | dbid }, { 'objname' | objid }, { nonclustered indid | 1 | 0 | -1 | -2 } [, partition_number] )

There will be a row for every page in the index so you might not want to take your largest index.

Also, even though PAD_INDEX is depending on FILLFACTOR (and that doesn't seem to be set), check anyway:

SELECT object_name(object_id), index_id, fill_factor, is_padded

FROM sys.indexes

WHERE object_id = sometable

AND index_id = someindex

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 4

LOGICAL FRAGMENTATION:-It is the level of fragmentation in your leaf nodes. Why does this happen?

The reason is when ever you insert or delete a row from your table the indexes assign the space as when it is available. If the page is full (fill factor 100%, Scan Density 100%) every new row has to be put in the next page, thereby page splits occur. This would kill the performance.Also, the upper levels of a well-used index with a decent fanout are typically memory resident and so do not incur IO costs - the IO cost comes from reading the leaf-level page.

- having interleaved index and data pages in extents will impact the ability of the readahead code to generate multi-page contiguous IOs, but again, that's a negligible cause compared to page fragmentation

- readahead is driven from the level above the leaf level

EXTENT FRAGMENTATION:- This refers to the fragmentation of the actual physical location of extents/pages within Sql Server data file(s) - it's typically called extent fragmentation due to the fact that extents are the primary allocation unit for Sql Server data, and the majority of the time allocation of space within a file will occur as a full extent, which is made up of 8 contiguous pages. Therefore, arguably the majority of the time this type of fragmentation manifests itself as interleaved extents that are allocated to different structures and the pages within each of these extents are mostly contiguous.

REASON:It occurs when data is being inserted into the database within multiple different tables, and extents are being allocated for these insertions within a given data file in a round-robin-ish fashion (i.e. extent #1 goes to index #1, extent #2 goes to index #2, extent #3 goes to index #3, then extent #4 goes to index #1, then extent #5 goes to index #3, etc., etc.). This results in properly ordered pages for the given indexes (i.e. no logical fragmentation), but not in contiguous pages, since the pages are "interleaved" with each other by nature of the extents being interleaved (you end up with 8 contiguous pages, then an extent for another object(s), then 8 more contiguous pages, etc., etc.,

Hope this helps..

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 5

I do know from extensive testing, that reorging an index of less than 1000 pages is pointless, but rebuilding will show reduced fragmentation

I'm curious.

From the DMV, what's the value of avg_fragment_size_in_pages ?

I think what we might have here is that the act of rebuilding the index has caused the mid-tier leaves to fragment... the mid-tier is the index of the leaf level pages (technically) so it makes sense that as the leaf level is "re-inserted" randomly that the mid-tier would get fragmented badly.

If the leaf-level pages were written sequentially, it would be a different story.

Just like Elisabeth Rédei noticed, You might also try rebuilding with pad_index in addition to fill factor, since pad_index is the "fill factor" the intermediate leaves... padding your index will reduce the number of page splits and thus extent fragmentation.

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Here's a way to test it:

create table fragtable (gid uniqueidentifier default newid() primary key clustered, fun int)

create index bob on fragtable(fun)

go

insert fragtable(fun)

select rand(checksum(newid()))*75

from sys.all_columns

go 30

dbcc showcontig('fragtable')

ALTER INDEX bob on fragtable rebuild

go 30

If you notice - the non-clustered index ends up fragmented, and essentially doesn't change, no matter how many times you rebuild it. Adding in the fillfactor doesn't seem to help.

Again - fragmentation per se isn't the problem. It's only when the fragmentation starts affecting performance. Just like DisKeeper and your disk degramenters never entirely remove fragmentation from a disk drive, the goal here is only to improve performance, so some fragmentation will remain (and quite a bit on small items).

----------------------------------------------------------------------------------
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?

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 7

Hi,

DBCC IND will reveal whether interleaving is happening but then it doesn't make sense that it is only the pages for the intermediate level that gets spread all over the place (according to earlier post it appears to happen on Level 2 only).

A bit further down in this blog "Extent Usage and Behaviours when using DBREINDEX and SHRINKFILE",

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/22/956.aspxhttp://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/22/956.aspx

there are scripts to find out whether this is happening if you don't want to use DBCC IND.

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 8

Matt Miller (11/7/2008)
Here's a way to test it:
create table fragtable (gid uniqueidentifier default newid() primary key clustered, fun int)
create index bob on fragtable(fun)
go
insert fragtable(fun)
select rand(checksum(newid()))*75
from sys.all_columns
go 30
dbcc showcontig('fragtable')
ALTER INDEX bob on fragtable rebuild
go 30
If you notice - the non-clustered index ends up fragmented, and essentially doesn't change, no matter how many times you rebuild it. Adding in the fillfactor doesn't seem to help.

I would suggest rebuilding the PK index too, then the fragmentation average of this table-based dbcc report goes down since you'll be rebuilding all the indexes on the table, rather than only one of them.

That will get it down to nothing.

Rebuilding usually works well for indexes larger than 2 extents.

I do find it oddly disturbing that someone else out there names things "bob" when testing 🙂

Evidently we're equally brain damaged!!

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

MW-309385 Old Hand Points: 341 More actions · Answer 9

Ok,

Thanks everyone.

I do have to agree with what you said.

This weekend we ran a maintenance job on all indexes and the results

are in agreement with what some of you said.

When the page count is less than 1000, SQl server takes no interest to defragment that level.

Thanks for the help.

Does anyone of you know how to paste a picture ( I mean a jpg file )?

This way, I can show you what I get as output when I check the

DMV values ?

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 10

You can upload a picture in jpg or any other format as an attachment. The link for attachment is at the very end when you are replying to a post.

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

MW-309385 Old Hand Points: 341 More actions · Answer 11

Hmm.. I see an icon with a picture but then the pciture has to be in a web site because they want us to type in a URL as a reference to the picture. So this won't work.

Next step: I will have to include an tag in my reply.

Anyone knows the code how to include a direct, path like

src="c:\picture1.jpg" in the "IF Code".

Or is there a help section in this site where I could see the syntax ?