SELECT
 d.\[object\_id\],
 s = OBJECT\_SCHEMA\_NAME(d.\[object\_id\]),
 o = OBJECT\_NAME(d.\[object\_id\]),
 d.equality\_columns,
 d.inequality\_columns,
 d.included\_columns,
 s.unique\_compiles,
 s.user\_seeks, s.last\_user\_seek,
 s.user\_scans, s.last\_user\_scan,
 'CREATE INDEX \[IX\_' + OBJECT\_NAME(d.OBJECT\_ID,d.database\_id) + '\_'
 + REPLACE(REPLACE(REPLACE(ISNULL(d.equality\_columns,''),', ','\_'),'\[',''),'\]','') +
 CASE
 WHEN d.equality\_columns IS NOT NULL AND d.inequality\_columns IS NOT NULL THEN '\_'
 ELSE ''
 END
 + REPLACE(REPLACE(REPLACE(ISNULL(d.inequality\_columns,''),', ','\_'),'\[',''),'\]','')
 + '\]'
 + ' ON ' + d.statement
 + ' (' + ISNULL (d.equality\_columns,'')
 + CASE WHEN d.equality\_columns IS NOT NULL AND d.inequality\_columns IS NOT NULL THEN ',' ELSE
 '' END
 + ISNULL (d.inequality\_columns, '')
 + ')'
 + ISNULL (' INCLUDE (' + d.included\_columns + ')', '') AS Create\_Statement
 INTO #candidates
 FROM sys.dm\_db\_missing\_index\_details AS d
 INNER JOIN sys.dm\_db\_missing\_index\_groups AS g
 ON d.index\_handle = g.index\_handle
 INNER JOIN sys.dm\_db\_missing\_index\_group\_stats AS s
 ON g.index\_group\_handle = s.group\_handle
 WHERE d.database\_id = DB\_ID()
 AND OBJECTPROPERTY(d.\[object\_id\], 'IsMsShipped') = 0;
 CREATE TABLE #planops
 (
 o INT,
 i INT,
 h VARBINARY(64),
 uc INT,
 Scan\_Ops INT,
 Seek\_Ops INT,
 Update\_Ops INT
 );

DECLARE @sql NVARCHAR(MAX) = N'';
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
 insert #planops
 select o = coalesce(T1.o, T2.o),
 i = coalesce(T1.i, T2.i),
 h = coalesce(T1.h, T2.h),
 uc = coalesce(T1.uc, T2.uc),
 Scan\_Ops = isnull(T1.Scan\_Ops, 0),
 Seek\_Ops = isnull(T1.Seek\_Ops, 0),
 Update\_Ops = isnull(T2.Update\_Ops, 0)
 from
 (
 select o = i.object\_id,
 i = i.index\_id,
 h = t.plan\_handle,
 uc = t.usecounts,
 Scan\_Ops = sum(case when t.LogicalOp in ('Index Scan', 'Clustered Index Scan') then 1 else 0 end),
 Seek\_Ops = sum(case when t.LogicalOp in ('Index Seek', 'Clustered Index Seek') then 1 else 0 end)
 from (
 select
 r.n.value('@LogicalOp', 'varchar(100)') as LogicalOp,
 o.n.value('@Index', 'sysname') as IndexName,
 pl.plan\_handle,
 pl.usecounts
 from sys.dm\_exec\_cached\_plans as pl
 cross apply sys.dm\_exec\_query\_plan(pl.plan\_handle) AS p
 cross apply p.query\_plan.nodes('//RelOp') as r(n)
 cross apply r.n.nodes('\*/Object') as o(n)
 where p.dbid = db\_id()
 and p.query\_plan is not null
 ) as t
 inner join sys.indexes as i
 on t.IndexName = quotename(i.name)
 where t.LogicalOp in ('Index Scan', 'Clustered Index Scan', 'Index Seek', 'Clustered Index Seek')
 and exists (select 1 from #candidates as c where c.object\_id = i.object\_id)
 group by i.object\_id,
 i.index\_id,
 t.plan\_handle,
 t.usecounts
 ) as T1
 full outer join
 (
 select o = i.object\_id,
 i = i.index\_id,
 h = t.plan\_handle,
 uc = t.usecounts,
 Update\_Ops = count(\*)
 from (
 select
 o.n.value('@Index', 'sysname') as IndexName,
 pl.plan\_handle,
 pl.usecounts
 from sys.dm\_exec\_cached\_plans as pl
 cross apply sys.dm\_exec\_query\_plan(pl.plan\_handle) AS p
 cross apply p.query\_plan.nodes('//Update') as r(n)
 cross apply r.n.nodes('Object') as o(n)
 where p.dbid = db\_id()
 and p.query\_plan is not null
 ) as t
 inner join sys.indexes as i
 on t.IndexName = quotename(i.name)
 where exists
 (
 select 1 from #candidates as c where c.\[object\_id\] = i.\[object\_id\]
 )
 and i.index\_id > 0
 group by i.object\_id,
 i.index\_id,
 t.plan\_handle,
 t.usecounts
 ) as T2
 on T1.o = T2.o and
 T1.i = T2.i and
 T1.h = T2.h and
 T1.uc = T2.uc;
 SELECT \[object\_id\], index\_id, user\_seeks, user\_scans, user\_lookups, user\_updates
 INTO #indexusage
 FROM sys.dm\_db\_index\_usage\_stats AS s
 WHERE database\_id = DB\_ID()
 AND EXISTS (SELECT 1 FROM #candidates WHERE \[object\_id\] = s.\[object\_id\]);
 ;WITH x AS
 (
 SELECT
 c.\[object\_id\],
 potential\_read\_ops = SUM(c.user\_seeks + c.user\_scans),
 \[write\_ops\] = SUM(iu.user\_updates),
 \[read\_ops\] = SUM(iu.user\_scans + iu.user\_seeks + iu.user\_lookups),
 \[write:read ratio\] = CONVERT(DECIMAL(18,2), SUM(iu.user\_updates)\*1.0 /
 SUM(iu.user\_scans + iu.user\_seeks + iu.user\_lookups)),
 current\_plan\_count = po.h,
 current\_plan\_use\_count = po.uc
 FROM
 #candidates AS c
 LEFT OUTER JOIN
 #indexusage AS iu
 ON c.\[object\_id\] = iu.\[object\_id\]
 LEFT OUTER JOIN
 (
 SELECT o, h = COUNT(h), uc = SUM(uc)
 FROM #planops GROUP BY o
 ) AS po
 ON c.\[object\_id\] = po.o
 GROUP BY c.\[object\_id\], po.h, po.uc
 )
 SELECT \[object\] = QUOTENAME(c.s) + '.' + QUOTENAME(c.o),
 c.equality\_columns,
 c.inequality\_columns,
 c.included\_columns,
 x.potential\_read\_ops,
 x.write\_ops,
 x.read\_ops,
 x.\[write:read ratio\],
 x.current\_plan\_count,
 x.current\_plan\_use\_count,
 c.create\_statement
 FROM #candidates AS c
 INNER JOIN x
 ON c.\[object\_id\] = x.\[object\_id\]
 ORDER BY x.\[write:read ratio\];

/\*
 drop table #candidates
 drop table #indexusage
 drop table #planops
 \*/

{{ < / hightlight > }}