Finding Missing Indexes - Costs

 SET TRANSACTION ISOLATION LEVEL

READ UNCOMMITTED

SELECT TOP 20

 ROUND(s.avg_total_user_cost *

       s.avg_user_impact

        * (s.user_seeks + s.user_scans),0)

                 AS [Total Cost]

 ,d.[statement] AS [Table Name]

 ,equality_columns

 ,inequality_columns

 ,included_columns

FROM sys.dm_db_missing_index_groups g

INNER JOIN sys.dm_db_missing_index_group_stats s

  ON s.group_handle = g.index_group_handle

INNER JOIN sys.dm_db_missing_index_details d

  ON d.index_handle = g.index_handle

ORDER BY [Total Cost] DESC

Author: http://www.i-programmer.info/programming/database/3208-improve-sql-performance-find-your-missing-indexes.html

Finding Missing Indexes - Costs (scripted)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 50 ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans), 0) AS [Total Cost]

    ,'create index ' + replace(replace(replace(d.[statement], '[', '_'), ']', '_'), '.', '') + '_' + CAST([dbo].[mxfn_ParseDateTimeToCHAR](GETDATE()) AS NVARCHAR(MAX)) + ' on ' + d.[statement] + ' (' + CASE

        WHEN equality_columns IS NULL

            AND inequality_columns IS NULL

            THEN ''

        WHEN equality_columns IS NOT NULL

            AND inequality_columns IS NOT NULL

            THEN equality_columns + ',' + inequality_columns

        WHEN equality_columns IS NULL

            AND inequality_columns IS NOT NULL

            THEN inequality_columns

        WHEN equality_columns IS NOT NULL

            AND inequality_columns IS NULL

            THEN equality_columns

        ELSE 'WTF'

        END + ')' + CASE

        WHEN included_columns IS NOT NULL

            THEN 'INCLUDE(' + included_columns + ')'

        ELSE ''

        END AS [Line to Run to Create needed Index]

/*,equality_columns

,inequality_columns

,included_columns*/

FROM sys.dm_db_missing_index_groups g

INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle

INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle

ORDER BY [Total Cost] DESC

mxfn_ParseDateTimeToCHAR

/****** Object:  UserDefinedFunction [dbo].[mxfn_ParseDateTimeToFloat]    Script Date: 7/1/2020 2:07:46 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[mxfn_ParseDateTimeToCHAR] (@inputDateTime DATETIME)

RETURNS NVARCHAR(MAX)

AS

BEGIN

    DECLARE @ret FLOAT

    DECLARE @tempFloat FLOAT

    SET @ret = 0

    SET @tempFloat = DATEPART(YEAR, @inputDateTime)

    SET @tempFloat = @tempFloat * 10000000000

    SET @ret = @ret + @tempFloat

    SET @tempFloat = DATEPART(MONTH, @inputDateTime)

    SET @tempFloat = @tempFloat * 100000000

    SET @ret = @ret + @tempFloat

    SET @tempFloat = DATEPART(DAY, @inputDateTime)

    SET @tempFloat = @tempFloat * 1000000

    SET @ret = @ret + @tempFloat

    SET @tempFloat = DATEPART(HOUR, @inputDateTime)

    SET @tempFloat = @tempFloat * 10000

    SET @ret = @ret + @tempFloat

    SET @tempFloat = DATEPART(MINUTE, @inputDateTime)

    SET @tempFloat = @tempFloat * 100

    SET @ret = @ret + @tempFloat

    SET @ret = @ret + DATEPART(SECOND, @inputDateTime)

    RETURN CONVERT(VARCHAR(100), CONVERT(DECIMAL(30, 0), @ret)) --str(@ret)

END

GO