Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Get index sizes in a db (basic)
-- With help from the internet and manning press!
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select
ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0) as Cost,
d.[statement] as TableName,
equality_columns,
inequality_columns,
included_columns
from
sys.dm_db_missing_index_groups as g
inner join sys.dm_db_missing_index_group_stats as s
ON s.group_handle = g.index_group_handle
inner join sys.dm_db_missing_index_details as d
ON d.index_handle = g.index_handle
order by Cost desc
-- With help from the internet and manning press!
select
object_name(i.object_id) as table_name,
COALESCE(i.name, space(0)) as index_name,
ps.row_count,
i.is_primary_key,
Cast((ps.reserved_page_count * 8)/1024. as decimal(12,2)) as size_in_mb,
COALESCE(ius.user_seeks,0) as user_seeks,
COALESCE(ius.user_scans,0) as user_scans,
COALESCE(ius.user_lookups,0) as user_lookups,
i.type_desc
from
sys.all_objects t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id
AND i.index_id = ps.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = db_id()
AND i.object_id = ius.object_id AND i.index_id = ius.index_id
order by size_in_mb desc, object_name(i.object_id), i.name
-- With help from the internet and manning press!
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select
DB_NAME() as DatabaseName,
SCHEMA_NAME(o.Schema_ID) as SchemaName,
OBJECT_NAME(s.[object_id]) as TableName,
i.name as IndexName,
s.user_updates,
s.system_seeks + s.system_scans + s.system_lookups as Usage
from
sys.dm_db_index_usage_stats as s
inner join sys.indexes i ON s.[object_id] = i.[object_id]
and s.index_id = i.index_id
inner join sys.objects o ON i.object_id = O.object_id
WHERE
s.database_id = DB_ID()
and OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
and s.user_seeks = 0
and s.user_scans = 0
and s.user_lookups = 0
and i.name is not null
order by s.user_updates desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.