What are these warning errors telling me and how do I resolve the issue

cfajuke 2017-10-30 19:32:24

Im getting an excessive grant warning on the SELECT portion of my plan diagram and a residual I/O warning on Idex Seek [Output].[IX_Output_Label]. I want to know what these warning errors telling me and how do I resolve these issues.

Thank you.

SDyckes2 2017-11-03 15:39:06
These 2 warnings are related. The residual I/O warning is alerting you to a hidden filter when doing the index seek on Output.IX_Output_Label. The index may have the columns you need to retrieve the data requested, but a different ordered index may be more efficient.

The Actual Rows Read: 4,211 versus the Actual Rows: 9 indicates that a hidden filter is being used to reduce the 4211 to 9 returned rows. Due to the Optimizer Estimates, a larger than needed amount of memory was requested when the plan was compiled, which results in the Excessive Grant: warning.

These are valuable informational warnings to potentially help you better tune the query and/or indexes. Rob Farley discusses this specific case in his blog, Number of Rows Read / Actual Rows Read warnings in Plan Explorer, where he explains it in detail.