Automatically Create and Delete Indexes Based on SQL Server’s Usage Metrics

originally posted 2014-06-26

1	originally posted 2014-06-26

Adapted from “Unique Indexes Are Code; Non-Unique Indexes Are Data” By Daniel White, 2014/06/26

create view [dbo].[vMissingIndexes]
as

/*
This view shows the server's suggestions for missing indexes that
should be created to improve performance based on actual
queries executed.
The formula for potential_user_benefit came straight from the
SQL Server Books Online documentation for the index-related
dynamic management view sys.dm_db_missing_index_group_stats:
http://msdn.microsoft.com/en-us/library/ms345421.aspx
I don't completely understand it, but it seems to work.
*/

select 
[IndexHandle] = mid.[index_handle]
,[DatabaseId] = mid.[database_id]
,[ObjectName] = mid.[statement]
,[EqualityColumns] = mid.[equality_columns]
,[InequalityColumns] = mid.[inequality_columns]
,[IncludedColumns] = mid.[included_columns]
,[UserSeeks] = migs.[user_seeks]
,[UserScans] = migs.[user_scans]
,[AvgTotalUserCost] = migs.[avg_total_user_cost]
,[AvgUserImpact] = migs.[avg_user_impact]
,[PotentialUserBenefit] = migs.[avg_total_user_cost] * migs.[avg_user_impact] * (migs.[user_seeks] + migs.[user_scans])
from sys.dm_db_missing_index_details mid
inner join sys.dm_db_missing_index_groups mig on mid.[index_handle] = mig.[index_handle]
inner join sys.dm_db_missing_index_group_stats migs on mig.[index_group_handle] = migs.[group_handle]
where (mid.[database_id] = db_id())
GO

-- return the maximum of 3 dates
create function [dbo].[fGetMax3DateTimes]
(
 @value1 datetime,
 @value2 datetime,
 @value3 datetime
)
returns datetime
as
begin
  declare @value datetime;
  select @value = MAX(value)
  from 
  (
    select @value1 value
    union
    select @value2 value
    union
    select @value3 value
  ) as allvalues;
  return @value;
end

/*
Indexes are selected for dropping by considering how long it has
been since they were last read and by comparing the number of
reads to the number of writes.
*/
create view [dbo].[vDroppableIndexes]
as
select [SchemaName] = [schemas].name
,[ObjectName] = [tables].name
,[IndexName] = [indexes].name
,[Usefulness] = isnull(ius.[user_seeks] + ius.[user_scans] + ius.[user_lookups] - ius.[user_updates], 0)
,[LastUserRead] = [dbo].[fGetMax3DateTimes](ius.[last_user_seek],ius.[last_user_scan],ius.[last_user_lookup])
,[LastUserUpdate] = ius.[last_user_update]
from sys.schemas [schemas]
inner join sys.tables [tables] on [schemas].[schema_id] = [tables].[schema_id]
inner join sys.indexes [indexes] on [tables].[object_id] = [indexes].[object_id]
left outer join sys.dm_db_index_usage_stats ius on ius.[database_id] = db_id() 
  and [indexes].[object_id] = ius.[object_id] 
  and [indexes].[index_id] = ius.[index_id]
where ([indexes].[is_unique] = 0)
GO

create function [dbo].[fGetMissingIndexName](@indexHandle int)
returns sysname
as
begin
  declare @indexName sysname;
  
  select @indexName = 'IX_' + obj.[name]
  from sys.objects obj
  inner join sys.dm_db_missing_index_details mid on obj.[object_id] = mid.[object_id]
  where mid.[index_handle] = @indexHandle
  and mid.[database_id] = db_id()

  select @indexName = @indexName + 
    case when [column_usage] = 'INCLUDE' then '#'
    else '_'
    end + [column_name]
  from sys.dm_db_missing_index_columns(@indexHandle);

  return @indexName;
end
GO

create procedure [dbo].[spCreateIndexes](@fullyQualifiedTableNamePattern nvarchar(255))
as
begin

  if (object_id('tempdb..#CreateIndexesTemp') is not null) drop table #CreateIndexesTemp
  if(@fullyQualifiedTableNamePattern is null or len(@fullyQualifiedTableNamePattern) = 0) set @fullyQualifiedTableNamePattern = '%'

  select 
  [ObjectName]
  ,[IndexName] = [dbo].[fGetMissingIndexName]([IndexHandle])
  ,[EqualityColumns]
  ,[InequalityColumns]
  ,[IncludedColumns]
  ,[PotentialUserBenefit]
  into #CreateIndexesTemp
  from [dbo].[vMissingIndexes]
  where [ObjectName] like @fullyQualifiedTableNamePattern

  declare @objectName sysname
  ,@indexName sysname
  ,@equalityColumns nvarchar(4000)
  ,@inequalityColumns nvarchar(4000)
  ,@includedColumns nvarchar(4000)
  ,@sql nvarchar(4000)

  while(1=1)
  begin
    set @indexName = null;

    select top 1
    @objectName = [ObjectName]
    ,@indexName = [IndexName]
    ,@equalityColumns = [EqualityColumns]
    ,@inequalityColumns = [InequalityColumns]
    ,@includedColumns = [IncludedColumns]
    from #CreateIndexesTemp
    order by [PotentialUserBenefit] desc;

    if(@indexName is not null)
    begin
      select @sql = 'create index ' + quotename(@indexName) +
        ' on ' + @objectName + ' (' +
        coalesce(@equalityColumns + ', ' +
            @inequalityColumns, @equalityColumns,
            @inequalityColumns) + ')' +
        isnull(' include (' + @includedColumns + ')', '');
      
      print @sql;
      exec (@sql);

      delete from #CreateIndexesTemp where [IndexName] = @indexName 
    end
    else break;
  end
end
GO

create PROCEDURE [dbo].[spDropIndexes](@fqnTableNamePattern nvarchar(255))
as
begin
  if (object_id('tempdb..#DropIndexesTemp') is not null) drop table #DropIndexesTemp
  if(@fqnTableNamePattern is null or len(@fqnTableNamePattern) = 0) set @fqnTableNamePattern = '%'
  
  select
  [SchemaName] 
  ,[ObjectName]
  ,[IndexName]
  ,[LastUserRead]
  ,[Usefulness]
  into #DropIndexesTemp
  from [dbo].[vDroppableIndexes]
  where [ObjectName] like @fqnTableNamePattern
  order by [LastUserRead],[Usefulness]

  declare @schemaName sysname,
    @objectName sysname,
    @indexName sysname,
    @sql nvarchar(4000);

  while(1=1)
  begin
    set @indexName = null;
    
    select top 1
    @schemaName = [SchemaName]
    ,@objectName = [ObjectName]
    ,@indexName = [IndexName]
    from #DropIndexesTemp
    order by [LastUserRead],[Usefulness]

    if(@indexName is not null)
    begin
      select @sql = 'DROP INDEX ' + quotename(@indexName) +
        ' ON ' + quotename(@schemaName) + '.' +
        quotename(@objectName) + ';';
      
      print @sql;
      exec (@sql);

      delete from #DropIndexesTemp where [IndexName] = @indexName 
    end
    else break;
  end
end
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

create view [dbo].[vMissingIndexes]

This view shows the server's suggestions for missing indexes that

should be created to improve performance based on actual

queries executed.

The formula for potential_user_benefit came straight from the

SQL Server Books Online documentation for the index-related

dynamic management view sys.dm_db_missing_index_group_stats:

http://msdn.microsoft.com/en-us/library/ms345421.aspx

I don't completely understand it, but it seems to work.

select

[IndexHandle] = mid.[index_handle]

,[DatabaseId] = mid.[database_id]

,[ObjectName] = mid.[statement]

,[EqualityColumns] = mid.[equality_columns]

,[InequalityColumns] = mid.[inequality_columns]

,[IncludedColumns] = mid.[included_columns]

,[UserSeeks] = migs.[user_seeks]

,[UserScans] = migs.[user_scans]

,[AvgTotalUserCost] = migs.[avg_total_user_cost]

,[AvgUserImpact] = migs.[avg_user_impact]

,[PotentialUserBenefit] = migs.[avg_total_user_cost] * migs.[avg_user_impact] * (migs.[user_seeks] + migs.[user_scans])

from sys.dm_db_missing_index_details mid

inner join sys.dm_db_missing_index_groups mig on mid.[index_handle] = mig.[index_handle]

inner join sys.dm_db_missing_index_group_stats migs on mig.[index_group_handle] = migs.[group_handle]

where (mid.[database_id] = db_id())

-- return the maximum of 3 dates

create function [dbo].[fGetMax3DateTimes]

(

@value1 datetime,

@value2 datetime,

@value3 datetime

)

returns datetime

begin

declare @value datetime;

select @value = MAX(value)

from

(

select @value1 value

union

select @value2 value

union

select @value3 value

) as allvalues;

return @value;

end

Indexes are selected for dropping by considering how long it has

been since they were last read and by comparing the number of

reads to the number of writes.

create view [dbo].[vDroppableIndexes]

select [SchemaName] = [schemas].name

,[ObjectName] = [tables].name

,[IndexName] = [indexes].name

,[Usefulness] = isnull(ius.[user_seeks] + ius.[user_scans] + ius.[user_lookups] - ius.[user_updates], 0)

,[LastUserRead] = [dbo].[fGetMax3DateTimes](ius.[last_user_seek],ius.[last_user_scan],ius.[last_user_lookup])

,[LastUserUpdate] = ius.[last_user_update]

from sys.schemas [schemas]

inner join sys.tables [tables] on [schemas].[schema_id] = [tables].[schema_id]

inner join sys.indexes [indexes] on [tables].[object_id] = [indexes].[object_id]

left outer join sys.dm_db_index_usage_stats ius on ius.[database_id] = db_id()

and [indexes].[object_id] = ius.[object_id]

and [indexes].[index_id] = ius.[index_id]

where ([indexes].[is_unique] = 0)

create function [dbo].[fGetMissingIndexName](@indexHandle int)

returns sysname

begin

declare @indexName sysname;

select @indexName = 'IX_' + obj.[name]

from sys.objects obj

inner join sys.dm_db_missing_index_details mid on obj.[object_id] = mid.[object_id]

where mid.[index_handle] = @indexHandle

and mid.[database_id] = db_id()

select @indexName = @indexName +

case when [column_usage] = 'INCLUDE' then '#'

else '_'

end + [column_name]

from sys.dm_db_missing_index_columns(@indexHandle);

return @indexName;

end

create procedure [dbo].[spCreateIndexes](@fullyQualifiedTableNamePattern nvarchar(255))

begin

if (object_id('tempdb..#CreateIndexesTemp') is not null) drop table #CreateIndexesTemp

if(@fullyQualifiedTableNamePattern is null or len(@fullyQualifiedTableNamePattern) = 0) set @fullyQualifiedTableNamePattern = '%'

select

[ObjectName]

,[IndexName] = [dbo].[fGetMissingIndexName]([IndexHandle])

,[EqualityColumns]

,[InequalityColumns]

,[IncludedColumns]

,[PotentialUserBenefit]

into #CreateIndexesTemp

from [dbo].[vMissingIndexes]

where [ObjectName] like @fullyQualifiedTableNamePattern

declare @objectName sysname

,@indexName sysname

,@equalityColumns nvarchar(4000)

,@inequalityColumns nvarchar(4000)

,@includedColumns nvarchar(4000)

,@sql nvarchar(4000)

while(1=1)

begin

set @indexName = null;

select top 1

@objectName = [ObjectName]

,@indexName = [IndexName]

,@equalityColumns = [EqualityColumns]

,@inequalityColumns = [InequalityColumns]

,@includedColumns = [IncludedColumns]

from #CreateIndexesTemp

order by [PotentialUserBenefit] desc;

if(@indexName is not null)

begin

select @sql = 'create index ' + quotename(@indexName) +

' on ' + @objectName + ' (' +

coalesce(@equalityColumns + ', ' +

@inequalityColumns, @equalityColumns,

@inequalityColumns) + ')' +

isnull(' include (' + @includedColumns + ')', '');

print @sql;

exec (@sql);

delete from #CreateIndexesTemp where [IndexName] = @indexName

end

else break;

end

create PROCEDURE [dbo].[spDropIndexes](@fqnTableNamePattern nvarchar(255))

begin

if (object_id('tempdb..#DropIndexesTemp') is not null) drop table #DropIndexesTemp

if(@fqnTableNamePattern is null or len(@fqnTableNamePattern) = 0) set @fqnTableNamePattern = '%'

select

[SchemaName]

,[ObjectName]

,[IndexName]

,[LastUserRead]

,[Usefulness]

into #DropIndexesTemp

from [dbo].[vDroppableIndexes]

where [ObjectName] like @fqnTableNamePattern

order by [LastUserRead],[Usefulness]

declare @schemaName sysname,

@objectName sysname,

@indexName sysname,

@sql nvarchar(4000);

while(1=1)

begin

set @indexName = null;

select top 1

@schemaName = [SchemaName]

,@objectName = [ObjectName]

,@indexName = [IndexName]

from #DropIndexesTemp

order by [LastUserRead],[Usefulness]

if(@indexName is not null)

begin

select @sql = 'DROP INDEX ' + quotename(@indexName) +

' ON ' + quotename(@schemaName) + '.' +

quotename(@objectName) + ';';

print @sql;

exec (@sql);

delete from #DropIndexesTemp where [IndexName] = @indexName

end

else break;

end