AWS RDS Basic TSQL Coding (IT) DBA Tasks SQL Server SSRS

New Index Advantage Report for 2025

By David Speight / December 22, 2025

Years ago, I borrowed some great code that was out there for suggesting INDEXES on tables. Then turned that into an SSRS Report. With the recent news that SSRS is being phased out and replaced with Power Bi, rewriting this report into BI seemed logical — BUT WAIT — Was that the only choice I had? Of course not. After some considerations, perhaps unique to my company, I decided that a report purely generated in SQL Server might be the best solution for now.

The code in this blog post ONLY works for SQL Server versions 2022+ Use at your own risk. You may have to revise a few lines in the Stored Procedure to make it work on SQL Server version 2019 or lower.

HERE is what my blog post is going to cover today!

  • A VIEW to gather the INDEX SUGGESTIONS into a table.
  • A STORED PROCEDURE to create an HTML Report and mail it.
  • A SQL AGENT JOB to run the procedure on a schedule.

VIEW to grab Index Suggestions

The code here may look familiar to many. Like I mentioned earlier, I borrowed this from a presenter at SQL SATURDAY in Orlando, Florida, years ago. And I’ve seen similar on many other blogs.

A few considerations my code needs to meet company best practices are:

  • Use our INDEX naming convention of IX_tableName_FirstColumName
  • I want to filter out any INDEXES for databases containing the letters RDS, since those are used by AWS RDS and I wouldn’t have access to update them.
  • More later … For now the VIEW is going to set [PRIMARY] as the File Group on each item.

Let’s start with the VIEW I’m creating.

CREATE VIEW [dbo].[vIndexAdvantageGet]
AS
-- 20251218 By David Speight + Others

SELECT	index_advantage,
unique_compiles,
user_seeks,
last_user_seek,
mid.statement,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
REPLACE(REPLACE(REPLACE(	CONCAT('CREATE NONCLUSTERED INDEX ',
	REPLACE(REPLACE(SUBSTRING(statement,(CHARINDEX('].[',statement,(CHARINDEX('].[',statement)+1))+2),150),'[','[IX_'),']',''),
	'_',
	CASE	WHEN LEN(mid.equality_columns) > 1 THEN
	(CASE WHEN CHARINDEX(',',mid.equality_columns) > 0 THEN
		REPLACE(REPLACE(   LEFT(mid.equality_columns,(CHARINDEX(',',mid.equality_columns)-1) )    ,'[',''),']','')
		ELSE REPLACE(REPLACE(mid.equality_columns,'[',''),']','') END)
	WHEN LEN(mid.inequality_columns) > 1 THEN
		(CASE WHEN CHARINDEX(',',mid.inequality_columns) > 0 THEN
			REPLACE(REPLACE(   LEFT(mid.inequality_columns,(CHARINDEX(',',mid.inequality_columns)-1) )    ,'[',''),']','')
			ELSE REPLACE(REPLACE(mid.inequality_columns,'[',''),']','') END)
	ELSE '1' END,
	'] ON ',
	SUBSTRING(statement,(CHARINDEX('].[',statement)+2),150),
	' (',
	REPLACE(mid.equality_columns,']','] ASC'),
	', ',
	REPLACE(mid.inequality_columns,']','] ASC'),
	') INCLUDE (',
	mid.included_columns,
	') WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY];'
	) , 
'INCLUDE ()', ' '), 
'ASC, )','ASC)'),
'(, [','([')
AS IndexSql
FROM
(SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, 
migs.unique_compiles,
migs.user_seeks,
migs.last_user_seek,
migs.group_handle
FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs_adv.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
WHERE (migs_adv.index_advantage > 1000) 
and (mid.statement NOT LIKE '%rds%');

GO

STORED PROCEDURE to Create the Report and Email It

The STORED PROCEDURE is going to do the following steps.

  • Load the INDEX ADVANTAGE VIEW into a temp table for use.
  • Loop through the table to find the correct File Group
  • Update the Tsql to use the correct File Group name.
  • Create the HTML Report
  • Email the Report

A few considerations my code needed to meet company best practices were:

  • Use our INDEX naming convention if IX_tableName_FirstColumName.
  • I want to filter out any INDEXES for databases containing the letters RDS.
  • Create the new index on a FileGroup reserved for INDEXES.

The last one might not be familiar to everyone, so let me explain. We have a few databases that are very large. For better performance, we put the TABLE data, INDEX data, and LOB data on separate File Groups. And it gets more extensive with separate groups for Finance, Applications, etc. So my code will need to identify which File Group in which to place the Index. When we can’t determine, we’ll default to [PRIMARY].

FIRST PART: Loading the INDEX VIEW

DECLARE @tIndexList as TABLE (	
	ID INT IDENTITY(1,1),
	TablePath varchar(512),
	index_advantage float,
	DatabaseName varchar(200),
	SchemaName varchar(100),
	TableName varchar(200),
	equality_columns varchar(2000),
	inequality_columns varchar(2000),
	included_columns varchar(4000),
	IndexSql varchar(8000),
	FileGroupName Varchar(512));

-- 1.  LOAD INDEXES INTO TEMP TABLE
INSERT INTO @tIndexList (TablePath, index_advantage, DatabaseName, SchemaName, TableName, equality_columns, inequality_columns, included_columns, IndexSql )
SELECT statement, index_advantage, 
(SELECT value from  STRING_SPLIT(statement,'.',1) where ordinal = 1),
(SELECT value from  STRING_SPLIT(statement,'.',1) where ordinal = 2),
(SELECT value from  STRING_SPLIT(statement,'.',1) where ordinal = 3),
ISNULL(equality_columns,''), ISNULL(inequality_columns,''), ISNULL(included_columns,''), IndexSql 
FROM [dbo].[vIndexAdvantageGet]
ORDER BY 3,2;

SECOND PART: Next, I will loop through my temp table and look up the correct File Group, siince we don’t want to use [PRIMARY] where there might be a better choice.

-- 2.  LOOP THROUGH TABLE, Adding the FileGroup Looked up in a spare column
SELECT @iLoop = MAX(ID) FROM @tIndexList;
WHILE @iLoop > 0
BEGIN
SELECT @Path = TablePath FROM @tIndexList WHERE ID = @iLoop;

-- Only works in SQL 2022+
SELECT @DatabaseName= value from  STRING_SPLIT(@Path,'.',1) where ordinal = 1
SELECT @SchemName= value from  STRING_SPLIT(@Path,'.',1) where ordinal = 2
SELECT @TableName= REPLACE(REPLACE(value,'[',''),']','') from  STRING_SPLIT(@Path,'.',1) where ordinal = 3
SELECT @tSql =
		CONCAT('SELECT DISTINCT ds.name as FileGroupName
		FROM ',@DatabaseName,'.sys.indexes AS ind
		INNER JOIN ',@DatabaseName,'.sys.tables AS t ON ind.object_id = t.object_id
		LEFT JOIN ',@DatabaseName,'.sys.data_spaces AS ds ON ind.data_space_id = ds.data_space_id
		WHERE t.Name = ''',@TableName,'''');

INSERT INTO @FgTable ( FileGroupname)
EXEC sp_executesql @tSql

-- This will find if there is a seperate INDEX filegroup for ONLY INDEXES associated with the table.
IF (SELECT COUNT(*) FROM @FgTable WHERE FileGroupName like '%index%') > 0
BEGIN
	SELECT TOP(1) @TheFileGroupname =  FileGroupName FROM @FgTable WHERE FileGroupName like '%index%';
END
ELSE
BEGIN
	SELECT TOP(1)  @TheFileGroupname =  FileGroupName FROM @FgTable;
END;

UPDATE @tIndexList
SET FileGroupName = @TheFileGroupname
WHERE ID = @iLoop;

DELETE
FROM @FgTable;

SELECT	@iLoop = @iLoop-1,
	@TheFileGroupname='',
	@Path='',
	@DatabaseName='',
	@SchemName='',
	@TableName='',
	@tSql='';
END;

THIRD PART: Then I will update the Tsql column in my table with the correct File Group name.

UPDATE @tIndexList
SET IndexSql = REPLACE(IndexSql,'PRIMARY',FileGroupName)
WHERE LEN(FileGroupName) > 2;

FOURTH PART: Now I’ll create the HTML Report.

-- 4. Output results into an Email with HTML format
IF LEN(@E_profile_name) > 2 
BEGIN

DECLARE @body1 nvarchar(max);
DECLARE @ServerName nvarchar(250);
DECLARE @bodyFIN nvarchar(max);
DECLARE @StyleSheet VARCHAR(1000);
DECLARE @Header VARCHAR(400);
DECLARE @GreenTitle VARCHAR(250);

SELECT TOP(1) @ServerName = ServerResolvedName FROM ServerAnalysis.Analysis.Server WHERE ServerNm = @@SERVERNAME;

SELECT @StyleSheet = 
'<style><!--
@font-face
{font-family:"Microsoft Sans Serif";
panose-1:2 11 6 4 2 2 2 2 2 4;}
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}

.top-aligned-table td, .top-aligned-table th {
vertical-align: top;
}
table, th, td {
border: 1px solid black; 
padding: 0.5em; 
}

--></style>',
@Header = CONCAT('<p class="MsoNormal" style="background:white"><span style="font-size:16pt;color:#009A44;letter-spacing:-1.0pt">MY</span><span style="font-size:16pt;color:#0C2340">COMPANY NAME</span></p><BR /><span style="font-size:16pt;color:#0C2340"><p><span style="font-size:16pt;color:#0C2340">INDEX SUGGESTIONS ON ',CONVERT(varchar(12),getdate()),' </span></p>'),
@GreenTitle = '<p class="MsoNormal" style="background:white"><span style="font-size:16.0pt;font-family:&amp;quot;color:#009A44;letter-spacing:-1.0pt">'


-- INDEX TABLE
set @body1 = cast( (
select td = (d.DatabaseName + '</td><td>' + CONVERT(varchar(24),d.index_advantage) + '</td><td>' + d.SchemaName + '</td><td>' + d.TableName + '</td><td>' + d.equality_columns + '</td><td>' + d.inequality_columns + '</td><td>' + d.included_columns + '</td><td>' + d.IndexSql + '</td><td>' + d.FileGroupName) 
from (
	SELECT  
		DatabaseName,
		index_advantage,
		SchemaName,
		TableName,
		equality_columns,
		inequality_columns,
		included_columns,
		IndexSql,
		FileGroupName
	FROM @tIndexList  
	) as d
for xml path( 'tr' ), type ) as varchar(max) )
set @body1 = '<table class="top-aligned-table">'
		+ '<tr><th style=''background:#C6EFCE;''>Database</th><th style=''background:#C6EFCE''>Index Advantage</th><th style=''background:#C6EFCE''>Schema</th><th style=''background:#C6EFCE''>Table</th><th style=''background:#C6EFCE''>equality_columns</th><th style=''background:#C6EFCE''>Inequality Columns</th><th style=''background:#C6EFCE''>Included Columns</th><th style=''background:#C6EFCE''>Index Sql</th><th style=''background:#C6EFCE''>File Group</th></tr>'
		+ replace( replace( @body1, '&lt;', '<' ), '&gt;', '>' )
		+ '</table>';
SELECT @body1 = LEFT(@body1,40000) -- Limit to 40 of the 64k 

SELECT @bodyFIN = CONCAT( @StyleSheet,@Header,'<BR />',@GreenTitle,UPPER(@ServerName),' INDEX SUGGESTIONS</span></p><BR />', @body1,'<BR /><BR /><BR /><BR />Report Generated by ',DB_NAME() ,'.dbo.IndexSuggestions2025<BR /><BR />')
SELECT @bodyFIN = LEFT(@bodyFIN,60000); -- char limit 64k, leaving space for other vars

FINAL PART: And finally, I’ll give my user the option of viewing the results or emailing the report.

If LEN(@bodyFIN) > 10
BEGIN
DECLARE @SubjectTxt   VARCHAR(50);
SELECT @SubjectTxt   = CONCAT(UPPER(@ServerName),' INDEX SUGGESTIONS');
EXEC msdb.dbo.sp_send_dbmail
	@profile_name = @E_profile_name,
	@recipients = @E_recipients,
	@body = @bodyFIN,
	@subject = @SubjectTxt,
	@body_format = 'HTML'; 
END
END
ELSE
BEGIN
SELECT * FROM @tIndexList ORDER BY DatabaseName, index_advantage DESC;
END;

Here is what the report could look like:

Here is all the code in the STORED PROCEDURE put together.

/****** Object:  StoredProcedure [dbo].[IndexSuggestions2025]    Script Date: 12/22/2025  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		David Speight and Others
-- Create date: 20251219
-- Description:	INDEX Suggestions.  SET @E_profile_name = 'NO' for results only w/o email sent
-- =============================================
ALTER   PROCEDURE [dbo].[IndexSuggestions2025] 
@E_profile_name nvarchar(250)='SQLAgentEmailProfileName',
@E_recipients nvarchar(2000)='dave@lovethesql.com'
AS
BEGIN

SET NOCOUNT ON;
DECLARE @Path varchar(500);
DECLARE @DatabaseName varchar(200);
DECLARE @SchemName varchar(100);
DECLARE @TableName varchar(200);
DECLARE @tSql nvarchar(3000);
DECLARE @FgTable as TABLE (ID INT IDENTITY(1,1), FileGroupName Varchar(512))
DECLARE @TheFileGroupname varchar(255)
DECLARE @iLoop INT;
DECLARE @tIndexList as TABLE (	
	ID INT IDENTITY(1,1),
	TablePath varchar(512),
	index_advantage float,
	DatabaseName varchar(200),
	SchemaName varchar(100),
	TableName varchar(200),
	equality_columns varchar(2000),
	inequality_columns varchar(2000),
	included_columns varchar(4000),
	IndexSql varchar(8000),
	FileGroupName Varchar(512));

-- 1.  LOAD INDEXES INTO TEMP TABLE
INSERT INTO @tIndexList (TablePath, index_advantage, DatabaseName, SchemaName, TableName, equality_columns, inequality_columns, included_columns, IndexSql )
SELECT statement, index_advantage, 
(SELECT value from  STRING_SPLIT(statement,'.',1) where ordinal = 1),
(SELECT value from  STRING_SPLIT(statement,'.',1) where ordinal = 2),
(SELECT value from  STRING_SPLIT(statement,'.',1) where ordinal = 3),
ISNULL(equality_columns,''), ISNULL(inequality_columns,''), ISNULL(included_columns,''), IndexSql 
FROM [dbo].[vIndexAdvantageGet]
ORDER BY 3,2;

-- 2.  LOOP THROUGH TABLE, Adding the FileGroup Looked up in a spare column
SELECT @iLoop = MAX(ID) FROM @tIndexList;
WHILE @iLoop > 0
BEGIN
SELECT @Path = TablePath FROM @tIndexList WHERE ID = @iLoop;

-- Only works in SQL 2022+
SELECT @DatabaseName= value from  STRING_SPLIT(@Path,'.',1) where ordinal = 1
SELECT @SchemName= value from  STRING_SPLIT(@Path,'.',1) where ordinal = 2
SELECT @TableName= REPLACE(REPLACE(value,'[',''),']','') from  STRING_SPLIT(@Path,'.',1) where ordinal = 3
SELECT @tSql =
		CONCAT('SELECT DISTINCT ds.name as FileGroupName
		FROM ',@DatabaseName,'.sys.indexes AS ind
		INNER JOIN ',@DatabaseName,'.sys.tables AS t ON ind.object_id = t.object_id
		LEFT JOIN ',@DatabaseName,'.sys.data_spaces AS ds ON ind.data_space_id = ds.data_space_id
		WHERE t.Name = ''',@TableName,'''');

INSERT INTO @FgTable ( FileGroupname)
EXEC sp_executesql @tSql

-- This will find if there is a seperate INDEX filegroup for ONLY INDEXES associated with the table.
IF (SELECT COUNT(*) FROM @FgTable WHERE FileGroupName like '%index%') > 0
BEGIN
	SELECT TOP(1) @TheFileGroupname =  FileGroupName FROM @FgTable WHERE FileGroupName like '%index%';
END
ELSE
BEGIN
	SELECT TOP(1)  @TheFileGroupname =  FileGroupName FROM @FgTable;
END;

UPDATE @tIndexList
SET FileGroupName = @TheFileGroupname
WHERE ID = @iLoop;

DELETE
FROM @FgTable;

SELECT	@iLoop = @iLoop-1,
	@TheFileGroupname='',
	@Path='',
	@DatabaseName='',
	@SchemName='',
	@TableName='',
	@tSql='';
END;

-- 3.UPDATE the TSQL Create Statement REPLACING [PRIMARY] with actual FileGroup
UPDATE @tIndexList
SET IndexSql = REPLACE(IndexSql,'PRIMARY',FileGroupName)
WHERE LEN(FileGroupName) > 2;

-- 4. Output results into an Email with HTML format
IF LEN(@E_profile_name) > 2 
BEGIN

DECLARE @body1 nvarchar(max);
DECLARE @ServerName nvarchar(250);
DECLARE @bodyFIN nvarchar(max);
DECLARE @StyleSheet VARCHAR(1000);
DECLARE @Header VARCHAR(400);
DECLARE @GreenTitle VARCHAR(250);

SELECT TOP(1) @ServerName = ServerResolvedName FROM ServerAnalysis.Analysis.Server WHERE ServerNm = @@SERVERNAME;

SELECT @StyleSheet = 
'<style><!--
@font-face
{font-family:"Microsoft Sans Serif";
panose-1:2 11 6 4 2 2 2 2 2 4;}
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}

.top-aligned-table td, .top-aligned-table th {
vertical-align: top;
}
table, th, td {
border: 1px solid black; 
padding: 0.5em; 
}

--></style>',
@Header = CONCAT('<p class="MsoNormal" style="background:white"><span style="font-size:16pt;color:#009A44;letter-spacing:-1.0pt">MY</span><span style="font-size:16pt;color:#0C2340">COMPANY NAME</span></p><BR /><span style="font-size:16pt;color:#0C2340"><p><span style="font-size:16pt;color:#0C2340">INDEX SUGGESTIONS ON ',CONVERT(varchar(12),getdate()),' </span></p>'),
@GreenTitle = '<p class="MsoNormal" style="background:white"><span style="font-size:16.0pt;font-family:&amp;quot;color:#009A44;letter-spacing:-1.0pt">'


-- INDEX TABLE
set @body1 = cast( (
select td = (d.DatabaseName + '</td><td>' + CONVERT(varchar(24),d.index_advantage) + '</td><td>' + d.SchemaName + '</td><td>' + d.TableName + '</td><td>' + d.equality_columns + '</td><td>' + d.inequality_columns + '</td><td>' + d.included_columns + '</td><td>' + d.IndexSql + '</td><td>' + d.FileGroupName) 
from (
	SELECT  
		DatabaseName,
		index_advantage,
		SchemaName,
		TableName,
		equality_columns,
		inequality_columns,
		included_columns,
		IndexSql,
		FileGroupName
	FROM @tIndexList  
	) as d
for xml path( 'tr' ), type ) as varchar(max) )
set @body1 = '<table class="top-aligned-table">'
		+ '<tr><th style=''background:#C6EFCE;''>Database</th><th style=''background:#C6EFCE''>Index Advantage</th><th style=''background:#C6EFCE''>Schema</th><th style=''background:#C6EFCE''>Table</th><th style=''background:#C6EFCE''>equality_columns</th><th style=''background:#C6EFCE''>Inequality Columns</th><th style=''background:#C6EFCE''>Included Columns</th><th style=''background:#C6EFCE''>Index Sql</th><th style=''background:#C6EFCE''>File Group</th></tr>'
		+ replace( replace( @body1, '&lt;', '<' ), '&gt;', '>' )
		+ '</table>';
SELECT @body1 = LEFT(@body1,40000) -- Limit to 40 of the 64k 

SELECT @bodyFIN = CONCAT( @StyleSheet,@Header,'<BR />',@GreenTitle,UPPER(@ServerName),' INDEX SUGGESTIONS</span></p><BR />', @body1,'<BR /><BR /><BR /><BR />Report Generated by ',DB_NAME() ,'.dbo.IndexSuggestions2025<BR /><BR />')
SELECT @bodyFIN = LEFT(@bodyFIN,60000); -- char limit 64k, leaving space for other vars

If LEN(@bodyFIN) > 10
BEGIN
DECLARE @SubjectTxt   VARCHAR(50);
SELECT @SubjectTxt   = CONCAT(UPPER(@ServerName),' INDEX SUGGESTIONS');
EXEC msdb.dbo.sp_send_dbmail
	@profile_name = @E_profile_name,
	@recipients = @E_recipients,
	@body = @bodyFIN,
	@subject = @SubjectTxt,
	@body_format = 'HTML'; 
END
END
ELSE
BEGIN
SELECT * FROM @tIndexList ORDER BY DatabaseName, index_advantage DESC;
END;
END

Schedule Agent Job To Send

I will now create a job in the SQL SERVER AGENT that will email me the report every Friday morning at 7:00 AM. here is a screenshot of the basic set I used.

Have a great day!

Tags: , , ,

You may also like