Slow performance

SRV TI 2017-09-21 19:26:08

SDyckes2 2017-09-21 23:16:19
First, I would recommend capturing the Actual plan instead of the Estimated plan with Plan Explorer (PE). The actual plan will provide additional details that can be utilized to offer more detailed tuning advice.

I will pick off the low hanging fruit here. First, SQL Server has alerted you to a potential missing index (Note: this is not a recommendation from the SentryOne Plan Explorer, rather it is from SQL Server.). This is seen by the yellow exclamation mark in the SELECT box as seen below. If you hover over the SELECT box, the dialogue box shown will display with the Warnings providing you the reason for the exclamation.

alt text

If you right-click on the SELECT box, you can choose the Missing Index Details.
alt text

Choosing the Missing Index Details will then pop up the dialogue box providing you with the potential missing index. Before applying this index, please check to see what current indexes you have on the table, you may be able to update an existing index to attain the same result. Always test your changes before applying to your production environment, just because SQL Server recommended an index does not mean it will improve the performance!

When you generate the plan from PE with the Get Estimated Plan or the Get Actual Plan boxes at the top, on the toolbar, you will have access to the Indes Analysis tab at the bottom of the screen. This tool in PE will allow you to test how a modified version of an existing index will score based on a hit percentage. You may also create a new virtual index to test with the scoring system.

The potential missing index would reduce the cost associated with the Index Scan on IX_CTL_BOLETIM_COLHEITA_CD_USO_SOLO_CD_OPERACAO, shown below.
alt text

Another potential performance gain would be to address the Key Lookup. Again, this can quickly be tuned with the Indes Analysis tool if available.

alt text

The Index Seek on TRA_DETALHE_CEM.IX_TRA_DETALHE_CEM_6 does not provide all the information the query requires to fulfill its request. SQL must make a second call to the TRA_DETALHE_CEM table, this time to the Primary Key, TRA_DETALHE_CEM_PK to attain the requested information. Updating the IX_TRA_DETALHE_CEM_6 index to include the missing data would potentially remove the Key Lookup and only perform an Index Seek one time to gather and return all the requested information. This missing information is listing in the Output List: of the hover over of the Key Lookup:
alt text

In this case, the VLR_VOLUME_IMPLEMENTO column looks like it can be added as an INCLUDE column to the existing IX_TRA_DETALHE_CEM_6 index to potentially resolve the Key Lookup.

Finally, you may want to check the compiled parameters to the runtime parameters. With the plan provided, we can see the compiled parameters of the estimated plan on the Parameters tab on the bottom of the tool.
alt text

If you utilize the Get Actual Plan feature of the tool, it will provide both the compiled value and the runtime value, allowing you to look for parameter sniffing issues.

These are just recommendations based on the estimated plan, no guarantees they will make a difference. The actual plan provides many additional details needed to be more specific on performance improvements.