Finding Missing Indexes - Costs - Microsoft

 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 - Pinal Dave

https://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

-- Missing Index Script
-- Original Author: Pinal Dave
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

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