Help! My server ate my SQL indexes

Updated: Jan 20

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
Adrian Sullivan
-- 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))