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
