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?
DBCC TRACEON (8649) and
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) are not available in SQL Server 2014.
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?
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