'sys.dm_db_index_physical_stats' giving 3 rows with different fragmentation.

Question

'sys.dm_db_index_physical_stats' giving 3 rows with different fragmentation.

Tac11

SSCertifiable

Points: 6980
More actions
January 31, 2015 at 3:32 pm

#303010

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);

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

Tac11 SSCertifiable Points: 6980 More actions · Answer 2

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!!!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

Tac11 SSCertifiable Points: 6980 More actions · Answer 4

So which level is fragmented 99% according to G. Berry's script? should I be worried about above result and run rebuild index job?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

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!

ScottPletcher SSC Guru Points: 101134 More actions · Answer 7

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)