SQL Script liệt kê các Missing Index

Missing index tức là các index không tồn tại trong SQL Server, nhưng có những câu truy vấn nào đó cần những index này, dba phải biết cách lấy thông tin về missing index.

SQL Server cung cấp 2 công cụ để ta tìm ra Missing Index

1. Sử dụng Database Engine Tuning Advisor (DTA): để sử dụng công cụ này trước tiên ta phải dùng SQL Profiler tạo ra 1 file workload capture tải công việc hàng ngày của một SQL Server Instance, dựa vào các Execution plan được lưu trong file workload này, Database Engine Tuning Advisor sẽ cho ta những gợi ý về missing index
• Chất lượng của các Missing Index được nhận dạng phụ thuộc vào chất lượng của file workload, nếu file workload thiếu quá nhiều câu truy vấn, dĩ nhiên sẽ ảnh hưởng đến chất lượng của các Missing Index.
• SQL Server Profiler đã tạo sẵn cho chúng ta một Template đó là Tuning template, template này đã được thiết kế để capture những event cho một workload được sử dụng bởi DTA.
2. Sử dụng các DMV

Một lưu ý rất quan trọng là Missing Index sẽ làm chỉ số CPU và I/O của SQL Server lên cao.

Sau đây là đoạn script tìm các missing index dùng DMV:

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

Tham khảo: http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

Tìm những câu truy vấn bị thiếu index:

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan'
AS sp)
 
SELECT DB_NAME(CAST(pa.value AS INT)) QueryDatabase
    ,s.sql_handle
    ,OBJECT_SCHEMA_NAME(st.objectid, CAST(pa.value AS INT)) AS ObjectSchemaName
    ,OBJECT_NAME(st.objectid, CAST(pa.value AS INT)) AS ObjectName
    ,SUBSTRING(st.text,s.statement_start_offset/2+1,
            ((CASE WHEN s.statement_end_offset = -1 THEN DATALENGTH(st.text)
                ELSE s.statement_end_offset END) - s.statement_start_offset)/2 + 1)  AS SqlText
    ,s.total_elapsed_time
    ,s.last_execution_time
    ,s.execution_count
    ,s.total_logical_writes
    ,s.total_logical_reads
    ,s.min_elapsed_time
    ,s.max_elapsed_time
    -- query_hash is useful for grouping similar queries with different parameters
    --,s.query_hash
    --,cast (p.query_plan as varchar(max)) query_plan
    ,p.query_plan
    ,mi.MissingIndex.value(N'(./@Database)[1]', 'NVARCHAR(256)') AS TableDatabase
    ,mi.MissingIndex.value(N'(./@Table)[1]', 'NVARCHAR(256)') AS TableName
    ,mi.MissingIndex.value(N'(./@Schema)[1]', 'NVARCHAR(256)') AS TableSchema
    ,mi.MissingIndex.value(N'(../@Impact)[1]', 'DECIMAL(6,4)') AS ProjectedImpact
    ,ic.IndexColumns
    ,inc.IncludedColumns
FROM (  -- Uncomment the TOP & ORDER BY clauses to restrict the data and
        -- reduce the query run time.
        SELECT --TOP 200
        s.sql_handle
        ,s.plan_handle
        ,s.total_elapsed_time
        ,s.last_execution_time
        ,s.execution_count
        ,s.total_logical_writes
        ,s.total_logical_reads
        ,s.min_elapsed_time
        ,s.max_elapsed_time
        ,s.statement_start_offset
        ,s.statement_end_offset
        --,s.query_hash
    FROM sys.dm_exec_query_stats s
    -- ORDER BY s.total_elapsed_time DESC
    ) AS s
CROSS APPLY sys.dm_exec_text_query_plan(s.plan_handle,statement_start_offset,statement_end_offset) AS pp
CROSS APPLY (SELECT CAST(pp.query_plan AS XML) AS query_plan ) AS p
CROSS APPLY p.query_plan.nodes('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex')
                AS mi (MissingIndex) 
CROSS APPLY (SELECT STUFF((SELECT ', ' + ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)')
            FROM mi.MissingIndex.nodes('./sp:ColumnGroup')
                AS t1 (ColumnGroup)
            CROSS APPLY t1.ColumnGroup.nodes('./sp:Column') AS t2 (ColumnGroupColumn)
            WHERE t1.ColumnGroup.value('./@Usage', 'NVARCHAR(256)') <> 'INCLUDE'
            FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'), 1, 2, '') AS IndexColumns ) AS ic 
CROSS APPLY (SELECT STUFF((SELECT ', ' + ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)')
            FROM mi.MissingIndex.nodes('./sp:ColumnGroup')
                AS t1 (ColumnGroup)
            CROSS APPLY t1.ColumnGroup.nodes('./sp:Column') AS t2 (ColumnGroupColumn)
            WHERE t1.ColumnGroup.value('./@Usage', 'NVARCHAR(256)') = 'INCLUDE'
            FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'), 1, 2, '') AS IncludedColumns ) AS inc 
CROSS APPLY sys.dm_exec_plan_attributes(s.plan_handle) pa
CROSS APPLY sys.dm_exec_sql_text (s.sql_handle) st
WHERE pp.query_plan LIKE '%MissingIndexes%'
  AND pa.attribute = 'dbid'

Tham khảo: http://sqlstudies.com/2013/11/11/a-better-way-to-find-missing-indexes/