Running sys.dm_db_index_physical_stats in parallel

I am trying to get fragmentation information on a few tables which are over 500 GB and I am using the ...DETAILED option of sys.dm_db_index_physical_stats. I am doing this on a restore copy of the production database on our pre-Prod server so I don’t care about hurting any performance on the server.

I ran it and it looks like it is running in serial and it is taking forever. Is there a way to run dm_db_index_physical_stats() in parallel? Or is any other setting interfering with it?

I guess DBCC TRACEON (8649) and OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) are not available in SQL Server 2014.

Also tried DBCC SETCPUWEIGHT(1000); from Paul White’s blog here. The blog mentions about parallelism inhibitor and one of which is system tables. Is dm_db_index_physical_stats() considered a system table?

sys.dm_db_index_physical_stats 

is a system table valued function.

Internally this will perform an openrowset call to an internal INDEXANALYSIS system data source.

create function [sys].[dm_db_index_physical_stats]
(
    @DatabaseId         SMALLINT        = 0,
    @ObjectId           INT             = 0,
    @IndexId            INT             = -1,
    @PartitionNumber    INT             = 0,
    @Mode               nvarchar(20)    = NULL
)
returns table
as
    return select *
        from OpenRowset
        (   TABLE
            INDEXANALYSIS,
            @DatabaseId,
            @ObjectId,
            @IndexId,
            @PartitionNumber,
            @Mode
        )
GO

More info on these internal data sources here.

You can find the same INDEXANALYSIS TVF call in the execution plan:

And you can also see that it is a multi statement table valued function.

So the query will not be able to use parallelism due to reason #1: system table access

The list changes from version to version, but for example these things
make the whole plan serial on SQL Server 2012:

• …

• System table access (e.g. reading from sys.tables)

The TVF call will also be serial due to reason #2: Multi statement TVF

These query features are examples that require a serial zone in the

• …

• Multi-statement T-SQL table-valued functions

Source