Tuning Advisor

I'm new to SolarWind and I have a question in Tuning Advisor. Does this mean it advise me to create another non-cluster index ? The table already contain cluster index on columns OrderNo and TestCode. And non-cluster index on columns ReceivedDate, HN and OrderDate. When I press Show index DDL. This query message display 

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname, >] ON [DB02].[lab].[Order] ([TestCode],[ReceiveDate]) INCLUDE ([OrderNo],[OrderDate],[HN],[SambleCode])

Thank in advance.

 

  • Hi Nutchi, not sure I have enough information, but there are a couple of things going on here. The first is just an index recommendation coming from Microsoft SQL Server, not DPA. I will leave it up to you whether that index is a good recommendation or not - we just want to make you aware in DPA that the index recommendation exists. Keep in mind that SQL Server will give the index recommendation based on the order of columns in the table (not necessarily the optimized leading column).

    The second part is the part where DPA is parsing the plan XML looking for producing steps that are inefficient. You can see that step 22 is doing a clustered index scan which is going after every row in that PK index. The probe function may be causing an implicit conversion on TestCode which even if you created the index as suggested, the optimizer may not be able to use it. My recommendation is to test in a dev environment and watch with DPA to see how the workload looks after each distinct modification. This is assuming you own the source code here - if not, please work with the 3rd party vendor of the software and then charge them consulting fees. Smirk

  • Hi mandevil. Thank you for your clearly explanation. I used to be python developer, just transition to junior DBA. Thx.