Simple query on sys.dm_db_partition_stats is running for 30 minutes
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 in the first comment:
&quot;Let's begin from changing your WHERE clause pstats.object_id = OBJECT_ID(N'SchemaName.TableName')&quot;
(To avoid a long running comment thread (and Paul's wrath ;-), I put this as an answer which can be elaborated on and expanded on. )