Missing index automation

Intelligent checks on this script

1. It won't be deployed if the server was restarted recently or days count value can be passed in to parameter.

2. Duplication filtered from the DMV.

3. If the index suggestion already provided by the old alerts then it has a validation to check and notify them on the agent job history logs.

Functionality of this script

1.It provides the missing index list across all the databases.

2.it provides useful columns to validate such as average_user_impact  etc…

USE [master]

GO

/****** Object:  StoredProcedure [dbo].[SP_Missingindexes]    Script Date: 16/04/2015 15:54:39 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_Missingindexes]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[SP_Missingindexes]

GO

/****** Object:  StoredProcedure [dbo].[SP_Missingindexes]    Script Date: 16/04/2015 15:54:39 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_Missingindexes]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'CREATE procedure [dbo].[SP_Missingindexes]

(

@dayscount int,

@datasource sysname

)

As begin

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--validation check that the sql instance restarted older than the specified count

Declare @restrarttime datetime

Declare @days int

SELECT @restrarttime= sqlserver_start_time FROM sys.dm_os_sys_info

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

if @days>= @dayscount

Begin

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[missingindex]'') AND type in (N''U''))

BEGIN

CREATE TABLE [dbo].[missingindex](

       [MissingIndexSQL] [nvarchar](max) NULL,

       [TotalCost] float (8) NULL,

       [TableName] [nvarchar](max) NULL,

       [equality_columns] [nvarchar](max) NULL,

       [inequality_columns] [nvarchar](max) NULL,

       [included_columns] [nvarchar](max) NULL,

       [avg_user_impact] float (8) null,

       [date] date

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

END

-- Acutal missing index recommendation

SELECT TOP 20

''CREATE NONCLUSTERED INDEX ''

+ QUOTENAME(''IX_AutoGenerated_''

+ REPLACE(REPLACE(CONVERT(VARCHAR(25), GETDATE(), 113)

, '' '', ''_''), '':'', ''_'')

+ ''_'' + CAST(d.index_handle AS VARCHAR(22))

)

+ '' ON '' + d.[statement] + ''(''

+ CASE

WHEN d.equality_columns IS NULL THEN d.inequality_columns

WHEN d.inequality_columns IS NULL THEN d.equality_columns

ELSE d.equality_columns + '','' + d.inequality_columns

END

+ '')''

+ CASE

WHEN d.included_columns IS NOT NULL THEN

'' INCLUDE ( '' + d.included_columns + '')''

ELSE ''''

END AS MissingIndexSQL

, ROUND(s.avg_total_user_cost * s.avg_user_impact

* (s.user_seeks + s.user_scans),0) AS [Total Cost]

, d.[statement] AS [Table Name]

, d.equality_columns

, d.inequality_columns

, d.included_columns

,s.avg_user_impact

INTO #tempMissingIndexes

FROM sys.dm_db_missing_index_groups g

INNER JOIN sys.dm_db_missing_index_group_stats s

ON s.group_handle = g.index_group_handle

INNER JOIN sys.dm_db_missing_index_details d

ON d.index_handle = g.index_handle

ORDER BY [Total Cost] DESC

-- get the unique missing index records incase if the rows are duplicated

select distinct([Table Name]),equality_columns,inequality_columns,included_columns into #distinctMissingIndexes from #tempMissingIndexes

--get the required columns and then stored in to actual missing index temp tables

select M.*  into #MissingIndexes from #distinctMissingIndexes as MI

inner join #tempMissingIndexes as M

on isnull(MI.[Table Name],'''')=isnull(M.[Table Name],'''') and isnull(M.equality_columns,'''')=isnull(MI.equality_columns,'''')

and isnull(M.inequality_columns,'''')=isnull(MI.inequality_columns,'''') and isnull(M.included_columns,'''')=isnull(MI.included_columns,'''')

--validation check that if index suggestions are repeated .

select M.tablename,M.equality_Columns,M.inequality_columns,M.included_columns from [missingindex] as M

inner join #MissingIndexes as MI

on isnull(MI.[Table Name],'''')=isnull(M.tablename,'''') and isnull(M.equality_columns,'''')=isnull(MI.equality_columns,'''')

and isnull(M.inequality_columns,'''')=isnull(MI.inequality_columns,'''') and isnull(M.included_columns,'''')=isnull(MI.included_columns,'''')

if @@rowcount = 0

Begin

DECLARE @MissingIndexesSQL NVARCHAR(MAX)

SET @MissingIndexesSQL = ''''

SELECT

@MissingIndexesSQL = @MissingIndexesSQL + MissingIndexSQL + CHAR(10)

FROM #MissingIndexes

DECLARE @StartOffset INT

DECLARE @Length INT

SET @StartOffset = 0

SET @Length = 4000

WHILE (@StartOffset < LEN(@MissingIndexesSQL))

BEGIN

PRINT SUBSTRING(@MissingIndexesSQL, @StartOffset, @Length)

SET @StartOffset = @StartOffset + @Length

END

PRINT SUBSTRING(@MissingIndexesSQL, @StartOffset, @Length)

--- inserting the actual index in to analysis tool for the later analysis

INSERT INTO [dbo].[missingindex]

           ([MissingIndexSQL]

           ,[TotalCost]

           ,[TableName]

           ,[equality_columns]

           ,[inequality_columns]

           ,[included_columns]

                 ,[avg_user_impact]

                 ,[date])

select MissingIndexSQL,[Total Cost],[Table Name],equality_columns,inequality_columns,

included_columns,avg_user_impact,getdate()

 from #MissingIndexes

Declare @deploydate date

set @deploydate=getdate()

SELECT  [MissingIndexSQL]

      ,[TotalCost]

      ,[TableName]

      ,[equality_columns]

      ,[inequality_columns]

      ,[included_columns]

      ,[avg_user_impact]

      ,[date]

  into #missingindexemail

  FROM [dbo].[missingindex]

  where [date]=@deploydate

 

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 = ''Missing index ''+'' ''+''reported in''+'' ''+@srvname

SET @tableHTML =

N''''+  

N''

Missing index:Performance Impact reported in the column avg_user_impact

'' +

N'''' +

N''
MissingIndexSQL

TableName

avg_user_impact

'' +

CAST ( (

SELECT td = CAST(MissingIndexSQL AS VARCHAR(max)),'''',

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

td = CAST(avg_user_impact AS VARCHAR(max))

FROM #missingindexemail

FOR XML PATH(''tr''), TYPE

) AS NVARCHAR(MAX) ) +

N''

''

EXEC msdb.dbo.sp_send_dbmail  @recipients=''mohamed.faizel@kpmg.co.uk'',

@subject = @subject,

@body = @tableHTML,

@body_format = ''HTML'' ;

drop table #missingindexemail

End

Else

print '' indexes are repeated''

drop table #MissingIndexes

drop table #distinctMissingIndexes

drop table #tempMissingIndexes

End

else

print ''validation Check''+'':''+''SQL instance restarted was not older than'' +'' ''+ cast(@dayscount as varchar)+'' ''+ ''days;''+'' ''+''Missing index Mechanism deactivated itself automatically.''

End

'

END

GO

List all the indexes info for the particular database 

select i.[name] as index_name,
    case when i.[type] = 1 then 'Clustered index'
        when i.[type] = 2 then 'Nonclustered unique index'
        when i.[type] = 3 then 'XML index'
        when i.[type] = 4 then 'Spatial index'
        when i.[type] = 5 then 'Clustered columnstore index'
        when i.[type] = 6 then 'Nonclustered columnstore index'
        when i.[type] = 7 then 'Nonclustered hash index'
        end as index_type,
    case when i.is_unique = 1 then 'Unique'
        else 'Not unique' end as [unique],
    schema_name(t.schema_id) + '.' + t.[name] as table_view, 
    case when t.[type] = 'U' then 'Table'
        when t.[type] = 'V' then 'View'
        end as [object_type]
from sys.objects t
    inner join sys.indexes i
        on t.object_id = i.object_id
    cross apply (select col.[name] + ', '
                    from sys.index_columns ic
                        inner join sys.columns col
                            on ic.object_id = col.object_id
                            and ic.column_id = col.column_id
                    where ic.object_id = t.object_id
                        and ic.index_id = i.index_id
                            order by col.column_id
                            for xml path ('') ) D (column_names)
where t.is_ms_shipped <> 1
and index_id > 0 and t.[type]='U' and t.name in ('tblstockmovement','tblstockledger','tblbondedwhtransactions')

order by t.name