Are your CPUs really busy on that server for some reason?

In my case, I have lots of free CPU, it's I/O that's restraining my system perf.

Wow, that seems way long for only 38 dbs.

Force SQL to not parallelize the query, and see how that runs:

SELECT
DB_NAME(database_id) AS db_name,
COUNT(*) / 128.0...

How is the underlying table clustered?

What the most common WHERE conditions used when you read this table?

The best, most complete solution overall is to best cluster...

I did get less execution time cost using COUNT() rather than COUNT_BIG():

SELECT
DB_NAME(database_id) AS db_name,
COUNT(*) / 128.0 AS buffer_pool_size_mb
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id <> 32767...

Yes.

;WITH cte_dups AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY name ORDER BY id DESC) AS row_num /* keep the HIGHEST/LAST id */
    FROM...

Try it this way:

SELECT *
FROM (
  SELECT Sequence#, Member#, Group#
  WHERE Group# = 'PP0018'
) AS iq1
WHERE Sequence# <> CASE...

How specifically?  That's a lot of LEFT JOINs.  Is it safe to use NOLOCK on those tables?  If so, have you tried that?  (Let...