Slowness in collecting fragmentation details from huge database

Question

Dear Team,

On running below code as an SQL Agent job, the performance is very slow causing blocking. The DB size is over 2 TB. Could you please advise any steps for improvement of this code?

Thanks,

SET NOCOUNT ON;
 
DECLARE @dbid smallint;
SET @dbid = DB_ID('DBName')
 
INSERT INTO DBAdminDB.dbo.tblIndexStats
(
ObjectId,
ObjectName,
SchemaName,
IndexName,
IndexId,
PartitionNum,
Fragmentation,
PagesCount,
Fragment_Count,
Avg_fragment_size_in_pages
)
SELECT
    PS.object_id AS Objectid,
    O.name AS ObjectName,
    S.name AS SchemaName,
    I.name AS IndexName,
    PS.index_id AS IndexId,
    PS.partition_number AS PartitionNum,
    ROUND(PS.avg_fragmentation_in_percent, 2) AS Fragmentation,
    PS.page_count AS PagesCount,
       PS.fragment_count,
       ROUND(PS.avg_fragment_size_in_pages, 2) AS Avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats (@dbid,NULL,NULL,NULL,'LIMITED') PS
      JOIN sys.objects O ON PS.object_id = O.object_id
      JOIN sys.schemas S ON S.schema_id = O.schema_id
      JOIN sys.indexes I ON I.object_id = PS.object_id
            AND I.index_id = PS.index_id
WHERE PS.index_id > 0

Answer 1

As sys.dm_db_index_physical_stats needs to read all pages in an index, it’s not fast. You should only run on off hours, and only one table or index at a time.

Answer 2

Hi @mo boy

In the official documentation of sys.dm_db_index_physical_stats, there is such a paragraph.

Probably this is why caused blocking.

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver16

Best regards,

Percy Tang