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

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.

Plan.pesession (304.2 kB)
avatar image By cfajuke 0 asked Oct 30 at 07:32 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By SDyckes2 81 answered Nov 03 at 03:39 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x1
x1

asked: Oct 30 at 07:32 PM

Seen: 22 times

Last Updated: Nov 03 at 03:39 PM