3

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?

5
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:

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

Source

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.