January 9, 2019 at 12:11 pm
Has anybody seen these named indexs? I am doing the below join and these 2 indexs pop up and are 84gb and 220gb in size and growing. When I join to the sys.tables table to get the table they are tied to they don't have a table associated with them?
Appreciate the help.
SELECT i.[name] AS IndexName
,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
AND s.[index_id] = i.[index_id]
GROUP BY i.[name]
ORDER BY i.[name]
GO
January 9, 2019 at 12:38 pm
They do have tables associated with them, but only user tables are in sys.tables.
Edit: And they're not two indexes. Two index names, yes, but 65 indexes in my SQL 2017 instance. You need to group on table name and index name, because index name is only unique within a table.
SELECT i.[name] AS IndexName, o.name as TableName
,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
AND s.[index_id] = i.[index_id]
INNER JOIN sys.all_objects o ON o.object_id = i.object_id
GROUP BY i.[name], o.name
ORDER BY i.[name], o.name
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2019 at 12:57 pm
Awesome thank you GilaMonster! Now my next question is do I rebuild the cl index on this system table to get the size of this index down?
January 9, 2019 at 1:06 pm
You do not. It's a system table, you can't even see it.
And note, it's not one index, your query aggregated 65 indexes into 2.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2019 at 1:11 pm
Ok thanks. Reading some more on this and seems to be service broker related and sure enough I see they have some sort of service broker set up.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply