Tuning Advisor

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. 

So the DPA is not the Advisor is a messenger, hahah

Actually, DPA is providing all the information you would need as a DBA to determine if the MS advise is good or not. We are parsing the query plan and looking at the producing steps to make sure the data fetch is optimized - if not, we surface it in DPA as part of the plan predicate info. The great part of this is that DPA is doing programmatically something which we would never do manually at this scale - we only dive into plans when someone says something is wrong. In this regard, DPA is always watching for ineffiencies at the workload level that can translate into over-usage of resources like memory, CPU, I/O, network ingress/egress. DPA is VERY much so the advisor.

Actually, DPA is providing all the information you would need as a DBA to determine if the MS advise is good or not. We are parsing the query plan and looking at the producing steps to make sure the data fetch is optimized - if not, we surface it in DPA as part of the plan predicate info. The great part of this is that DPA is doing programmatically something which we would never do manually at this scale - we only dive into plans when someone says something is wrong. In this regard, DPA is always watching for ineffiencies at the workload level that can translate into over-usage of resources like memory, CPU, I/O, network ingress/egress. DPA is VERY much so the advisor.