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.
If I just do a COUNT, it comes back with 93462 in about 1 second.
SELECT i.name takes 2 seconds (returns 93462 rows)
SELECT p.rows takes at least 18 minutes
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.
This is on SQL Server 2016 SP1-CU2, Enterprise, 64-bit
All help greatly appreciated!
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
