Unused index List Automatic report

Setup Unusedindex Job ALert

USE [DBA]

GO

/****** Object:  StoredProcedure [dbo].[P_unusedindexlist]    Script Date: 23/02/2016 13:19:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--P_unusedindexlist '10','KCRCSQL411I03\I03'

--select @@Servername

CREATE procedure [dbo].[P_unusedindexlist]

(

@dayscount int,

@datasource sysname

)

As begin

Declare @restrarttime datetime

Declare @days int

--Declare @dayscount int =10

SELECT @restrarttime= sqlserver_start_time FROM sys.dm_os_sys_info

SELECT @days=DATEDIFF(day, @restrarttime, getdate())

if @days>= @dayscount

Begin

-- Create the temp table to store the unused indexes

IF OBJECT_ID('TEMPDB..#UnusedIndexes') IS NOT NULL

DROP TABLE #UnusedIndexes

CREATE TABLE #UnusedIndexes

(

DBNAME VARCHAR(500)

,OBJECTNAME VARCHAR(500)

,INDEXNAME VARCHAR(500)

,USER_SEEKS bigint

,USER_SCANS bigint

, USER_LOOKUPS bigint

,USER_UPDATES bigint

)

-- Load the unused indexes into the temp table

EXEC sp_MSForEachDB 'USE [?];

INSERT INTO #UnusedIndexes

SELECT  DISTINCT

        db_NAME() AS DBNAME

        ,OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME]

        ,I.[NAME] AS [INDEX NAME]

        ,S.USER_SEEKS

        ,S.USER_SCANS

        ,S.USER_LOOKUPS

        ,S.USER_UPDATES

              FROM    sys.dm_db_index_usage_stats AS S

         INNER JOIN sys.indexes AS I

         ON I.[OBJECT_ID] = S.[OBJECT_ID]

         AND I.INDEX_ID = S.INDEX_ID

               inner join sys.objects o

               ON I.object_id = o.object_id

WHERE    OBJECTPROPERTY(S.[OBJECT_ID],''IsUserTable'') = 1

and I.index_id<>1 --exclude primary key

and S.user_seeks=0 -- no index seek performed by the user

and S.USER_SCANS=0

and S.USER_LOOKUPS=0

and S.USER_UPDATES>=0 --index maintained during updates

and I.name is not null

       AND o.is_ms_shipped = 0 -- exclude system objects

       AND o.type NOT IN(''F'', ''UQ'') -- exclude the foreign keys and unique contraints

'

DECLARE @bodyMsg nvarchar(max)

DECLARE @subject nvarchar(max)

DECLARE @tableHTML nvarchar(max)

Declare @srvname sysname

select @srvname=srvname from sys.sysservers where datasource=@datasource

SET @subject = 'Unused indexes'+' '+'executed in'+' '+@srvname

SET @tableHTML =

N''+   

N'

Unused Index List

' +

N'' +

N'

DBNAME

OBJECTNAME

INDEXNAME

USER_SEEKS

USER_SCANS

USER_LOOKUPS

USER_UPDATES

' +

CAST ( (

SELECT

td = CAST(DBNAME AS VARCHAR(max)),'',

td = CAST(OBJECTNAME AS VARCHAR(max)),'',

td = CAST(INDEXNAME AS VARCHAR(max)),'',

td = CAST(USER_SEEKS AS VARCHAR(100)),'',

td = CAST(USER_SCANS AS VARCHAR(100)),'',

td = CAST(USER_LOOKUPS AS VARCHAR(100)),'',

td = CAST(USER_UPDATES AS VARCHAR(100))

FROM #UnusedIndexes

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'

'

EXEC msdb.dbo.sp_send_dbmail 

@profile_name='xxxx',

@recipients='xxxx',

@subject = @subject,

@body = @tableHTML,

@body_format = 'HTML' ;

drop table #UnusedIndexes

End

Else

print 'Server was not restarted older than 1 month'

End

GO

Find out the disabled indexes 

SELECT i.name AS Index_Name, i.index_id, i.type_desc, s.name AS 'Schema_Name', o.name AS Table_Name

FROM sys.indexes i

JOIN sys.objects o on o.object_id = i.object_id

JOIN sys.schemas s on s.schema_id = o.schema_id

WHERE i.is_disabled = 1

ORDER BY

i.name

GO

Find out the unused indexes 

SELECT  DISTINCT

        db_NAME() AS DBNAME

        ,OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME]

        ,I.[NAME] AS [INDEX NAME]

        ,S.USER_SEEKS

        ,S.USER_SCANS

        ,S.USER_LOOKUPS

        ,S.USER_UPDATES

              FROM    sys.dm_db_index_usage_stats AS S

         INNER JOIN sys.indexes AS I

         ON I.[OBJECT_ID] = S.[OBJECT_ID]

         AND I.INDEX_ID = S.INDEX_ID

               inner join sys.objects o

               ON I.object_id = o.object_id

WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

and I.index_id<>1 --exclude primary key

and S.user_seeks=0 -- no index seek performed by the user

and S.USER_SCANS=0

and S.USER_LOOKUPS=0

and S.USER_UPDATES>=0 --index maintained during updates

and I.name is not null

       AND o.is_ms_shipped = 0 -- exclude system objects

       --AND o.type NOT IN('F', 'UQ') -- exclude the foreign keys and unique contraints

           and OBJECT_NAME(s.[OBJECT_ID]) in ('tblSt')

go

          SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],

       I.[NAME] AS [INDEX NAME],

       A.LEAF_INSERT_COUNT,

       A.LEAF_UPDATE_COUNT,

       A.LEAF_DELETE_COUNT

FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A

       INNER JOIN SYS.INDEXES AS I

         ON I.[OBJECT_ID] = A.[OBJECT_ID]

            AND I.INDEX_ID = A.INDEX_ID

WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

and OBJECT_NAME(A.[OBJECT_ID])='tblSto'