Kalen Delaney : fragmentation

Geek City: Fragmentation and Forwarded Records in a Heap

Kalen Delaney — Thu, 12 Nov 2009 01:31:00 GMT

I know it's been a while since I had a technical post, and I've got 3 of them started and saved as drafts. I keep waiting for spare time. Now that PASS is over, and I've crawled partway out from under the TO-DO pile that accumulated while I was preparing...(read more)

Geek City: Fragmentation Internals

Kalen Delaney — Thu, 06 Mar 2008 21:42:00 GMT

Yet another fragmentation post, to answer a question asked in response to a previous answer....

SQL_Girl is still confused about DETAILED vs LIMITED, and I don't blame her. She reported that the BOL says: "The nonleaf levels of indexes are only processed when mode = DETAILED. " But I had said: "The second misunderstanding involves what LIMITED means. LIMITED does not ONLY show the leaf level, in fact, just the opposite. LIMITED does not evaluate pages at the leaf level at all."

So my answer actual was partly incorrect, and also partly just a confusion about what "processed" or "evaluated" means.

I was incorrect in implying that LIMITED gave you everything but the leaf. It only displays information for the leaf level. Thanks to SQL_Girl for keeping me honest here.

However, what exactly does "processed" mean? Most of the referenced post was about logical fragmentation as reported in the column avg_fragmentation_in_percent. Logical fragmentation is a measure of how 'out of order' the pages are. How can SQL Server determine whether the pages are out of order or not?

Below is a graphic that I use in my classes when discussing index structures. In the graphic, the leaf level has no logical fragmentation because the logical order, from values 1 to 80,000 is the same as the physical page numbers, 1151 to 2150. SQL Server can determine this without actually touching the leaf level at all. This information is available at Level 1. The index pages at level 1 have a pointer to every single page at the leaf level. Each index row at level 1 includes the first key value on a page at level 0, and then the file number and page number which start with that key value. When determining the value for avg_fragmentation_in_percent, SQL Server can just look at the index pages at level 1 and see the page numbers in order (all on file 1): 1151, 1152, 1153....2150. So it reports that at level 0 there is a 0 value for avg_fragmentation_in_percent (i.e. NO fragmentation). If there was fragmentation, SQL Server could determine that by looking at the level above the leaf. For LIMITED, that is basically all SQL Server does...it looks at the level above the leaf (level 1) to report whatever it can about the leaf level (level 0).

When I mentioned the term "processed" I was talking about all the other work that SQL Server does in DETAILED mode. It figures out the values for min, max and avg record size, ghost and forwarded record counts, and the value for avg_page_space_used_in_percent. It cannot get these values without actually examining every single page at the leaf level and in LIMITED mode it does not do that. It stays at the level above the leaf and does what it can from there.

So, to summarize:

In LIMITED mode, SQL Server scans only level 1 and reports the logical fragmentation of level 0.

In DETAILED mode, SQL Server scans every level, and reports on all kinds of stuff.

This is still just a summary, because there is all kinds of other information, such as when you have LOB and row-overflow data, etc. But that's for another time.

Have fun!

~Kalen

Did You Know? Fragmentation, revisited....

Kalen Delaney — Fri, 29 Feb 2008 01:00:00 GMT

A couple of questions arrived regarding my earlier post about fragmentation on the system tables, and since the questions really don't have anything to do with system tables, I felt they deserved a new post.

1) Using the DETAILED option includes the non-zero index-levels as well (non-leaf pages). According to many source, this should also be actioned, seeing that this indicates "logical" fragmentation (meaning that the leaf pages are not in the order of the non-leaf keys any more). I cannot find anything conclusive on this. Where as the index level=0 occurrences, indicates extent fragmentation. We are using this dmv, to check fragmentation-levels first, and based on that, do the necessary re-index/reorg. Currently we are using the LIMITED option (which only shows leaf-level), but we seem to be missing indexes that needs to be re-indexed/re-orged.

There are two misunderstandings reflected in this question. Although there are several types of fragmentation, the value in the avg_fragmentation_in_percent represents only logical fragmentation, in any level of any index. Yes, there can be logical fragmentation in the upper levels, which may need to be addressed in some very large tables. Extent fragmentation is only reported for heaps, and once you have a clustered index, the table itself is just the leaf level of the index. So basically, there is no difference between the meaning of the avg_fragmentation_in_percent value no matter what level you are at. However, fragmentation is really only an issue if there are LOTS of pages. I usually say that if there are less than 100 pages, you should never worry about fragmentation. However, this this whitepaper suggests that a better cutoff would actually be 1000 pages:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

It would need to be a very big table to have upper levels of indexes with more than 1000 pages, and the sys.dm_db_index_physical_stats view does show you the number of pages at each level.

The second misunderstanding involves what LIMITED means. LIMITED does not ONLY show leaf level, in fact, just the opposite. LIMITED does not evaluate pages at the leaf level at all. It can still determine the logical fragmentation because of the pointers in the upper levels, but LIMITED will return no values that require actually examining the leaf level rows. This includes values like min, max and avg record size, ghost and forwarded records counts, and the value for avg_page_space_used_in_percent. If you just want to see logical fragmentation to know if defrag or reorg might help, LIMITED should be fine.

2) Secondly, the stats that this function return, does not seem to be reliable. I ran the function prior to an ALTER…. REBUILD, and then again thereafter. There were hardly any differences in the stats. Is it now a case of the stats being unreliable, or the REBUILD not being effective?

REBUILD doesn't seem to make a difference in the avg_fragmentation_in_percent value is when there are too few pages in the table.

Geek City: Fragmentation on the System Tables

Kalen Delaney — Fri, 18 Jan 2008 01:05:00 GMT

It's my first post of the new year. I hope it's starting out well for all of you! New year, but sometimes the same old questions. I got another email asking about defragging the system tables. It seems to be in the Hit Parade of FAQs.

First of all, WHY do you think you would need to defrag a system table? Fragmentation is only a problem when you are performing an in-order scan on a table of more than 100 pages or so, and how often do you do that to a system table that is that big? Rarely, I would assume, but let me know if you have seen an actual need for defragging a system table.

In SQL Server 2000, you can actually use a system table as a parameter to DBCC SHOWCONTIG to see the fragmentation values. One of my readers reported that he tried running a DBCC INDEXDEFRAG on SQL 2000 system tables and while sometimes it worked, sometimes it corrupted the table! It doesn't sound worth it to me.

And now, with SQL Server 2005 there is no way to see the fragmentation on the system tables. What about using the new Dynamic Management Object sys.dm_db_index_physical_stats? It requires an object_id as a parameter, and if you select from the metadata view sys.all_objects you can see the object_id for the real system tables, even if you're not using the DAC. Try this:

select * from sys.all_objects
where type = 'S'

You should see all the system tables in your current database, and their object IDs. So you could try passing the id to the management view. Here I'll try it for syshobts, which has an ID of 15:

select * from
sys.dm_db_index_physical_stats(1, 15, null, null, 'detailed')

No results are returned. If I replace the 15 with null, to indicate I want all objects, I do not see any objects with IDs less than 100, which means there are no system objects reported.

I did notice one interesting behavior while playing around with this concept. If I use the old DBCC SHOWCONTIG and try to get a report for a system table, this command gives me an error:

dbcc showcontig ('syshobts')

Msg 2501, Level 16, State 45, Line 1
Cannot find a table or object with the name "syshobts". Check the system catalog.

However, if I qualify the table name with the schema name, something different happens.

dbcc showcontig ('sys.syshobts')

This time, I don't get an error. I don't get a fragmentation report, but I don't get an error. I just get the message that DBCC execution completed. So it's obvious that there is a difference here. If I then intentionally misspell the object name

dbcc showcontig ('sys.syshobbits')

now I get the error that SQL Server cannot find the object. So it seems that in SQL Server 2005, DBCC SHOWCONTIG is deliberately filtering out the real system tables, and it was by design to not return fragmentation information. So even if you could run defrag on your system tables, you could never know what impact you had!

I think there's better ways to spend our time.

Have fun!

~Kalen