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.
By cfajuke 0 asked Oct 30 at 07:32 PM
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.
By SDyckes2 81 answered Nov 03 at 03:39 PM