MISSIng index sql server

-- Missing Index Script
-- Original Author: Pinal Dave 
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
GOIn SQL Server, you can use the DMV (Dynamic Management View) called sys.dm_db_missing_index_details to find information about missing indexes.
This view provides details about the queries that could benefit from certain indexes.
Here's a sample query that you can use to identify missing indexes:
SELECT
    dmid.index_handle,
    dmid.statement,
    dmid.equality_columns,
    dmid.inequality_columns,
    dmid.included_columns,
    dmigs.unique_compiles,
    dmigs.user_seeks,
    dmigs.avg_user_impact,
    dmigs.last_user_seek
FROM 
    sys.dm_db_missing_index_details AS dmid
INNER JOIN 
    sys.dm_db_missing_index_groups AS dmig ON dmid.index_handle = dmig.index_handle
INNER JOIN 
    sys.dm_db_missing_index_group_stats AS dmigs ON dmig.index_group_handle = dmigs.group_handle
ORDER BY 
    dmigs.avg_user_impact DESC;

This query retrieves information about missing indexes, including the statement that could benefit from an index, 
the columns involved (equality_columns, inequality_columns), included columns, and some statistics like user seeks and average user impact.

Please note that the sys.dm_db_missing_index_details DMV provides information about potential missing indexes based on historical data. 
It's crucial to carefully evaluate and consider the impact before creating any new indexes, as adding indexes comes with trade-offs like 
increased storage and maintenance overhead.

Additionally, tools like SQL Server Management Studio (SSMS) provide a Database Engine Tuning Advisor (DTA) that can analyze queries
and suggest missing indexes based on actual query execution plans.

Always test any changes on a non-production system before implementing them in a production environment.
SQL相关代码片段
