Development progress w/e 23/09/2018 (Version 1.2)


Index advisor

A first part of the index advisor module – the missing index dashboard is now released and available for download. I have been experimenting with different charts to best show the impact of the missing indexes. Any feedback welcome. Big thanks to ColinDouglas92 for contributing to this release.

SQLWATCH Index advisor missing indexes

You will notice a new SQL Agent job, scheduled to run once a week, on a Saturday at 6am.


I plan to implement analysis of existing indexes to show how are they being used and flag up any unused and underutilized indexes where the write cost outweighs the read benefits

How we know what indexes are missing

SQL Server is kind enough to tell which indexes it *thinks* are missing based on the execution plans. This information is stored in the following Dynamic Management Views.

Note. Missing indexes reported by SQL Server are a guideline of what SQL thinks and are not always correct. Care should be taken before applying recommendations. Please make sure you understand your workload and data, do not apply indexes automatically without the full understanding of the implications and impact. There are sometimes valid reasons to not have an index despite the recommendation.
The numbers are estimated based on cost-based optimisation and they are all “could” and not “will”. SQL does not really know how useful the index would have been unless this index is created and performance measured. From the Microsoft Docs:
  • avg_total_user_cost – Average cost of the user queries that could be reduced by the index in the group.
  • avg_user_impact – Average percentage benefit that user queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented.

I created a quadrant chart on the SQLWATCH dashboard that takes into account avereage_total_cost (x), average_user_impact (y) and the number of seeks (size). I am hoping this will give you a better idea of the potential benefit and impact.

How we capture missing indexes

	[server_name] = @@servername ,
	[database_name] = db.[database_name], 
	[database_create_date] = db.[database_create_date],
	[object_name] = parsename(mi.[statement],2) + '.' + parsename(mi.[statement],1), 
	[snapshot_time] = @snapshot_time,
	[index_tsql] = 'CREATE INDEX SQLWATCH_AUTOIDX_' + rtrim(convert(char(100),mi.[index_handle])) + 
		'_' + convert(varchar(10),getutcdate(),112) + ' ON ' + mi.statement + ' (' + 
		case when [equality_columns] is not null then [equality_columns] else '' end + 
		case when [equality_columns] is not null and [inequality_columns] is not null then ', ' else '' end + 
		case when [inequality_columns] is not null then [inequality_columns] else '' end + ') ' + 
		case when [included_columns] is not null then 'INCLUDE (' + [included_columns] + ')' else '' end
		+ N' WITH (' 
			+ case when @allows_online_index = 1 then N'ONLINE=ON,' else N'' end + N'SORT_IN_TEMPDB=ON' 
		+ N')',
	[snapshot_type_id] = @snapshot_type
from sys.dm_db_missing_index_groups ig 

	inner join sys.dm_db_missing_index_group_stats igs 
	on igs.group_handle = ig.index_group_handle 

	inner join sys.dm_db_missing_index_details mi 
	on ig.index_handle = mi.index_handle

	inner join sys.databases sdb
	on sdb.[name] = db_name(mi.[database_id])
	and sdb.database_id > 4
	and sdb.[name] not like '%ReportServer%'

	inner join [dbo].[sql_perf_mon_database] db
	on db.[database_name] = db_name(mi.[database_id])
	and db.[database_create_date] = sdb.[create_date]

We are also generating the CREATE INDEX statement for all missing indexes to make it easier to apply them. Enterprise Edition can create and rebuild indexes in ONLINE mode whereas Standard Edition cannot. To make use of ONLINE index creation in the Enterprise edition we need to check which Edition we are running on , and conditionally include the ONLINE=ON statement in the creation command

	declare @allows_online_index bit
	select @allows_online_index = case 
					convert(varchar(4000),serverproperty('Edition')) like 'Enterprise%' 
				or	convert(varchar(4000),serverproperty('Edition')) like 'Developer%'
			then 1
			else 0

Impact calculation

The impact column visible on the dashboard is a result of the below formula in PowerBI which takes avg cost, impact and number of user seeks into account:

Impact = MissingIndex[user_seeks] * MissingIndex[avg_total_user_cost] * (MissingIndex[avg_user_impact] / 100)


As mentioned above, these recommendations are just a guideline based on cost-estimation and are not always correct. They do not distinguish between clustered and non-clustered indexes and do not take storage impact and write penalties into account.


Indexes are logged under a dedicated snapshot_type_id = 3 thanks to which, we can set a different retention period. By default, missing indexes will be removed after 30 days from the SQLWATCH.

New Formatting

You will also notice the new dashboard has a slightly different layout, with an apparent purple bar on top. This isn’t me being inconsistent and random but rather me trying to find the best color scheme and design for PowerBI dashboards. Eventually I will re-align all dashboards to follow one standard. Again, any feedback welcome.


You can download this releases from GitHub: v.1.2.6841.38712 (2018-09-24)

Table of Contents
Marcin Gminski

Marcin Gminski

Following his degree in Telecommunications Engineering and Information Technology, Marcin has gained over 20 years’ experience and knowledge of the SQL Server administration, E2E performance testing and tuning, database development life cycle covering technical architecture, data analysis, logical and physical design including disaster recovery, specification, coding, implementation and support. Marcin has delivered projects in banking, retail, automotive and medical industries.
This site, like most websites, uses cookies. By continuing to use this website, you agree to their use.