Any suggestions on making this query run faster.

Garry B 2015-09-10 15:53:15

This runs in 2 1/2 minutes and is calling a view and joining to other tables. Need to get quicker if possible. I tried adding a non clustered index to the table included in the clustered index scan. Object4.Index1 but that did not help.

SQLkiwi 2015-09-12 03:03:44
alt text

When creating the nonclustered index, you need to be sure it includes Column13 in the Output List, and also any columns referenced in ScalarString20. Ideally, the index would also be seekable, and provide a useful order for the following grouping operation, but it's impossible to be more specific about the details from an anonymized plan.

This table has 23,368,800 rows – filtered down to 478,708 by the ScalarString20 predicate, so a suitable nonclustered index should help. You should also look to remove the Key Lookup (again, be sure to check the predicate columns as well as the output list). Running the query directly from Plan Explorer rather than importing from SSMS would collect other useful performance information as well. There are many other issues with this plan, but that should get you started.