title: Performance issue joining system tables & DMVs on DB with many partitioned tables?
body: <p>I'm running the below query on each database in my environment, twice a day, for tracking. I have a relatively busy server with a database that has a count of 95k rows for this query (this DB contains many about 20 tables that can have many partitions, some upwards of 300). On all my other servers it runs fine, but for this one database I've started having problems, where it used to finish within a couple of minutes but now will run over 18 minutes.</p>
  
  <p>If I just do a COUNT, it comes back with 93462 in about 1 second. </p>
  
  <p>SELECT i.name takes 2 seconds (returns 93462 rows)</p>
  
  <p>SELECT p.rows takes at least 18 minutes</p>
  
  <p>sp_whoisactive shows no WAIT_INFO.  I tried using SQL Sentry Plan Explorer's WAIT STATS feature, but since the query never finishes (longest I've let it run is 18 minutes) I don't get any results.</p>
  
  <p>This is on SQL Server 2016 SP1-CU2, Enterprise, 64-bit</p>
  
  <p>All help greatly appreciated!</p>
  
  <pre><code>SELECT distinct 'mydbname' AS database_name,
          SCHEMA_NAME(o.schema_id) AS table_schema,
          o.name AS table_name,
          i.name AS index_name,
          o.type_desc as type_desc_full,
          CASE i.type 
                  WHEN 0 THEN 'HP' 
                  WHEN 1 THEN 'C' 
                  WHEN 2 THEN 'NC'
                  WHEN 3 THEN 'XM'
                  WHEN 4 THEN 'Sp'
                  WHEN 5 THEN 'CS' --clustered columnstore, which doesn't exist (yet)
                  WHEN 6 THEN 'cs' --nonclustered columnstore, available in 2012.
          ELSE 'UK' END AS type_desc_brief,
          MAX(a.type) as allocation_type,
          MAX(p.rows) AS rows_in_table,
          SUM(a.total_pages * 8/1024) AS Total_MB,
          avg(u.user_seeks) AS user_seeks,
          MAX(last_user_seek) AS last_user_seek,
          avg(u.user_lookups) AS user_lookups,
          MAX(last_user_lookup) AS last_user_lookup,
          avg(u.user_scans) AS  user_scans,
          MAX(last_user_scan) AS last_user_scan,
          avg(u.user_updates) AS  user_updates,
          MAX(last_user_update) AS last_user_update
  FROM    sys.indexes i
  JOIN    sys.objects o
          ON i.object_id = o.object_id
  LEFT JOIN sys.dm_db_index_usage_stats u
          ON i.object_id = u.object_id
             AND i.index_id = u.index_id
             AND u.database_id = DB_ID()
  inner JOIN sys.partitions as p
          on i.object_id = p.object_id
          and i.index_id = p.index_id
  INNER JOIN sys.allocation_units AS a
          on (a.type = 2 AND p.partition_id = a.container_id)
          OR ((a.type = 1 OR a.type = 3) AND p.hobt_id = a.container_id)
  --WHERE   o.type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE','SQL_TABLE_VALUED_FUNCTION')           -- No system tables!
  GROUP BY SCHEMA_NAME(o.schema_id),
          o.name,
          i.name,
          o.type_desc,
          CASE i.type 
                  WHEN 0 THEN 'HP' 
                  WHEN 1 THEN 'C' 
                  WHEN 2 THEN 'NC'
                  WHEN 3 THEN 'XM'
                  WHEN 4 THEN 'Sp'
                  WHEN 5 THEN 'CS' --clustered columnstore
                  WHEN 6 THEN 'cs' --nonclustered columnstore, available in 2012.
          ELSE 'UK' END
  </code></pre>