Table of Contents
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.
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.
- 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'
[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%'
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.
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)