and new column is not null column.

Can we use MERGE Statement for data archiving?

No, query takes <1 sec from SSMS

yes Grant, it is executing in 1 sec after forcing index seek

As temporary fix,  forcing Index seek hint. Now Optimizer is using Index seek and no sort

• This reply was modified 4 years, 12 months ago by  IMary.

I've uploaded the screen shots, for query 11 in the sqlplan, I'm not seeing sort in Test.

also, Index scan is happening in Prod vs Index seek in Test

I've uploaded the screen shots, for query 11 in the sqlplan, I'm not seeing sort in Test.

thanks Grant.

I just took fresh backup of PROD database and restored in Test server and executed the same query and SORT is happening prod but not in Test. It executes...

• This reply was modified 4 years, 12 months ago by  IMary.

Thanks so much,

I have attached anonymized sql plans using SentryOne's Plan Explorer

This website is blocking sqlplans, so I've uploaded XML version of it. Please advise

• This reply was modified 4 years, 12 months ago by  IMary.
• This reply was modified 4 years, 12 months ago by  IMary.

In prod, Sort Operator,  I'm seeing below warning:

Operator used tempdb to spill data during execution with spill level 1 and 1 spilled thread(s), Sort wrote 51440 pages to and read...

No information in the logs other than memory dump.

We had failover as Manual. But if the primary is not healthy, will it not failover to secondary at all with Manual...

• This reply was modified 5 years ago by  IMary.