sqlserver 查询数量查看，并快速建立索引。

1.创建函数：fn_Index_CreateIndexName

-- CREATE FUNCTION fn_Index_CreateIndexName

    ALTER FUNCTION [dbo].[fn_Index_CreateIndexName] (@equality_columns NVARCHAR(4000), 
    @Inequality_columns NVARCHAR(4000), @index_handlE INT) RETURNS VARCHAR(128)
    AS
    BEGIN

    DECLARE @IndexName NVARCHAR(MAX)

    SET @IndexName = ISNULL(@equality_columns,@Inequality_columns)

    SET @IndexName = LTRIM(REPLACE(@IndexName,'[','_'))

    SET @IndexName = RTRIM(REPLACE(@IndexName,']','_'))

    SET @IndexName = REPLACE(@IndexName,',','')

    SET @IndexName = REPLACE(@IndexName,'_ _','_')

        IF LEN(@IndexName) > 120
        BEGIN

            SET @IndexName = SUBSTRING(@IndexName,0,120)

        END  

        SET @IndexName = @IndexName + CAST(@index_handlE AS NVARCHAR(15))

        RETURN @IndexName 
    END

 

2.建立视图：vw_Index_MissingIndex

SELECT     ROUND((s.avg_total_user_cost * s.avg_user_impact) * (s.user_seeks + s.user_scans), 0) AS [Total Cost], '[' + d.name + ']' AS DBName, 
                      dbo.fn_Index_CreateIndexName(mid.equality_columns, mid.inequality_columns, mid.index_handle) AS ID, REPLACE(mid.equality_columns, ',', ' ASC,') 
                      AS equality_columns, REPLACE(mid.inequality_columns, ',', ' ASC,') AS Inequality_columns, mid.included_columns, mid.statement
FROM         sys.dm_db_missing_index_groups AS g INNER JOIN
                      sys.dm_db_missing_index_group_stats AS s ON s.group_handle = g.index_group_handle INNER JOIN
                      sys.dm_db_missing_index_details AS mid ON mid.index_handle = g.index_handle INNER JOIN
                      sys.databases AS d ON d.database_id = mid.database_id

 

 

3.建立存储过程：usp_Index_MissingIndexCreationStatements

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[usp_Index_MissingIndexCreationStatements]    Script Date: 2021/11/4 9:31:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

    ALTER PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements]
    AS

    DECLARE @IndexCreationPlaceholder_Start  AS NVARCHAR(MAX)
    DECLARE @IndexCreationPlaceholder_End  AS NVARCHAR(MAX)

    -- PREPARE PLACEHOLDER

    SET @IndexCreationPlaceholder_Start = 'IF NOT EXISTS
    (SELECT * FROM {2}.sys.indexes WHERE [name] = ''IX_{0}'' )
                    BEGIN
                    CREATE NONCLUSTERED INDEX [IX_{0}] ON {1}'

    SET @IndexCreationPlaceholder_End = ' WITH (PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
    ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                    END;' + char(13) + char(10)

    -- STATEMENT CREATION

    SELECT
        [Total Cost],
        DBName,
        CASE
        WHEN NOT mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN
                    REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', 
                    mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
                    + '
                       ( ' +
                       COALESCE(mid.equality_columns,'') +
                       ' ASC,' + 
                       COALESCE(mid.Inequality_columns,'') +
                       ' ASC
                    )' +
                    COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
                    + @IndexCreationPlaceholder_End

            WHEN mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN
                    REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
                + '( ' + COALESCE(mid.Inequality_columns,'') + ' ASC) ' +
                COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
                + @IndexCreationPlaceholder_End

        WHEN NOT mid.equality_columns IS NULL AND mid.Inequality_columns IS NULL THEN
                REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', 
                mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
                + '( ' +COALESCE(mid.equality_columns,'') +  ' ASC ) ' +
                COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
                + @IndexCreationPlaceholder_End
        ELSE NULL
    END AS Index_Creation_Statement,
    ' DROP INDEX [IX_' + mid.ID  + '] ON ' + mid.[statement]  
        +  + char(13) + char(10) AS Index_Drop_Statement
    FROM [dbo].[vw_Index_MissingIndex] AS mid
    order by [Total Cost] desc