Forum Discussion
Fragmentation report running for long time
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
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
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
- As I already wrote, use a different scanning mode.
