SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sys.dm_db_index_physical_stats


sys.dm_db_index_physical_stats

Author
Message
Leeland
Leeland
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2778 Visits: 1331
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_name Type Computed Length
PROGNAME nvarchar no 80
DYNPNUMBER nvarchar no 8
R3STATE nvarchar no 2
FIELDINFO varbinary no -1
LOGICINFO varbinary no -1
EXTENSIONS varbinary no -1

index_name index_description index_keys
CI_Index~0 clustered, unique, primary key located on PRIMARY PROGNAME, DYNPNUMBER, R3STATE





Any help on pointing me to the correct direction would greatly be appreciated.

Lee
Leeland
Leeland
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2778 Visits: 1331
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.


SELECT object_id,
MAX(avg_fragmentation_in_percent)
FROM MyTable
WHERE MAX(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_id fragmentation%
829506284 86.2069



This would indicate that the index(s) on the table above would require a rebuild

Am I off base?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)

Group: General Forum Members
Points: 295845 Visits: 46685
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)

Group: General Forum Members
Points: 295845 Visits: 46685
p.s. There are several very good index rebuild scripts already written and available for use. Don't reinvent the wheel.

eg: http://www.sqlfool.com

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Leeland
Leeland
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2778 Visits: 1331
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_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'



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.

eg: http://www.sqlfool.com


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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)

Group: General Forum Members
Points: 295845 Visits: 46685
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 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.


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
http://www.sqlfool.com

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Leeland
Leeland
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2778 Visits: 1331
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)

Group: General Forum Members
Points: 295845 Visits: 46685
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Leeland
Leeland
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2778 Visits: 1331
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)SSC Guru (295K reputation)

Group: General Forum Members
Points: 295845 Visits: 46685
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search