Slowness in collecting fragmentation details from huge database

mo boy 396 Reputation points
2023-11-10T09:00:41.1766667+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,068 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more

  2. Bruce (SqlWork.com) 59,051 Reputation points
    2023-11-10T19:16:26.1433333+00:00

    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.

    0 comments No comments

  3. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-11-13T02:26:27.0366667+00:00

    Hi @mo boy

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

    User's image

    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

    0 comments No comments