January 31, 2015 at 3:32 pm
Hi all,
I am not sure if this is normal. I have 500GB+ size of a table. when I run BELOW query (from Glenn Berry) WITH DETAILED, i get 3 different fragmentation rows with different no. of pages. Can someone explain please?
for example:
TableName IndexName index_type_desc avg_fragmentation_in_percent page_count
======= ========= ========== =================== =========
Customer IX_CustomerID Clustered Index 99.47 65,000
Customer IX_CustomerID Clustered Index 5.04 8750000
Customer IX_CustomerID Clustered Index 0.00 675000000
================================================================
-- Note: This could take some time on a very large database
SELECT DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name],
i.name AS [Index Name], ps.index_id, index_type_desc,
avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,N'DETAILED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id]
AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
AND page_count > 1500
ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);
January 31, 2015 at 7:05 pm
Look carefully at the output of the data, you will see that there may be multiple index levels. The function, sys.dm_db_index_physical_stats, when run with the DETAIL option shows the fragmentation level of indexes at all levels.
February 1, 2015 at 5:48 am
Thanks for you reply. But i didn't catch you. All three outputs have same table name, same index name and index type, but different fragment level and different page count!!!
February 1, 2015 at 8:21 am
Tac11 (2/1/2015)
Thanks for you reply. But i didn't catch you. All three outputs have same table name, same index name and index type, but different fragment level and different page count!!!
That index on that table has three levels to the index. When you run sys.dm_db_index_physical_stats at a detail level you will get the fragmentation level of an index at all levels of the index.
From books online regarding the table returned:
index_level tinyint Current level of the index.
0 for index leaf levels, heaps, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.
Greater than 0 for nonleaf index levels. index_level will be the highest at the root level of an index.
The nonleaf levels of indexes are only processed when mode = DETAILED.
February 1, 2015 at 8:44 am
So which level is fragmented 99% according to G. Berry's script? should I be worried about above result and run rebuild index job?
February 1, 2015 at 9:26 am
Tac11 (2/1/2015)
So which level is fragmented 99% according to G. Berry's script? should I be worried about above result and run rebuild index job?
My guess, since you don't have the index_level included in your post, would be level 0 which would be the leaf level.
February 2, 2015 at 10:46 am
You'll also see multiple rows for the same table/index combination if the table has multiple partitions. You just need to include the columns for index level and partition number so that you can distinguish those multiple rows from each other.
Cheers!
February 2, 2015 at 11:05 am
Since the page count is vastly smaller, I would think it's the highest level of the index rather than the lowest (leaf) level.
But you should definitely change the query to include more details, including at least the index_level and the alloc_unit_type_desc.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 2, 2015 at 11:09 am
Tac11 (2/1/2015)
So which level is fragmented 99% according to G. Berry's script? should I be worried about above result and run rebuild index job?
Just change the Word "Detailed" to "Sampled" and this nuance will go away. Heh... well except for "Out of Row" and "LOB data". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply