Help! My server ate my SQL indexes
Updated: Jan 21, 2021
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