Why Index column is NULL for DMV - sys.dm_db_index_physical_stats

Question

Why Index column is NULL for DMV - sys.dm_db_index_physical_stats

patelxx

SSC Eights!

Points: 853
More actions
October 30, 2019 at 11:06 am

#3693035

I am running the DMV (sys.dm_db_index_physical_stats) to get the fragmentation for a database, however when I get the results the Index column is NULL for 3 tables even though the table in question has 2 x indexes on it and the avg_frag percentage is >80%. Why is this? I've tried DBCC INDEXDEFRAG for these 3 x tables and Fragmentation still remains >80%. What can i do?

Viewing 3 posts - 1 through 2 (of 2 total)

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

I can think of a few reasons for this, but could you post your full query?

I am not sure if it is that I am running that DMV wrong, but your specific output, from what I can tell, needs to join to other tables to get all of the data you are looking for.

I am making a guess your query is similar to this one:

SELECT
                .[name] AS [Schema]
[t].[name] AS 

                , .[name]                  AS [index]
                , [ips].[avg_fragmentation_in_percent]
                , [ips].[record_count]        AS [table_record_count]
                , [ips].[page_count]
 FROM           [sys].[dm_db_index_physical_stats](   DB_ID()
                                                      , NULL
                                                      , NULL
                                                      , NULL
                                                      , 'SAMPLED'
                                                  ) AS [ips]
     INNER JOIN [sys].[tables]                      AS [t]
         ON [t].[object_id]       = [ips].[object_id]
     INNER JOIN [sys].[schemas]                     AS 
         ON [t].[schema_id]       = .[schema_id]
     INNER JOIN [sys].[indexes]                     AS 
         ON ([ips].[object_id]    = .[object_id])
            AND ([ips].[index_id] = .[index_id])
 ORDER BY       [avg_fragmentation_in_percent] DESC;

If so, try pulling the [ips].[index_type_desc] column. When the index_name is null, at least on my system, it is because it is a heap and all indexes that are on it are non-clustered indexes.

The TL;DR version of the above - check to see if the tables with NULL are HEAPs or not. NULL generally means you have a heap. You can have a HEAP with a nonclustered index on it.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Sue_H SSC Guru Points: 90860 More actions · Answer 2

patelxx wrote:

I am running the DMV (sys.dm_db_index_physical_stats) to get the fragmentation for a database, however when I get the results the Index column is NULL for 3 tables even though the table in question has 2 x indexes on it and the avg_frag percentage is >80%. Why is this? I've tried DBCC INDEXDEFRAG for these 3 x tables and Fragmentation still remains >80%. What can i do?

Without seeing the query it's hard to say but I would suspect those are heaps. On most of those queries, the index name is null if the table is a heap. You'd want to include index type in those queries.

Sue