DDL attached, we are not able to provide sample data, per my boss's instructions:

"Everything but sample data. Column definition should allow them to generate what they need"

thanks Jeff

note that the table I deleted from is not referenced in the query

thats all the code for the misbehaving query, moot point anyway as no code is changing until we figure out what went wrong that day

All:

This was literally a "switch-flip" -- the query run time doubled exactly right after I made the changes noted in my original post

(note: on that day I rebuilt indexes...

Any luck with the plan?

On a related note, I am trying to pass as a query hint the XML from what I believe to be the plan that the query...

stepping out, will be back online Monday, thank you for your help today

my apologies for the runaround on the query plan -- I thought sys.dm_exec_text_query_plan yielded an actual, apparently that's...

okay, that's not what I've heard elsewhere (re: column stats) but so be it

try this plan -- I generated it via "Include Actual Execution Plan" (vs sys.dm_exec_text_query_plan, from which I...

the stats which were rebuilt were COLUMN statistics

I thought table index rebuild only updated TABLE statistics (fullscan)

Are you trying to tell me that this is not the case, i.e. table...

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...

"One other thing would really help, the actual execution plan."

This IS the ACTUAL execution plan

"Also, do you really drop the temporary table as soon as it is populated as it...

I have attached the results of sp_help's on the tables, and the sql statement

I believe it is multi-line

Note that the query did not change -- the emphasis is on what could have happened that weekend that caused the query to double in time

that IS the actual plan:

select

(SELECT SUBSTRING(text, r.statement_start_offset/2,(CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2 ) FROM sys.dm_exec_sql_text(r.sql_handle) ) AS query_text...

I dont think I rebuilt stats that day,just the indexes.

I added a update stats task to the maint plan about a week later (after the index rebuild), however it updates...

note: the emphasis is on what happened when the aforementioned operations were performed on database "P" such that the query doubled in run time, not so much on what...