SQLServer-Common/dbo.sp_GetMissingIndexs.sql at main · jahangard/SQLServer-Common · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO


-- =============================================
-- Author:		Mehdi Jahangard
-- Create date: 2018/07/11
-- Description:
-- =============================================
CREATE   PROCEDURE [dbo].[sp_GetMissingIndexs]
AS
BEGIN
    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 [MIX_'+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
END
GO