A new development:

As previously stated, the "trans" and "asset" tables are subscribed tables

I re-initialized the subscription this morning

Now the query appears to be back to normal run time

(no changes in table definition [indexes, etc.] compared to before the purge/etc)

My understanding is that a re-initialization does the following:

- Drops and re-creates the table on the subscriber side

- Bulk inserts into the subscribed table

- Re-creates indexes, constraints, etc.

So what is the difference between doing all of the above, versus just running a maintenance plan to rebuild all the table's indexes?

I am suspending the weekly index rebuild/update stats for now -- I don’t want the table to get fragmented again, but I also don’t want this performance degradation to resurface. Also, a re-initialization of this subscription would be too time-consuming and disruptive to do on a regular basis.

Thoughts?