Recommended index even though that index already exists

On the DPA, I am seeing a recommended index for a column on one of the DB tables due to "Inefficient table/index access steps discovered by DPA" from a cluster index scan with an estimated number of rows of 6M. There are also the warnings "Parallelism warning" and "Predicate - no index". The predicate here is the same column as for the recommended index.

However, according to the Current Table Information, I can see there is already an nonclustered index on that column. The fragmentation is 0% and the stats were generated 12 hours ago.

Does anyone have any advice on what can be done to improve performance?

  • Sorry for the delay in getting back to you, but this is a known issue and is being worked on for a future version of DPA. If the plan suggests a clustered index scan, you will need to understand why. Maybe the where clause is not very selective so SQL Server does a full scan. Within the plan if you review Estimated Rows (how many rows are returned after filtering) and Estimated Rows Read (how many rows were read in total). If this is higher than 10-15% a full scan is often the best way to run the query.