Reports running slow

SQLDB 2015-03-26 13:11:41

link textWe have multiple reports using the attached plan( query) as a filter to view the records. All the reports which have this part of filter are reported running slow and

What can i do in this part of query to improve overall peformance of the reports.

Thanks!

@SQLkiwi, attached as mentioned in comment.

link text

@Aaron attached is the actual execution plan as well, thank you.

Aaron Bertrand 2015-03-26 13:20:45
The only expensive thing in this entire plan is the index seek, which yields 14 million rows. Yet only 421 are ultimately returned. Is there any way to filter these rows down more directly in the original table by adding additional columns to the predicate? Impossible for me to suggest how, both because you've anonymized the plan (obliterating the statement) and because this looks like an estimated plan (or a plan generated by SSMS or pulled from cache). If you can provide a non-anonymized, post-execution, actual plan generated from within Plan Explorer, it will provide a lot more details that will allow for more specific observations and recommendations.
SQLkiwi 2015-03-26 13:43:27
The problematic part of the query is:

Top subtree

In particular, the 14,466,991 rows coming from the Table2.Index 4 Seek are filtered down (as Aaron mentions in his comment to the question) to just 421 rows by a residual predicate on the Nested Loops Join immediately above it:

Join predicate

There's no way to know from an anonymized plan what that ScalarString28 predicate is, but you should look into it. It may be that you could adjust the indexing on Table2 to allow/encourage the optimizer to push that predicate further down.

It is also possible that the entire subtree shown above is built on a faulty assumption. It is relatively common for subtrees below a Top operator (as shown above) to cause problems when the distribution of data is unfavourable. The optimizer makes special adjustments below operations like Top, to optimize for finding a certain number of rows quickly.

You might like to try executing the query with the documented and supported hint OPTION (QUERYTRACEON 4138) to disable these 'row goal' adjustments to see if it results in a better-performing plan. See https://support.microsoft.com/en-us/kb/2667211 for more information. Note this trace flag is really intended to help diagnose the reasons for poor plan selection rather than providing a 'fix'.

At which point, I notice you are using SQL Server 2005, which means that hint and trace flag will not be effective for you, sorry. I'll leave the advice there for other readers. There's no simple way I can think of right now to turn off the 'row goal' behaviour for you, so concentrate on the first issue I raised instead.

Update based on actual plan

The second uploaded plan has a different shape from the first one, meaning a recompilation has occurred:

Actual Plan

The estimate on Table1 makes me think that table might be a table variable. If so, consider using a #temporary table instead. These typically provide better cardinality information to the optimizer than table variables, and can also support automatically-created statistics. Once you have done that, it is quite likely the rest of the plan shape will change again, based on the better information provided to the optimizer.

You should still review your indexing on Table 5 to include Column14 so a Key Lookup is avoided.

SQLDB 2015-03-26 14:17:51
Thanks @SQLkiwi, I am sorry if I get wrong , because little confused with the execution plan attached after "Update based on actual plan", I do not see such plan as attached on my question. Sorry if I am seeing something incorrect here, please help
SQLkiwi 2015-03-26 14:35:31
@SQLDB There are two statements in the second plan you uploaded. The one shown above is Statement 2.
SQLDB 2015-03-30 11:04:41
Thanks @SQLkiwi. Table 1 is using temporary table as checked from the plan and table 5 cannot include column 14 because I believe index 7 being a clustered index. I have attached the un anonymize plan. Please suggest what can be done, thank you.
SQLkiwi 2015-03-31 13:16:47
@SQLDB Replace the table variable with a #temporary table as I mentioned in the answer. As far as the Key Lookup is concerned, yes of course the lookup occurs on the clustered index – that's where the non-index columns are located 🙂 Removing the lookup means adding columns to the preceding nonclustered index. One option in this specific case is to add the column wooProfitCenter_bcoIdFk to the preceding nonclustered index IS_wooSite_SitDfk.