January 7, 2011 at 7:57 am
I have been given the task to attempt to come up with a way to evaluate the need for selective reindexing / reorg for our SAP system SQL 2008 databases.
I have been reviewing the above DMV and I am a little confused based on how I should be evaluating the output.
I have been looking at a variety of user created scripts on SSC and still am unsure how things would work conceptually.
For instance...I run the DMV for one specific table in all three modes (Limited, Sampled, Detailed).
For Limited and Sampled I get two rows back and for Detailed I get 5 rows back.
The table in question has 1 index Clustered index.
The Limited and Sampled pull back stats for two allocation unit types:
IN_ROW_DATA (Index Level = 0 and Index Depth = 4) with average_fragmentation in percent = 6.xxx
LOB_DATA (Index Level = 0 and Index Depth = 1) with average_fragmentation in percent = 0
The Detailed pull back stats for the same two allocation units but this time it is a lot more information which is the confusing part for me at the moment.
IN_ROW_DATA (Index Level = 0 and Index Depth = 4) with average_fragmentation in percent = 6.xxx
IN_ROW_DATA (Index Level = 1 and Index Depth = 4) with average_fragmentation in percent = 86.xxx
IN_ROW_DATA (Index Level = 2 and Index Depth = 4) with average_fragmentation in percent = 83.xxx
IN_ROW_DATA (Index Level = 3 and Index Depth = 4) with average_fragmentation in percent = 0
LOB_DATA (Index Level = 0 and Index Depth = 1) with average_fragmentation in percent = 0
SO...if I am dumping all the DMV stats to a table for evaluation...how am I supposed to determine WHAT to do here...
My thought was get the schema, table, and index name for the particular row...if it meets certain parameters then rebuild or re-org.
But since it is only 1 index my initial thought is it would be processed up to 5 times in DETAILED mode which is obviously something I don't want.
I started reading the technical documentation:
http://msdn.microsoft.com/en-us/library/ms189051.aspx
and
http://msdn.microsoft.com/en-us/library/ms188917%28v=sql.90%29.aspx
but it just is not clicking at the moment.
For reference the one table I am referring to above has the following schema and PK
Column_nameTypeComputedLength
PROGNAMEnvarcharno80
DYNPNUMBERnvarcharno8
R3STATEnvarcharno2
FIELDINFOvarbinaryno-1
LOGICINFOvarbinaryno-1
EXTENSIONSvarbinaryno-1
index_nameindex_descriptionindex_keys
CI_Index~0clustered, unique, primary key located on PRIMARYPROGNAME, DYNPNUMBER, R3STATE
Any help on pointing me to the correct direction would greatly be appreciated.
Lee
January 7, 2011 at 8:25 am
OK does this make sense...I was just reading a wonderful article by Gail Shaw (Thanks for the article)...
http://www.sqlservercentral.com/articles/Indexing/68439/
This is probably going to sound nice and stupid but is the following a good assumption?
Since I ran the first two scans in (Limited and Sampled) it scans the index but doesn't go to the leaf level of the index. The table in question has a page count for the IN_ROW_DATA of 132955 and a page count for LOB_DATA of 1445895
The LIMITED mode is the fastest and scans the smallest number of pages. It scans all pages for a heap, but only the parent-level pages for an index, which are the pages above the leaf-level.
The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.
As a result it shows very low fragmentation and if I were running a script with logic (fragmentation > xxx) it would pass over this index.
When running the detailed it shows all the levels of the index 0, 1, 2, 3.
The first and second levels of this particular index appear to have very high levels of fragmentation (80% plus)...
In this example would you query your output table to say something like:
return all indexes and their max fragmentation level and then evaluate just the singular value? i.e.
SELECTobject_id,
MAX(avg_fragmentation_in_percent)
FROMMyTable
WHEREMAX(avg_fragmentation_in_percent) > 30
GROUP BY object_id
Something like this would hopefully return only 1 row for the above table and it would be:
object_idfragmentation%
82950628486.2069
This would indicate that the index(s) on the table above would require a rebuild
Am I off base?
January 7, 2011 at 9:16 am
In general, in most cases, worry about the leaf level and no other. The higher levels of the index are generally not large enough for fragmentation to matter, and they're far more likely to be in memory where fragmentation doesn't matter.
If you have specific cases (huge indexes) that are exceptions, treat them separately.
SELECT object_id, avg_fragmentation_in_percent
FROM MyTable
WHERE avg_fragmentation_in_percent > 30 and page_count > 1000 and index_level = 0 and alloc_unit_type_desc = 'IN_ROW_DATA'
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
January 7, 2011 at 9:18 am
p.s. There are several very good index rebuild scripts already written and available for use. Don't reinvent the wheel.
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
January 7, 2011 at 9:45 am
GilaMonster (1/7/2011)
In general, in most cases, worry about the leaf level and no other. The higher levels of the index are generally not large enough for fragmentation to matter, and they're far more likely to be in memory where fragmentation doesn't matter.If you have specific cases (huge indexes) that are exceptions, treat them separately.
SELECT object_id, avg_fragmentation_in_percentFROM MyTable
WHERE avg_fragmentation_in_percent > 30 and page_count > 1000 and index_level = 0 and alloc_unit_type_desc = 'IN_ROW_DATA'
In the example I gave above the largest table currently in this database would be skipped over based on the above filtering criteria...
What is to be made of the 'intermediate' levels of the clustered index that have very high fragmentation?
Is there a criteria that you review to say that 'level' #2 has high fragmentation but because of xxx and xxx we shouldn't be concerned
vs.
level #3 of this index has xxx fragmentation and because of xxx and xxx this index should be considered for a rebuild EVEN though the 0 level of the index shows only 6% fragmentation.
GilaMonster (1/7/2011)
p.s. There are several very good index rebuild scripts already written and available for use. Don't reinvent the wheel.
Believe me I am not trying to write something from scratch but when you search 'index rebuild' and see 275 results come back it is a little time consuming to determine which is good and which is not good...
it would be helpful if the search criteria allowed to sort based on views / ratings / etc...
Also if you have a recommendation and have a moment please feel free to point me in that direction I would greatly appreciate it.
Thanks again for the article and the reply,
Lee
January 7, 2011 at 2:29 pm
TalkToLee (1/7/2011)
Is there a criteria that you review to say that 'level' #2 has high fragmentation but because of xxx and xxx we shouldn't be concernedvs.
level #3 of this index has xxx fragmentation and because of xxx and xxx this index should be considered for a rebuild EVEN though the 0 level of the index shows only 6% fragmentation.
I did mention...
In general, in most cases, worry about the leaf level and no other. The higher levels of the index are generally not large enough for fragmentation to matter, and they're far more likely to be in memory where fragmentation doesn't matter.
Typically the aprox threshold where you start worrying about rebuilding is somewhere around 1000 pages
Also if you have a recommendation and have a moment please feel free to point me in that direction I would greatly appreciate it.
I gave one
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
January 7, 2011 at 2:36 pm
Hi Gail,
I don't want to sound like I am beating a dead horse and I did read your reply...if that is common knowledge I didn't know.
Your comment
The higher levels of the index are generally not large enough for fragmentation to matter, and they're far more likely to be in memory where fragmentation doesn't matter.
Make sense but how would you determine the threshold of 'large enough' a ratio?
The table I focused on initially was 12 gb's in size, has 132955 pages...
I am not trying to be critical of your comments and hope it doesn't sound that way...I guess I am trying to figure out the 'rule of thumb' when digging through the results.
January 7, 2011 at 2:39 pm
TalkToLee (1/7/2011)
The higher levels of the index are generally not large enough for fragmentation to matter, and they're far more likely to be in memory where fragmentation doesn't matter.
Make sense but how would you determine the threshold of 'large enough' a ratio?
From previous post:
Typically the aprox threshold where you start worrying about rebuilding is somewhere around 1000 pages
1000 pages is a rough estimate given by one of the people that wrote the storage engine code for the aprox size of an index level where fragmentation starts to have an effect on scan performance.
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
January 14, 2011 at 8:31 am
Hi Gail,
Just wanted to stop back and thank you for the link and information.
I got that script working, ran it and have been studying the logic in it to get a better understanding of the whole process.
Thanks for taking the time to post back for me.
Lee
January 14, 2011 at 8:35 am
Pleasure.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply