SQL Server Missing Index Script

Aşağıda hazırladığım script ile tablolarınız üzeirnde ihtiyaç duyulan Index leri bulabilirsiniz.

NOT :  Indexleri script ile bulduğunuz gibi sisteme CREATE etmeyiniz !!! İlk etapta tabloyu izlemeye alınız ve daha sonra Index i oluşturmaya karar verdikten sonraki süreçte bir müddet oluşturduğunuz Index ‘i monitor ediniz. Eğer sağlıklı ise yaşamasına izin verin değil ise Index i kaldırınız.

SELECT  user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [Index Advantage] ,
        migs.last_user_seek AS [Last User Seek],
        mid.[statement] AS [Database.Schema.Table] ,
        mid.equality_columns AS [Equality Columns] ,
        mid.inequality_columns AS [Inequality Columns] ,
        mid.included_columns AS [Included Columns] ,
        migs.unique_compiles AS [Unique Compiles],
        migs.user_seeks AS [User Seeks] ,
        migs.avg_total_user_cost AS [AVG Total User Cost] ,
        migs.avg_user_impact AS [AVG User Impact],
		 N'CREATE NONCLUSTERED INDEX [SQLTRINX_' + SUBSTRING(mid.statement,
                                                      CHARINDEX('.',
                                                              mid.statement,
                                                              CHARINDEX('.',
                                                              mid.statement)
                                                              + 1) + 2,
                                                      LEN(mid.statement) - 3
                                                      - CHARINDEX('.',
                                                              mid.statement,
                                                              CHARINDEX('.',
                                                              mid.statement)
                                                              + 1) + 1) + '_'
        + REPLACE(REPLACE(REPLACE(CASE WHEN mid.equality_columns IS NOT NULL
                                            AND mid.inequality_columns IS NOT NULL
                                            AND mid.included_columns IS NOT NULL
                                       THEN mid.equality_columns + '_'
                                            + mid.inequality_columns
                                            + '_Includes'
                                       WHEN mid.equality_columns IS NOT NULL
                                            AND mid.inequality_columns IS NOT NULL
                                            AND mid.included_columns IS NULL
                                       THEN mid.equality_columns + '_'
                                            + mid.inequality_columns
                                       WHEN mid.equality_columns IS NOT NULL
                                            AND mid.inequality_columns IS NULL
                                            AND mid.included_columns IS NOT NULL
                                       THEN mid.equality_columns + '_Includes'
                                       WHEN mid.equality_columns IS NOT NULL
                                            AND mid.inequality_columns IS NULL
                                            AND mid.included_columns IS NULL
                                       THEN mid.equality_columns
                                       WHEN mid.equality_columns IS NULL
                                            AND mid.inequality_columns IS NOT NULL
                                            AND mid.included_columns IS NOT NULL
                                       THEN mid.inequality_columns
                                            + '_Includes'
                                       WHEN mid.equality_columns IS NULL
                                            AND mid.inequality_columns IS NOT NULL
                                            AND mid.included_columns IS NULL
                                       THEN mid.inequality_columns
                                  END, ', ', '_'), ']', ''), '[', '') + '] '
        + N'ON ' + mid.[statement] + N' (' + ISNULL(mid.equality_columns, N'')
        + CASE WHEN mid.equality_columns IS NULL
               THEN ISNULL(mid.inequality_columns, N'')
               ELSE ISNULL(', ' + mid.inequality_columns, N'')
          END + N') ' + ISNULL(N'INCLUDE (' + mid.included_columns + N');',
                               ';') AS CreateStatement
FROM    sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )
        INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) ON migs.group_handle = mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) ON mig.index_handle = mid.index_handle
WHERE   mid.database_id = DB_ID()
ORDER BY [Index Advantage] DESC;

 

Kolay Gelsin.

Leave a Reply

Your email address will not be published. Required fields are marked *