2

This question already has an answer here:

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.

marked as duplicate by Michael Green, McNets, James Anderson, Joe Obbish, Marco Jun 27 '17 at 8:21

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    I believe you are running it for whole database can you make it for table or a index, yes it can be slow for big databases see my answer here – Shanky Jun 22 '17 at 14:09
  • I need to run it for the whole database. I need to know every index in the database and then choose which ones to REBUILD or REORG. I'm doing the whole database maintenance. Not only a single table.. I already know it can be slow for a big database. That's why I wanted to know if there's another way to get index fragmentation. – Danielle Paquette-Harvey Jun 22 '17 at 14:35
  • Or else what would you do? Question sys.indexes to know every object and index in the database, and then do an dm_db_index_physical_stats for every single object one at a time? – Danielle Paquette-Harvey Jun 22 '17 at 14:36
  • 4
    Have you considered restoring a copy of the database somewhere else and running the fragmentation stats against that? It is a little more work, but you could automate the restore and frag stats gathering which would allow you to focus on the individual indexes that need the attention. – Nic Jun 22 '17 at 15:24
  • 1
    @Nic: Have you considered posting an answer? :) – Andriy M Jun 22 '17 at 15:30
2

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 ] 

Not the answer you're looking for? Browse other questions tagged or ask your own question.