Running sys.dm_db_index_physical_stats in parallel

Posted on

Question :

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?

Answer :


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
    return select *
        from OpenRowset
        (   TABLE

More info on these internal data sources here.

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

enter image description here

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

enter image description here

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


Leave a Reply

Your email address will not be published. Required fields are marked *