declare @dbid int
select @dbid = db_id()
select (cast((user_seeks + user_scans + user_lookups) as float) / case user_updates when 0 then 1.0 else cast(user_updates as float) end) * 100 as [%]
	, (user_seeks + user_scans + user_lookups) AS total_usage
	, objectname=object_name(s.object_id), s.object_id
	, indexname=i.name, i.index_id
	, user_seeks, user_scans, user_lookups, user_updates
	, last_user_seek, last_user_scan, last_user_update
	, last_system_seek, last_system_scan, last_system_update
	, 'DROP INDEX ' + i.name + ' ON ' + object_name(s.object_id) as [Command]
from sys.dm_db_index_usage_stats s,
	sys.indexes i
where database_id = @dbid 
and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
and i.name not like 'PK_%'
and object_name(s.object_id) = 'ClientFile'
order by [%] asc

CREATE TABLE [dbo].[LogDataFileIndexUsage](
	[PlanId] [int] IDENTITY(1,1) NOT NULL,
	[PlanDate] [datetime] NOT NULL CONSTRAINT [DF_LogDataFileIndexUsage_PlanDate]  DEFAULT (getutcdate()),
	[DBname] [varchar](50) NULL,
	[SPname] [varchar](256) NULL,
	[ScanCount] [int] NULL,
	[SeekCount] [int] NULL,
	[UpdateCount] [int] NULL,
	[RefCount] [int] NULL,
	[UseCount] [int] NULL,
	[QueryPlan] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[LogDataFileIndexUsage] ADD [IndexName] [varchar](256) NULL
PRIMARY KEY CLUSTERED 
(
	[PlanId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


GO

CREATE PROCEDURE [dbo].[spGetPlanUsingIndex]
	@indexName NVARCHAR(128)
AS
BEGIN	
	SET NOCOUNT ON;

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 	

	-- Make sure the name passed is appropriately quoted 
	IF (LEFT(@IndexName, 1) <> '[' AND RIGHT(@IndexName, 1) <> ']') SET @IndexName = QUOTENAME(@IndexName); 

	WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	INSERT INTO [dbo].[LogDataFileIndexUsage]
	(
		DBName,
		SPname, 
		ScanCount,
		SeekCount,
		UpdateCount,
		RefCount,
		UseCount,
		QueryPlan,
		IndexName
	)
	SELECT
		DB_NAME(E.dbid) AS [DBName],
		object_name(E.objectid, dbid) AS [ObjectName],
		E.query_plan.value('count(//RelOp[@LogicalOp = ''Index Scan'' or @LogicalOp = ''Clustered Index Scan'']/*/Object[@Index=sql:variable("@IndexName")])','int')  AS [ScanCount],
		E.query_plan.value('count(//RelOp[@LogicalOp = ''Index Seek'' or @LogicalOp = ''Clustered Index Seek'']/*/Object[@Index=sql:variable("@IndexName")])','int')  AS [SeekCount],
		E.query_plan.value('count(//Update/Object[@Index=sql:variable("@IndexName")])','int') AS [UpdateCount],	
		P.refcounts AS [RefCounts],
		P.usecounts AS [UseCounts],
		E.query_plan AS [QueryPlan],
		@IndexName
	FROM sys.dm_exec_cached_plans P
	CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) E
	WHERE	
		E.query_plan.exist('//*[@Index=sql:variable("@IndexName")]') = 1
	OPTION(MAXDOP 1, RECOMPILE);
END

GO

SELECT
mig.index_group_handle, mid.index_handle,
CONVERT (decimal (28,1),
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
) AS improvement_measure,
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '')
+ ' with (online=on)' AS create_index_statement,
migs.*, mid.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 --AND database_id = 12
AND mid.statement like ('%[ClientFile]')
ORDER BY convert(varchar(10), last_user_seek, 120) desc, migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) /*last_user_seek*/ DESC