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.
sys.dm_db_missing_index_details sys.dm_db_missing_index_group_stats sys.dm_db_missing_index_groups
- 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
select [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, mi.[index_handle], igs.[last_user_seek], igs.[unique_compiles], igs.[user_seeks], igs.[user_scans], igs.[avg_total_user_cost], igs.[avg_user_impact], [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 when convert(varchar(4000),serverproperty('Edition')) like 'Enterprise%' or convert(varchar(4000),serverproperty('Edition')) like 'Developer%' then 1 else 0 end
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)