Is there a way to get index fragmentation without using dm_db_index_physical_stats?

Question

I am using SQL Server 2016 SP1 standard edition.

The view dm_db_index_physical_stats is very slow on big databases. Even if I specify the 'LIMITED' option.

I use the view to get index fragmentation, to determine if I should do a REBUILD or a REORG in my maintenance. But as the databases grows, the call to the DMV dm_db_index_physical_stats takes more and more time. On some database, which can be as big as 1TB, it can take an hour to query the DMV. (I have no down time, there is always clients connected to the database doing work.)

So I was wondering if there's a way to know index fragmentation without querying the DMV.

score 2 · Accepted Answer · edited Jun 22 '17 at 18:41

I'll just resume the great suggestions that were made in the comments section :
The short answer is No, you have to use the DMV.

One possible alternative to using the DMV on the whole database would be to do it by table, but I need to do it for the whole database, and I already split index maintenance on multiple days so that it fits as much as possible in the maintenance window.

Another alternative would be to restore the database on another server and gather the index fragmentation over there.

I can also try to see if there's some unused indexes and see if I can remove some. I've found this query very use full, if it can serve someone else. If you see indexes with no reads and only writes, it means that the index is only used when you insert rows in the table, but never in the SELECT queries. You can then analyse and decide if it's usefull and should be kept.

SELECT  '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] ,
        i.[name] AS [index_name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups]
            AS [user_reads] ,
        ddius.[user_updates] AS [user_writes] ,
        ddios.[leaf_insert_count] ,
        ddios.[leaf_delete_count] ,
        ddios.[leaf_update_count] ,
        ddios.[nonleaf_insert_count] ,
        ddios.[nonleaf_delete_count] ,
        ddios.[nonleaf_update_count]
FROM    sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id] AND i.[index_id] = ddius.[index_id]
INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id] AND SP.[index_id] = ddius.[index_id]
INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
INNER JOIN sys.[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) AS ddios
  ON ddius.[index_id] = ddios.[index_id]
  AND ddius.[object_id] = ddios.[object_id]
  AND SP.[partition_number] = ddios.[partition_number]
  AND ddius.[database_id] = ddios.[database_id]
WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
      AND ddius.[index_id] > 0
      AND ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
ORDER BY ddius.[user_updates] DESC ,
        su.[name] ,
        o.[name] ,
        i.[name ]

Is there a way to get index fragmentation without using dm_db_index_physical_stats?

1 Answers1