Fragmentation report running for long time

[Sujay1185]

We have client SQL database 2012 with 5 TB of data and we are trying to run the fragmentation report for index rebuild but the report query is running for almost 2 hours and we need to kill the session manually because of the query running for long time. Can you please help me how to fix the fragmentation report and run the rebuild index without issues in SQL database 

Thanks,

Sujay

[#2]

@Sujay1185 , do you query via sys.dm_db_index_physical_stats? Then you can use the "mode" parameter to reduce the workload, see sys.dm_db_index_physical_stats (Transact-SQL) - SQL Server | Microsoft Learn => Scanning modes

[#3]

@olafhelper tried the below query against the user db

SELECT

      dbtables.[name] AS 'TableName',

    ISNULL(dbindexes.[name], 'Heap') AS 'IndexName',

    indexstats.index_type_desc AS 'IndexType',

    indexstats.avg_fragmentation_in_percent AS 'FragmentationPercent',

    indexstats.page_count AS 'PageCount'

FROM

    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, Null) indexstats

INNER JOIN

    sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]

LEFT OUTER JOIN

    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

                              AND indexstats.index_id = dbindexes.index_id

WHERE

    indexstats.database_id = DB_ID() and indexstats.avg_fragmentation_in_percent > 50 and indexstats.page_count > 1000 and dbtables.[schema_id] =1

ORDER BY

    indexstats.avg_fragmentation_in_percent DESC

For 6777 tables it was still running more than 1.5 hrs. Please let me know do we get the result further or any fine tune can be done.

Thanks,

Sujay

[#4]

As I already wrote, use a different scanning mode.

[#5]

Thanks it worked

[#6]

In the same fragmentation issue, I have created a automated job in SQL agent to run the index rebuild job on daily basis but it keep on failing for the last 1 week stating the process ended. So to rebuild the index do we need space in Data drive were the .MDF files resides. The current free space is 1 TB out of 5.16TB. Please let me know do we have suggestions to run the index rebuild job without failure.

[#7]

In addition to the above query I could see so many Heap indexes available in the tables and can we recommend to drop and create as clustered index instead. Let me know the suggestions

[#8]

Can please someone help me on this above query