Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sys.dm_db_index_physical_stats Expand / Collapse
Author
Message
Posted Friday, January 7, 2011 7:57 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:16 PM
Points: 417, Visits: 1,091
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
Post #1044438
Posted Friday, January 7, 2011 8:25 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:16 PM
Points: 417, Visits: 1,091
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?
Post #1044454
Posted Friday, January 7, 2011 9:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
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 2008, MVP
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

Post #1044487
Posted Friday, January 7, 2011 9:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
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 2008, MVP
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

Post #1044489
Posted Friday, January 7, 2011 9:45 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:16 PM
Points: 417, Visits: 1,091
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
Post #1044520
Posted Friday, January 7, 2011 2:29 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
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 2008, MVP
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

Post #1044730
Posted Friday, January 7, 2011 2:36 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:16 PM
Points: 417, Visits: 1,091
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.
Post #1044735
Posted Friday, January 7, 2011 2:39 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
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 2008, MVP
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

Post #1044738
Posted Friday, January 14, 2011 8:31 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:16 PM
Points: 417, Visits: 1,091
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
Post #1047957
Posted Friday, January 14, 2011 8:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
Pleasure.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1047961
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse