January 4, 2010 at 11:02 am
I have the following in my script to pull the fragmentation information.
SELECT
QUOTENAME(DB_NAME(DDIPS.[database_id])) AS [database_name]
,QUOTENAME(S.[name]) AS [schema_name]
,QUOTENAME(O.[name]) AS [table_or_view_name]
,QUOTENAME(I.[name]) AS [index_name]
,[defrag_method] =
CASE
WHEN DDIPS.[avg_fragmentation_in_percent] > 30
AND SERVERPROPERTY('Edition') IN ('Developer Edition', 'Enterprise Edition', 'Enterprise Evaluation Edition') THEN 'REBUILD'
ELSE 'REORGANIZE'
END
--,DDIPS.[database_id]
,DDIPS.[object_id]
,DDIPS.[index_id]
,DDIPS.[partition_number]
,DDIPS.[index_type_desc]
,DDIPS.[alloc_unit_type_desc]
--,DDIPS.[index_depth]
--,DDIPS.[index_level]
,DDIPS.[avg_fragmentation_in_percent]
--,DDIPS.[fragment_count]
--,DDIPS.[avg_fragment_size_in_pages]
,DDIPS.[page_count]
--,DDIPS.[avg_page_space_used_in_percent]
--,DDIPS.[record_count]
--,DDIPS.[ghost_record_count]
--,DDIPS.[version_ghost_record_count]
--,DDIPS.[min_record_size_in_bytes]
--,DDIPS.[max_record_size_in_bytes]
--,DDIPS.[avg_record_size_in_bytes]
--,DDIPS.[forwarded_record_count]
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') DDIPS
INNER JOIN [sys].[sysdatabases] SD
ON DDIPS.[database_id] = SD.[dbid]
INNER JOIN [sys].[objects] O
ON DDIPS.[object_id] = O.[object_id]
INNER JOIN [sys].[indexes] I
ON DDIPS.[index_id] = I.[index_id]
AND I.[object_id] = O.[object_id]
INNER JOIN [sys].[schemas] S
ON S.[schema_id] = O.[schema_id]
INNER JOIN [sys].[partitions] P
ON DDIPS.[object_id] = P.[object_id]
AND I.[index_id] = P.[index_id]
WHERE
DDIPS.[page_count] > 1000 -- Ignore indexes < 8000K
AND DDIPS.[avg_fragmentation_in_percent] > 5 -- Ignore indexes that have < = 5% fragmentation
AND DDIPS.[index_type_desc] IN ('CLUSTERED INDEX', 'NONCLUSTERED INDEX') -- Only get clustered and nonclustered indexes
AND I.[is_disabled] = 0 -- Only enabled indexes
AND I.[is_hypothetical] = 0 -- Only real indexes
AND O.[type_desc] = 'USER_TABLE'; -- Restrict to user tables
[HIS][dbo][ENCOUNTERS][ENC_MRN]REORGANIZE171758115711CLUSTERED INDEXIN_ROW_DATA6.4069081250486289981
[HIS][dbo][ENCOUNTERS][E_ADMIT]REORGANIZE171758115761NONCLUSTERED INDEXIN_ROW_DATA5.7332779393435821663
[HIS][dbo][ENCOUNTERS][E_DISCHARGE]REORGANIZE1717581157101NONCLUSTERED INDEXIN_ROW_DATA15.564523525635121806
[HIS][dbo][ENCOUNTERS][E_ENC_FAC]REORGANIZE1717581157111NONCLUSTERED INDEXIN_ROW_DATA15.856958494650524021
[HIS][dbo][PHYSICIANS][PHYS_FOLDER]REORGANIZE178158138511CLUSTERED INDEXIN_ROW_DATA6.48809370354138116367
[HIS][dbo][PHYSICIANS][PHYS_FOLDER]REORGANIZE178158138511CLUSTERED INDEXIN_ROW_DATA24.68168462291871021
That produces some duplicate entries. Notice the last two rows. I ran it with an "*" instead of named columns to see what was different about the appearently identical rows and saw that Level was different. Shown below.
SELECT
*
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') DDIPS
INNER JOIN [sys].[sysdatabases] SD
ON DDIPS.[database_id] = SD.[dbid]
INNER JOIN [sys].[objects] O
ON DDIPS.[object_id] = O.[object_id]
INNER JOIN [sys].[indexes] I
ON DDIPS.[index_id] = I.[index_id]
AND I.[object_id] = O.[object_id]
INNER JOIN [sys].[schemas] S
ON S.[schema_id] = O.[schema_id]
INNER JOIN [sys].[partitions] P
ON DDIPS.[object_id] = P.[object_id]
AND I.[index_id] = P.[index_id]
WHERE
DDIPS.[page_count] > 1000 -- Ignore indexes < 8000K
AND DDIPS.[avg_fragmentation_in_percent] > 5 -- Ignore indexes that have < = 5% fragmentation
AND DDIPS.[index_type_desc] IN ('CLUSTERED INDEX', 'NONCLUSTERED INDEX') -- Only get clustered and nonclustered indexes
AND I.[is_disabled] = 0 -- Only enabled indexes
AND I.[is_hypothetical] = 0 -- Only real indexes
AND O.[type_desc] = 'USER_TABLE'; -- Restrict to user tables
7171758115711CLUSTERED INDEXIN_ROW_DATA406.40690812504862666213.50660462323638998189.9138250555967308581300210219210.27NULLHIS70x01065536167782402009-07-20 14:01:11.8701900-01-01 00:00:00.000090G:\mssql\data\dv_his_data.mdf611ENCOUNTERS1717581157NULL10U USER_TABLE2000-05-09 15:59:19.1232009-10-18 17:15:43.3430001717581157ENC_MRN11CLUSTERED010009000011dbo113940383754158081717581157113940383754158083085813
7171758115761NONCLUSTERED INDEXIN_ROW_DATA405.72892623026498134016.1656716417912166288.398628613788308581350485648.24NULLHIS70x01065536167782402009-07-20 14:01:11.8701900-01-01 00:00:00.000090G:\mssql\data\dv_his_data.mdf611ENCOUNTERS1717581157NULL10U USER_TABLE2000-05-09 15:59:19.1232009-10-18 17:15:43.3430001717581157E_ADMIT62NONCLUSTERED010009000011dbo11180141325896908817175811576118014132589690883085813
71717581157101NONCLUSTERED INDEXIN_ROW_DATA4015.564523525635134386.342641070389762180687.8147022485792308581350485648.24NULLHIS70x01065536167782402009-07-20 14:01:11.8701900-01-01 00:00:00.000090G:\mssql\data\dv_his_data.mdf611ENCOUNTERS1717581157NULL10U USER_TABLE2000-05-09 15:59:19.1232009-10-18 17:15:43.3430001717581157E_DISCHARGE102NONCLUSTERED010009000011dbo112927313165811712171758115710129273131658117123085813
71717581157111NONCLUSTERED INDEXIN_ROW_DATA3015.853455453788538376.260099035704982402089.0924388435878308581300515954.147NULLHIS70x01065536167782402009-07-20 14:01:11.8701900-01-01 00:00:00.000090G:\mssql\data\dv_his_data.mdf611ENCOUNTERS1717581157NULL10U USER_TABLE2000-05-09 15:59:19.1232009-10-18 17:15:43.3430001717581157E_ENC_FAC112NONCLUSTERED110009000011dbo113208788142522368171758115711132087881425223683085813
7178158138511CLUSTERED INDEXIN_ROW_DATA406.48648648648649786114.802824068184711636589.68896713615028971747009210092.181NULLHIS70x01065536167782402009-07-20 14:01:11.8701900-01-01 00:00:00.000090G:\mssql\data\dv_his_data.mdf611PHYSICIANS1781581385NULL10U USER_TABLE2000-05-09 15:59:24.2172000-05-09 15:59:24.2170001781581385PHYS_FOLDER11CLUSTERED010009000011dbo113982326943580161781581385113982326943580168971747
7178158138511CLUSTERED INDEXIN_ROW_DATA4124.681684622918710201.00098039215686102194.41546824808511636500657365.069NULLHIS70x01065536167782402009-07-20 14:01:11.8701900-01-01 00:00:00.000090G:\mssql\data\dv_his_data.mdf611PHYSICIANS1781581385NULL10U USER_TABLE2000-05-09 15:59:24.2172000-05-09 15:59:24.2170001781581385PHYS_FOLDER11CLUSTERED010009000011dbo113982326943580161781581385113982326943580168971747
As you can maybe see from the crazy garbled text above is that there are appearantly multiple levels in this index. How do I handle that? Which fragmentation number should I use? The line showing the "0" level is at 6.49% and the line showing the "1" level is at 24.68%.
I made red the duplicate lines where it showed the index level. That is the index_level column.
January 4, 2010 at 12:45 pm
This is a matter of preference. For my preference, I would use the larger number.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 5, 2010 at 6:50 am
In most cases I have seen the recommendation is to use the 'Limited' value for sys.dm_db_index_physical_stats instead of 'Detailed'. 'Limited' returns just the Top level (0). So I'd start with just using 'Limited' so there is one row per index. If there are still issues I'd change to detailed.
Another option is to group by the common columns and take max(avg_fragmentation_in_percent)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 5, 2010 at 6:52 am
In detailed mode there's a row for each level of the index. In limited there's only a row for the leaf level (level 0).
Since the intermediate levels are much smaller than the leaf, it's the leaf level (level 0) that you need to be concerned about when doing rebuilds.
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 5, 2010 at 6:57 am
Jack Corbett (1/5/2010)
Another option is to group by the common columns and take max(avg_fragmentation_in_percent)
I wouldn't suggest that. Consider the case of an index with a 5-page intermediate level that's 80% fragmented and a 2000-page leaf level that's 2% fragmented. Rebuilding that would be a waste of time, the intermediate's too small to benefit and the leaf is not sufficiently fragmented to really bother.
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 5, 2010 at 7:01 am
Can't disagree with that, as usual, Gail. I was just presenting another option, if the OP has to use 'Detailed'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 5, 2010 at 7:18 am
If detailed has to be used, stick a where clause and filter for index level = 0
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 5, 2010 at 7:57 am
Thanks for the detailed responses. From what I have read over at http://msdn.microsoft.com/en-us/library/ms188917.aspx , and your responses, I was going to go with the WHERE index_level = 0 solution. My original intent was to be thorough with the scan and thus used DETAILED.
I guess my only question is when should I start worrying about non-leaf nodes for fragmentation? How big does it have to be?
January 5, 2010 at 8:06 am
jason.stephens (1/5/2010)
I guess my only question is when should I start worrying about non-leaf nodes for fragmentation? How big does it have to be?
Rough guess, same place as the leaf level. Around 1000 pages, maybe. Though, if you have an index with 1000 intermediate level pages, it's either one massive big index key or one massive big table.
However, a rebuild (not a reorg) will rebuild all levels, so in general, if you're rebuilding to fix fragmentation of the leaf level, you'll be fixing the fragmentation of the other levels as well.
btw, why is the rebuild option only for Enterprise Edition in your script?
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply