Help! My server ate my SQL indexes

Not really, but it is a nice little title isn’t it.

So you are looking for a quick fix for your indexes. Use this little script.

Thanks to Brent Ozar and Clayton Kramer for your work on the script, I just chopped it a bit for our South African climate and added some other sections.

It will show you suggested indexes for performance improvements, then show you unused indexes and then finally heaps.

Edit: I changed the “ChangeIndexStatement” that gets generated to include a fill factor and make everything Ascending, at least I hope that is what I did.

Simple and effective.

/*-*-*-*-*-*-*-*-* ﷽‎ *-*-*-*-*-*-*-*-*-*-*-*/
/* ------------------------------------------------------------------
-- Title : FindMissingIndexes
-- Author : Brent Ozar
-- Date : 2009-04-01
-- Last Modified Date : 2012-02-20
-- Modified By : 
Clayton Kramer ckramer.kramer@gmail.com
Adrian Sullivan adriaan.sullivan@gmail.com
-- Description: This query returns indexes that SQL Server 2005(and higher) thinks are missing since the last restart.
-- The "SeekImpact" & "Scan Impact" columns are relative to the time of last restart and how bad SQL Server needs the index.
-- I consider over 10 to need attendtion.
-- Changes: Updated to expose full table name. This makes it easier to identify which database needs an index.
-- Modified the CreateIndexStatement to use the full table path and include the equality/inequality columns for easier identifcation.
-- Added the current Index usage to show a summary of unused indexes.. seeing as you are looking at indexes.
------------------------------------------------------------------ */

SET FMTONLY OFF/*This is for temporary tables in SSRS*/

DECLARE @MissingIndex TABLE(DB VARCHAR(250),SeekImpact DECIMAL(10,3), ScanImpact DECIMAL(10,3), [Table] VARCHAR(2000), ChangeIndexStatement VARCHAR(4000), equality_columns VARCHAR(4000), inequality_columns VARCHAR(4000), included_columns VARCHAR(4000))
DECLARE @HeapTable TABLE(DB VARCHAR(250), [schema] VARCHAR(250), [table] VARCHAR(250), [rows] BIGINT, user_seeks BIGINT, user_scans BIGINT, user_lookups BIGINT, user_updates BIGINT, last_user_seek DATETIME, last_user_scan DATETIME, last_user_lookup DATETIME)
DECLARE @NeverUsedTable TABLE(DB VARCHAR(250),Consideration VARCHAR(50),TableName VARCHAR(50),TypeDesc VARCHAR(50),IndexName VARCHAR(250),Updates BIGINT, ast_user_scan DATETIME,last_user_seek DATETIME,Pages BIGINT)

DECLARE @Database VARCHAR(255), @Table VARCHAR(255), @cmd NVARCHAR(3500),@cmd2 NVARCHAR(3500), @fillfactor INT, @pagemin INT, @fragmin INT, @WaitFor VARCHAR(12),@ActivateWait VARCHAR(1), @JustShowMe VARCHAR(1)
DECLARE @SQL NVARCHAR(4000)
SET @WaitFor = '00:00:15' /*hh:mm:ss Average time for a big index is around 2 minutes, and assume around 25% the trans log filled by each rebuild*/
SET @fillfactor = 80 /*Depending or your index growth anywhere between 70-90 should work*/
SET @pagemin = 1000 /*not concerned with small tables, ignore less than 1500 pages index*/
SET @fragmin = 5 /*Ignore fragmentation less than 5%*/
SET @ActivateWait = 0 /*1 will active it*/
SET @JustShowMe = 1 /*0 is off*/
DECLARE @Databases TABLE(id INT IDENTITY(1,1), databasename VARCHAR(250))
INSERT INTO @Databases
SELECT name FROM sys.databases db
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')
AND db.state <> 6
AND db.user_access <> 1
ORDER BY 1
--SELECT * FROM @Databases
SET @SQL = ''

DECLARE @i_Count INT, @i_Max INT
SET @i_Max = (SELECT MAX(id) FROM @Databases )
SET @i_Count = 1
WHILE @i_Count <= @i_Max
BEGIN
 SET @Database = (SELECT databasename FROM @Databases WHERE id = @i_Count)
 SET @SQL = '
 USE ['+@Database +']
 SELECT '''+@Database+''', (avg_total_user_cost * avg_user_impact/100.0) * (user_seeks ) SeekImpact
 , (avg_total_user_cost * avg_user_impact/100.0) * (user_scans) ScanImpact
 , [Table] = [statement]
 , [CreateIndexStatement] = ''CREATE NONCLUSTERED INDEX IX_'' + sys.objects.name COLLATE DATABASE_DEFAULT + ''_''
 + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,'''')+ISNULL(mid.inequality_columns,''''), ''['', ''''), '']'',''''), '', '',''_'') + '' ON '' + [statement]
 + REPLACE('' ( '' + IsNull(mid.equality_columns, '''')
 + CASE WHEN mid.inequality_columns IS NULL THEN '''' ELSE CASE WHEN mid.equality_columns IS NULL THEN '''' ELSE '','' END + mid.inequality_columns END + '' ASC ) '' ,'','','' ASC ,'') + CASE WHEN mid.included_columns IS NULL THEN '''' ELSE ''INCLUDE ('' + mid.included_columns + '')'' END
 + ''WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,FILLFACTOR = 90) ON [PRIMARY];''
 , mid.equality_columns
 , mid.inequality_columns
 , mid.included_columns
 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
 INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
 WHERE (migs.group_handle IN (SELECT TOP (500) group_handle FROM sys.dm_db_missing_index_group_stats WITH (nolock) ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
 AND OBJECTPROPERTY(sys.objects.OBJECT_ID, ''isusertable'') = 1
 ORDER BY SeekImpact DESC, ScanImpact DESC, [CreateIndexStatement] DESC

 '
 INSERT INTO @MissingIndex
 EXEC sp_executesql @SQL
 --PRINT @SQL

/*---------------------------------------Shows Indexes that have never been used---------------------------------------*/
 SET @SQL = '
 USE ['+@Database +']
 DECLARE @DaysAgo INT, @TheDate DATETIME
 SET @DaysAgo = 5
 SET @TheDate = CONVERT(DATETIME,CONVERT(INT,DATEADD(DAY,-@DaysAgo,GETDATE())))

DECLARE @db_id smallint, @tab_id int
 SET @db_id=db_id()
 SET @tab_id=object_id(''Production.Product'')

SELECT '''+@Database+''',
 CASE WHEN b.type_desc = ''CLUSTERED'' THEN ''Consider Carefully'' ELSE ''May remove'' END Consideration
 , t.name TableName
 , b.type_desc TypeDesc
 , b.name IndexName
 , a.user_updates Updates
 , a.last_user_scan
 , a.last_user_seek
 , SUM(aa.page_count) Pages
 FROM sys.dm_db_index_usage_stats as a
 JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
 LEFT OUTER JOIN sys.tables AS t ON b.[object_id] = t.[object_id]
 LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES isc ON isc.TABLE_NAME = t.name
 LEFT OUTER JOIN sys.dm_db_index_physical_stats (@db_id,@tab_id,NULL, NULL, NULL) AS aa ON aa.object_id = a.object_id
 WHERE
 (DATEDIFF(DAY,a.last_user_scan,GETDATE()) > @DaysAgo AND DATEDIFF(DAY,a.last_user_seek,GETDATE()) > @DaysAgo)
 AND t.name NOT LIKE ''sys%''
 GROUP BY t.name, b.type_desc, b.name, a.user_updates, a.last_user_scan, a.last_user_seek
 HAVING SUM(aa.page_count) > 500
 ORDER BY Pages DESC
 '
 INSERT INTO @NeverUsedTable
 EXEC sp_executesql @SQL
 --PRINT @SQL

 /*---------------------------------------Shows tables without primary key. Heaps---------------------------------------*/
 SET @SQL = '
 USE ['+@Database +']
 SELECT '''+@Database +''', SCHEMA_NAME(o.schema_id) AS [schema],object_name(i.object_id ) AS [table],p.rows,user_seeks,user_scans,user_lookups,user_updates,last_user_seek,last_user_scan,last_user_lookup
 FROM sys.indexes i
 INNER JOIN sys.objects o ON i.object_id = o.object_id
 INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
 LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
 WHERE i.type_desc = ''HEAP''
 AND SCHEMA_NAME(o.schema_id) NOT LIKE ''sys''
 AND rows > 500
 ORDER BY rows desc
 '
 INSERT INTO @HeapTable
 EXEC sp_executesql @SQL
 --PRINT @SQL

SET @i_Count = @i_Count + 1
END
SELECT 'MissingIndex' [ ] , * FROM @MissingIndex ORDER BY SeekImpact DESC
SELECT 'HeapTables' [ ] , * FROM @HeapTable
SELECT 'NeverUsedIndexes' [ ] , * FROM @NeverUsedTable

If there is demand for it I can make this a management script that can create/drop indexes or even the primary key stuff. Let me know if someone is interested.

Adrian