Index counts:
select
o.name, COUNT(*)
from sys.indexes i inner join sys.objects o on i.object_id=o.object_id
where o.type='U' and i.index_id<>1
group by o.name
order by COUNT(*) desc
index usage/unused indexes
select
--db_name(db_id()) as [database]
--, objectname=object_name(s.object_id)
ROW_NUMBER() over (partition by o.name,i.name order by i.index_id) as rowcouter
,
'CREATE ' +
CASE i.IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE ' ' END +
'NONCLUSTERED ' + ' INDEX '+
i.NAME + ' ' +' ON ' +
OBJECT_NAME (i.OBJECT_ID) +
'('+
DBO.fGetIndexCols (object_NAME(i.OBJECT_ID), i.index_id) + ')'
+Case when dbo.fGetIncludedIndex(object_NAME(i.OBJECT_ID), i.index_id) is null then '' else ' INCLUDE ('+dbo.fGetIncludedIndex(object_NAME(i.OBJECT_ID), i.index_id)+')' end +
' ON ['+
( SELECT GROUPNAME
FROM SYSFILEGROUPS
WHERE GROUPID = DATA_SPACE_ID
) + ']' IndexScript
,
'drop index ' + i.name + ' on ' + o.name as dropscript
, Indexed_Columns = LEFT(ixColumns, LEN(ixColumns) -1)
, Included_Columns = LEFT(includedColumns, LEN(includedColumns) -1)
, o.name as tablename
, indexname=i.name
, i.index_id
, user_seeks
, user_scans
, user_lookups
, user_updates
--, user
--, reads=user_seeks + user_scans + user_lookups
--, writes = user_updates
from
sys.indexes i inner join sys.objects o on i.object_id=o.object_id
left join sys.dm_db_index_usage_stats s on i.index_id=s.index_id and i.object_id=s.object_id
CROSS APPLY (
SELECT sc.Name + CASE WHEN is_descending_key = 1 THEN ' DESC'
ELSE '' END
+ ', '
FROM sys.index_columns ic
JOIN sys.columns sc
on ic.object_id = sc.object_id
AND ic.column_id = sc.column_id
WHERE i.object_id = ic.object_id
AND is_included_column = 0
and i.index_id = ic.index_id
ORDER BY ic.Key_Ordinal
FOR XML PATH('') ) ix (ixColumns)
CROSS APPLY (
SELECT sc2.Name + ', '
FROM sys.index_columns ic2 JOIN sys.columns sc2 on ic2.object_id = sc2.object_id
AND ic2.column_id = sc2.column_id
WHERE i.object_id = ic2.object_id
AND is_included_column = 1
and i.index_id = ic2.index_id
ORDER BY ic2.Key_Ordinal
FOR XML PATH('') ) nc (includedColumns)
where o.type='U' --and s.database_id=DB_ID()
and i.index_id <>1
and o.name not like 'tem%'
and (isnull(user_seeks,0) + isnull(user_scans,0) + isnull(user_lookups,0))= 0 --comment out to show all indexes usage
and i.name is not null
--and i.index_id=0
--order by o.name, user_seeks, user_scans,user_lookups, user_updates
Missing indexes
;WITH I AS (
-- Missing Indexes current database by Index Advantage
-- This DMV Query written by Glenn Berry
SELECT user_seeks * avg_total_user_cost *
(avg_user_impact * 0.01) AS [index_advantage],
migs.last_user_seek,
mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns,
mid.included_columns,migs.unique_compiles, migs.user_seeks,
migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
AND user_seeks * avg_total_user_cost *
(avg_user_impact * 0.01) > 10000 -- Set this to Whatever
)
