Get index sizes in a db (basic)

Index recommendations

-- 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

Index sizes

-- 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

Unused indexes

-- 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