Gail, Thank you so much for your quick reply. I was hoping to hear from you on this one.

In the past we went down the stats path, and now we are updating stats on the 2 larger tables in the query every 6 hours. That seemingly worked for a while, but seems to have fallen off.

Auto_update stats is on, and as you can see from the row counts in the tables involved in the query, the tables are not terrible large either, though some are very wide.

ROW_COUNTS

1,314,740

1,019,205

457,865

315,543

315,383

107,365

95

0

0

0

0

0

0

In our bi-weekly Index rebuild job, I am immediately running sp_updatestats following the Index rebuild. I figured it was more efficiant this way.

After reading your reply to this post, I did a manual update statistics to each table in the query...execution time actually increased (granted a shared test server with shared disk).

After these results, I ran a full sp_updatestats against the database. That ran for 5 + minutes, and now the query is returning results in 2 seconds.