April 23, 2012 at 9:51 am
Normally we check index fragmentation only where index_level=0 I'm wondering about the need or not to rebuild fragmented indexes where index_level is 1 or 2 and page_count is under 3000 ? the table I'm currently looking at has 67 million records but these particular indexes all show under 700,000 as record_count.
Sql 2005 64-bit Enterpirse on Server 2008 R2 64-bit 256GB memory
April 23, 2012 at 10:45 am
Indianrock (4/23/2012)
Normally we check index fragmentation only where index_level=0 I'm wondering about the need or not to rebuild fragmented indexes where index_level is 1 or 2 and page_count is under 3000 ? the table I'm currently looking at has 67 million records but these particular indexes all show under 700,000 as record_count.Sql 2005 64-bit Enterpirse on Server 2008 R2 64-bit 256GB memory
Index id = 1 means the data is stored in a clustered table, i.e. the table has a clustered index. Index id = 0 means the table has no clustered index, i.e. the table is stored as a heap. A table cannot have both an index id of 0 and 1.
Defragmenting clustered tables is a straightforward affair and there is plenty of guidance online.
Defragmenting heaps is a very different thing, and there is no clear path or guidance to defragment them as it will depend on the heap's definition as to whether you can expect to see a benefit, or whether you will make things worse. Also, defragmenting heaps is not actually supported directly in T-SQL syntax in SQL 2005. Here is a good article that describes the concern and things to watch out for if you think your heaps need to be defragmented:
A SQL Server DBA myth a day: (29/30) fixing heap fragmentation
EDIT: re: the min page count I use a threshold page count of 1000 before I will bother defragmenting an index
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2012 at 11:04 am
Maybe, but probably not. Index levels > 0 are much, much smaller than the leaf level are. They're more likely to be in memory all the time (and for pages in memory fragmentation is irrelevant).
If you have absolutely massively huge tables that get massive range scans, then maybe fragmentation of the intermediate levels is a problem, but the table and the scan need to be huge enough that SQL needs to issue read-aheads on the intermediate level index pages as well as at the leaf. I don't have a fixed number here, but it's really the massively huge tables. If the number of pages at the intermediate level is under 1000 I wouldn't worry at all.
p.s. 70 million rows is kinda large, certainly not huge.
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
April 23, 2012 at 11:26 am
I think I misunderstood the intent of your question a bit. I think Gail is a lot closer to what you were asking but now I am curious...are you seeing fragmentation in index_level 1 and 2 that you think would warrant a reorg/rebuild where there is not enough fragmentation in index_level 0 to warrant a reorg/rebuild?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2012 at 12:06 pm
We already handle the index_level=0 indexes in a rebuild/reorganize stored procedure. If the index_level is not zero we've been ignoring them. A new DBA is reporting higher than 30% fragmentation in quite a few indexes, but from what I can see those are mostly index_level 1 or 2 and often in smaller tables. Can't hurt to rebuild those too I suppose, but with 256GB of memory ( currently 184GB maximum for sql ) it may not help much. That memory figure will double in the near future.
April 23, 2012 at 12:16 pm
On your 67 million row table what is the page count and frag % at index level 1? and 2?
Do you have pad_index set and fillfactor set to a # lower than 100 (and not 0)? If you want to try adjusting things to avoid fragmentation in those upper levels you could consider changing those settings while rebuilding.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2012 at 12:34 pm
page count on those indexes varies from 5 up to 2700, fragmentation from 44-86% and all are index_level 1 or 2 in the report I'm talking about. When I check the same table on a copy of production taken the day before reindexing occurs, the index_level=0 indexes all show under 10% fragmentation.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply