• State Not Answered
  • Locked Locked
  • Replies 1 reply
  • Subscribers 26 subscribers
  • Views 847 views
  • Users 0 members are here
Options
Related
This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Recommended index even though that index already exists

vlau

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?

Parents
  • 0

    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.

Reply
  • 0

    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.

Children
No Data

SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.

SolarWinds Customer Success Center Certification SolarWinds Lab Link Accounts
About THWACK Blogs Federal & Government Edit Settings Free Tools & Trials
Legal Documents Terms of Use Privacy California Privacy Rights Security Information
©2021 SolarWinds Worldwide, LLC. All Rights Reserved.