Sqlserver自動查詢缺失索引及拼出建立索引的語句的指令碼

官方文件



avg_total_user_cost 可透過組中的索引減少的使用者查詢的平均成本
avg_user_impact     實現此缺失索引組後，使用者查詢可能獲得的平均百分比收益。 該值表示如果實現此缺失索引組，則查詢成本將按此百分比平均下降
user_seeks         由可能使用了組中建議索引的使用者查詢所導致的查詢次數
user_scans         由可能使用了組中建議索引的使用者查詢所導致的掃描次數


(migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)) * (migs.user_seeks + migs.user_scans) > 100000
平均成本×平均收益×訪問次數,如果一個表的索引平均成本×平均收益是3左右，但是自上次查詢日期和時間以來該表索引的查詢seek次數就達到8萬次(還沒有包括掃描scan的次數的情況下)，那麼這個計算數值還是遠遠大於10萬，這種情況下，這個表的就算已經建立了索引還是會提示繼續建立索引，這種情況下就需要在語句中排除該表的這個索引或直接排除該表





SELECT
    mig.index_handle,
    mid.database_id,
    mid.object_id,
    mid.[statement] [object_fullname],
    (migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX [' AS [create_index_part1],
    'missing_index_' + CONVERT(varchar, mig.index_group_handle) + '_' + CONVERT(varchar, mid.index_handle) + '_' + LEFT(PARSENAME(mid.statement, 1), 32) AS [create_index_name],
    ']' +
    ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') +
    CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL(mid.inequality_columns, '') +
    ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') + '  WITH (' AS [create_index_part2],
    'DATA_COMPRESSION = ROW, AS [create_index_part3],
    migs.group_handle,
    migs.unique_compiles,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    migs.last_user_scan,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    migs.system_seeks,
    migs.system_scans,
    migs.last_system_seek,
    migs.last_system_scan,
    migs.avg_total_system_cost,
    migs.avg_system_impact
INTO #CINDEXES
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
WHERE ((migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)) * (migs.user_seeks + migs.user_scans) > 100000)
--and mid.object_id<>1771153355 排除某張表的時候，加上某張表的id就行
ORDER BY (migs.avg_total_user_cost * migs.avg_user_impact) * (migs.user_seeks + migs.user_scans) DESC

--## CHECK IF RECORDS EXIST ##

IF NOT EXISTS (SELECT TOP 1 1 FROM #CINDEXES)
BEGIN
    DROP TABLE #CINDEXES
    
    RETURN
END

--## CHECK IF INDEX ALREADY EXISTS ##

CREATE TABLE #RINDEXES (
    index_handle INT NOT NULL,
    sql_statement NVARCHAR(MAX) NULL
    )

DECLARE @TSQL NVARCHAR(MAX)

DECLARE RunPerIndex CURSOR FOR
SELECT
    index_handle,
    database_id,
    object_id,
    improvement_measure,
    object_fullname
FROM #CINDEXES

DECLARE @Handle INT,
        @DatabaseID INT,
        @ObjectID INT,
        @Impact INT,
        @ObjectFullName SYSNAME,
        @Rebuild BIT,
        @Filegroup SYSNAME,
        @IndexName SYSNAME

OPEN RunPerIndex
FETCH NEXT FROM RunPerIndex INTO @Handle, @DatabaseID, @ObjectID, @Impact, @ObjectFullName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @TSQL =
'SET @Rebuild = 0
SET @Filegroup = NULL
SET @IndexName = NULL

DECLARE @Missing TABLE (
    key_ordinal INT IDENTITY(1,1) NOT NULL,
    column_id INT NOT NULL,
    column_name    SYSNAME    NULL,
    column_usage NVARCHAR(32) NULL
    )

INSERT INTO @Missing
SELECT *
FROM sys.dm_db_missing_index_columns(' + CONVERT(NVARCHAR(32), @Handle) + ')

DECLARE @Count INT = @@ROWCOUNT

SELECT TOP 1
    @Rebuild = 1,
    @Filegroup = [filegroup],
    @IndexName = [index_name]
FROM
    (
    SELECT
        O.name [object_name], I.index_id, I.name [index_name], F.name [filegroup], COUNT(C.column_id) [index_cols], COUNT(M.column_id) [missing_cols]
    FROM ' + QUOTENAME(DB_NAME(@DatabaseID)) + '.sys.index_columns IC
    INNER JOIN ' + QUOTENAME(DB_NAME(@DatabaseID)) + '.sys.objects O
    ON O.object_id = IC.object_id
    INNER JOIN ' + QUOTENAME(DB_NAME(@DatabaseID)) + '.sys.indexes I
    ON I.index_id = IC.index_id AND
       I.object_id = IC.object_id
    INNER JOIN ' + QUOTENAME(DB_NAME(@DatabaseID)) + '.sys.columns C
    ON C.object_id = IC.object_id AND
       C.column_id = IC.column_id
    INNER JOIN ' + QUOTENAME(DB_NAME(@DatabaseID)) + '.sys.filegroups F
    ON F.data_space_id = I.data_space_id
    LEFT OUTER JOIN @Missing M
    ON M.column_id = IC.column_id AND
       ( M.column_usage = ''INCLUDE'' OR M.key_ordinal = IC.key_ordinal ) AND
       IC.is_included_column =
            CASE M.column_usage
                WHEN ''INCLUDE'' THEN 1
                ELSE 0
            END
    WHERE IC.object_id = ' + CONVERT(NVARCHAR(32), @ObjectID) + '
    GROUP BY O.name, I.index_id, I.name, F.name
    ) T
WHERE
    T.index_cols = T.missing_cols AND
    T.index_cols = @Count'
    
    EXECUTE sys.sp_executesql
        @TSQL,
        N'@Rebuild BIT OUT, @Filegroup SYSNAME OUT, @IndexName SYSNAME OUT',
        @Rebuild = @Rebuild OUT,
        @Filegroup = @Filegroup OUT,
        @IndexName = @IndexName OUT

    IF @Rebuild = 1
    BEGIN
        INSERT INTO #RINDEXES
        VALUES (
            @Handle,
            (
                SELECT TOP 1
                    create_index_part1 + @IndexName + create_index_part2 + 'DROP_EXISTING = ON, ' + create_index_part3 + ' ON ' + QUOTENAME(@Filegroup)
                FROM #CINDEXES
                WHERE index_handle = @Handle
            )
        )                       

        DELETE FROM #CINDEXES
        WHERE index_handle = @Handle
    END

    FETCH NEXT FROM RunPerIndex INTO @Handle, @DatabaseID, @ObjectID, @Impact, @ObjectFullName
END

CLOSE RunPerIndex
DEALLOCATE RunPerIndex

--## SEND EMAIL ##

DECLARE @Recipients VARCHAR(510) = 'dba@net.com;',
        @Subject VARCHAR(255) = UPPER(@@SERVERNAME) + ' Indexes',
        @Body VARCHAR(MAX),
        @RowColor BIT = 0

SET @Body =
'<style type="text/css">body { font-family:"arial"; }</style>
<body>
The following indexes need to be CREATED on ' + UPPER(@@SERVERNAME) + ': <br /><br />
<table border="1" cellspacing="0" cellpadding="10" style="text-align:center">
    <thead>
        <tr style="background-color:#525252; color:White">
            <th>Create Statement</th>
        </tr>
    </thead>
    <tbody>
'

SELECT
    @RowColor =
        CASE @RowColor
            WHEN 0 THEN 1
            ELSE 0
        END,
    @Body +=
        '<tr' + CASE @RowColor WHEN 1 THEN ' style="background-color:#D3D3D3"' ELSE '' END + '>' + CHAR(13) +
        '<td>' + create_index_part1 + create_index_name + create_index_part2 + create_index_part3 + '</td>' + CHAR(13) +
        '</tr>' + CHAR(13)
FROM #CINDEXES
ORDER BY improvement_measure DESC

SET @Body +=
'    <tbody>
</table>
<br />
<br />
The following indexes need to be REBUILT on ' + UPPER(@@SERVERNAME) + ': <br /><br />
<table border="1" cellspacing="0" cellpadding="10" style="text-align:center">
    <thead>
        <tr style="background-color:#525252; color:White">
            <th>Rebuild Statement</th>
        </tr>
    </thead>
    <tbody>
'

SET @RowColor = 0

SELECT
    @RowColor =
        CASE @RowColor
            WHEN 0 THEN 1
            ELSE 0
        END,
    @Body +=
        '<tr' + CASE @RowColor WHEN 1 THEN ' style="background-color:#D3D3D3"' ELSE '' END + '>' + CHAR(13) +
        '<td>' + sql_statement + '</td>' + CHAR(13) +
        '</tr>' + CHAR(13)
FROM #RINDEXES

SET @Body +=
'</table>
</body>'

EXECUTE msdb.dbo.sp_send_dbmail
    @recipients = @Recipients,
    @profile_name = 'WON Notification',
    @body_format = 'html',
    @subject = @Subject,
    @body = @Body;

DROP TABLE #CINDEXES
DROP TABLE #RINDEXES