Database Tuning Advisor recommends to create an ex

问题:

When I run SQL Server 2005 Database Tuning Advisor, it gives a recommendation to create an index, but it will recommends to index a column which already has an index on it. Why does it give a recommendation to create the same index again?

Here is my SQL:

SELECT t.name AS 'affected_table'
  , 'Create NonClustered Index IX_' + t.name + '_' 
   + CAST(ddmid.index_handle AS VARCHAR(10))
   + ' On ' + ddmid.STATEMENT 
   + ' (' + IsNull(ddmid.equality_columns,'') 
   + CASE 
     WHEN ddmid.equality_columns IS NOT NULL 
          AND ddmid.inequality_columns IS NOT NULL
     THEN ',' 
     ELSE '' 
     END 
   + ISNULL(ddmid.inequality_columns, '')
   + ')' 
   + ISNULL(' Include (' + ddmid.included_columns + ');', ';') 
  AS sql_statement
  , ddmigs.user_seeks
  , ddmigs.user_scans
  , CAST((ddmigs.user_seeks + ddmigs.user_scans) 
    * ddmigs.avg_user_impact AS INT) AS 'est_impact'
  , ddmigs.last_user_seek
FROM 
  sys.dm_db_missing_index_groups AS ddmig
  INNER JOIN sys.dm_db_missing_index_group_stats AS ddmigs
    ON ddmigs.group_handle = ddmig.index_group_handle
  INNER JOIN sys.dm_db_missing_index_details AS ddmid 
    ON ddmig.index_handle = ddmid.index_handle
  INNER Join sys.tables AS t
    ON ddmid.OBJECT_ID = t.OBJECT_ID
WHERE 
  ddmid.database_id = DB_ID()
  AND CAST((ddmigs.user_seeks + ddmigs.user_scans) 
  * ddmigs.avg_user_impact AS INT) > 100
ORDER BY 
  CAST((ddmigs.user_seeks + ddmigs.user_scans) 
  * ddmigs.avg_user_impact AS INT) DESC;

回答1:

Perhaps try "DESC" to order a different way?

This worked in another similar SO question... Why does SQL Server 2005 Dynamic Management View report a missing index when it is not?

回答2:

You may need to run your queries and suggest the index that is already there.

SELECT * FROM table WITH INDEX(IX_INDEX_SHOULD_BE_USED) WHERE x = y

The index that is there might not be thought of as useful from SQL Server. Run the query that is suggesting the need for the index and see the execution path in SQL Server and then build other indexes that are needed.

回答3:

Can u please list the full index missing warning message? generally, it's asking to create an index on the table BUT only to return certain fields, instead of an index on the table, which will return all fields by default.

回答4:

Go ahead and script out the details of both your current index strucutre and then compare this to reccomendations made by the DTA.

I suspect that you will find there are structural differences in the results.