Need Help with this plan ( Query is running more than 1 Hour)

SQLFanatic 2014-07-29 04:34:20

SomewhereSomehow 2014-07-29 17:58:24
Could you post an Actual Plan and, maybe, some details, if it is anonymized, please?
Kevin 2014-07-29 18:59:36
As SomewhereSomehow mentioned earlier, it's hard to give you a lot of advice with an anonymized estimated plan. However, a couple things of note pop out.

First, you have a couple clustered index scans. That means every row in the table will be scanned. That could be a LOT of data. Can you add additional WHERE clause or JOIN clause filters to transform the scan into a seek?

Second, you have some HUGE data reads on the lower right branch of the query plan, meaning 10s of millions of records, all acquired through index scans. It looks like your seek predicates are being thwarted. Both of those indexes are composite indexes made up of multiple columns. You might wish to create individual non-clustered indexes on each column or more closely evaluate the scan criteria to see what better kind of index will help speed this query.

Hope that helps,

-Kevin

SQLFanatic 2014-07-30 16:25:30
Hi Kevin- Thanks for your response. We cannot change the query to add additional WHERE clauses. But I will try creating additional NCIs as you suggested. I willupdate the post with results.