Etiket arşivi: sql unused indexes

Missing index ve Unused indexleri Bulmak

--unused indexes
Declare @dbid int 
Select @dbid = db_id() 
Select objectname=object_name(i.object_id),, i.index_id from sys.indexes i 
join sys.objects o on i.object_id = o.object_id where objectproperty(o.object_id,'IsUserTable') = 1 
and i.index_id NOT IN (select s.index_id from sys.dm_db_index_usage_stats s where s.object_id=i.object_id 
and i.index_id=s.index_id and database_id = @dbid ) order by objectname,i.index_id,indexname asc

-- Missing indexes
SELECT MID.[statement] AS ObjectName
,MID.equality_columns AS EqualityColumns
,MID.inequality_columns AS InequalityColms
,MID.included_columns AS IncludedColumns
,MIGS.last_user_seek AS LastUserSeek
* MIGS.avg_user_impact 
* (MIGS.user_seeks + MIGS.user_scans) AS Impact
,N'CREATE NONCLUSTERED INDEX <Add Index Name here> ' + 
N'ON ' + MID.[statement] + 
N' (' + MID.equality_columns 
+ ISNULL(', ' + MID.inequality_columns, N'') +
N') ' + ISNULL(N'INCLUDE (' + MID.included_columns + N');', ';')
AS CreateStatement
FROM sys.dm_db_missing_index_group_stats AS MIGS
INNER JOIN sys.dm_db_missing_index_groups AS MIG
ON MIGS.group_handle = MIG.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS MID
ON MIG.index_handle = MID.index_handle
WHERE database_id = DB_ID()
AND MIGS.last_user_seek >= DATEDIFF(month, GetDate(), -1)