Simple query on sys.dm_db_partition_stats is running for 30 minutes

Question

Simple query on sys.dm_db_partition_stats on user database is running for close to 30 mins whereas it used to complete in less than 5 seconds earlier.

Query is just to fetch the right partition for loading the data and is not reading any data from user databases. Same query ran and completed in less than 5 seconds on any other large user database. Looking at the execution plan for this db and query output messages, we observed for one Table 'sysrowsets' . Scan count 1, logical reads is hundreds of thousands whereas on other databases, it is less than a thousand.

Here is the output for reference.

Table 'sysrowsets'. Scan count 888, logical reads 3227880, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

We are unable to query this sysrowsets directly as it is an underlying table. Able to query when connected via DAC but couldn't find anything abnormal from this.

Still wondering what the reason for this many reads is and why is it taking such long time.

SQL Server Version: 2019, Compatibility Level: 150

SELECT Max(pstats.partition_number) AS PartitionNumber
FROM sys.dm_db_partition_stats AS pstats(NOLOCK)
INNER JOIN sys.destination_data_spaces AS dds(NOLOCK) ON pstats.partition_number = dds.destination_id
INNER JOIN sys.partition_schemes AS ps(NOLOCK) ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf(NOLOCK) ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS prv(NOLOCK) ON pf.function_id = prv.function_id
AND pstats.partition_number = (
CASE pf.boundary_value_on_right
WHEN 0
THEN prv.boundary_id
ELSE (prv.boundary_id + 1)
END
)
WHERE OBJECT_Name(pstats.object_id) = 'OBJECTNAME'
AND OBJECT_SCHEMA_NAME(pstats.object_id) = 'DBNAME'

Partitions are equally created on all the user databases. Not too many tables were created on the database recently. Adding to that, mentioned query is to fetch data from only system DMVs and no table from user databases is being read. Hence, I assume there is no scope to add indexes for performance improvements. Also, this behavior is being observed all of a sudden since last 4 days. We restored last week full backup and tested with same query and got results in less than 5 seconds. No significant changes or upgrades are done to system or database.

Answer 1

I see where you are coming from. But I don't think you'll get anywhere without changing the query. This is the world we live in, with optimizers and the uncertainty regarding what query plan we get. Make the predicate seekable and you are likely to get a different plan, quite probably to the better. I tried a non-seekable predicate vs a seekable predicate on object_id when selecting from sys.dm_db_partition_stats, and did indeed get different execution plans:

SELECT * FROM sys.dm_db_partition_stats
WHERE object_id  = 123132

SELECT * FROM sys.dm_db_partition_stats
WHERE CAST(object_id AS varchar(20))  = '123132'

So, repeating Denis' suggestion:

Let's begin from changing your WHERE clause pstats.object_id = OBJECT_ID(N'SchemaName.TableName')

Answer 2

I know that this is an old post but I just ran into the same issue on an Azure SQL Managed Instance. I have very similar code running that checks a table that is partitioned by year and month and creates future partitions when needed. The code ran without issue on our on premise servers, but we started experiencing problems after migrating to Azure. We had seen similar issues with some other SQL code and the issue was that SQL server was using nested loop joins which were causing the delay. By adding a join hint to use hash joins it eliminated the problem.

SELECT Max(pstats.partition_number) AS PartitionNumber
FROM sys.dm_db_partition_stats AS pstats(NOLOCK)
INNER HASH JOIN sys.destination_data_spaces AS dds(NOLOCK) ON pstats.partition_number = dds.destination_id
INNER HASH JOIN sys.partition_schemes AS ps(NOLOCK) ON dds.partition_scheme_id = ps.data_space_id
INNER HASH JOIN sys.partition_functions AS pf(NOLOCK) ON ps.function_id = pf.function_id
LEFT HASH JOIN sys.partition_range_values AS prv(NOLOCK) ON pf.function_id = prv.function_id
AND pstats.partition_number = (
CASE pf.boundary_value_on_right
WHEN 0
THEN prv.boundary_id
ELSE (prv.boundary_id + 1)
END
)
WHERE OBJECT_Name(pstats.object_id) = 'OBJECTNAME'
AND OBJECT_SCHEMA_NAME(pstats.object_id) = 'DBNAME'