Sql-server – Best way to merge tightly coupled SQL Server Instances

A simpler way to write the MERGE is as follows:

MERGE #target /* WITH (SERIALIZABLE) */ AS T
USING #source AS S
    ON S.Id = T.Id
WHEN MATCHED
    AND T.IsDeleted = 0
    AND S.Modified > T.Modified
    THEN UPDATE SET
        Modified = S.Modified,
        NoteText = S.NoteText
WHEN NOT MATCHED THEN
    INSERT (Id, Modified, IsDeleted, NoteText)
    VALUES (S.Id, S.Modified, 0, S.NoteText);

The SERIALIZABLE hint would be necessary to avoid a potential race condition. This consideration obviously does not apply to a local temporary table target, but it would be remiss of me not to mention.

In broad terms, it is almost always wrong to perform any sort of filtering in the ON clause (or equivalently, in a CTE). The ON clause should only be used to specify how rows between the source and target are related.

See Inserting, Updating, and Deleting Data by Using Merge for more details.

The source and target tables should also have a unique index or key. Moreover, for best performance SQL Server should be able to guarantee that the MERGE statement will affect each target row a maximum of once. Without that, the execution plan will have to detect multiple DML operations on the same row in order to raise a runtime error if it happens. This often requires a Sort, and always requires a Segment, Sequence Project, and Assert. None of these will make your command execute any faster :)

Well here is a small checklist. Due to the fact that I'm not on your machine I cannot take a look inside and give you some suggestions. But I'm pretty sure, that you'll find the root cause with the provided statements.

Wrong memory configuration

As you mentioned, you upgraded your SQL Server and run different instances on the same physical machine. One of the things which may be harmful is a restart or anything like that (e.g. Failover). Maybe your instance has reset their memory limits. This may explain your drastically slowdown. You can check if they still up with your documented max memories. You can get them running the following the command below:

EXEC sp_configure N'max server memory (MB)'

If you just want to see this as a quickshot. You can try to query the dm_os_sys_memory which has a nice column system_memory_state_desc which isn't very detailed but it says you the needed things too. It will display if your memory is low, which means there is a constant memory pressure on the system.

SELECT system_memory_state_desc
FROM sys.dm_os_sys_memory

Statistics are out of date

You might want to update your statistics for your database tables, as they may be too old.

EXEC sp_updatestats

In a situation like yours, it is wise to throw away your cache plan, as it might be useless due to the old statistics. If sp_updatestats shows many tables and many statistics which are updated, it may be a good idea to throw it away and let the SQL Server recreate it.

You can achieve this using this DBCC command:

DBCC FREEPROCCACHE

Fragmented indices

It might be, that your indices are too fragmented which cause a huge load on your I/O subsystem. Before you blindly execute the statement, you should check if your machine has a higher load on your I/O subsystem. This may be easily done by using perfmon for example. The READ UNCOMMITTED should be used as some indices may be written at the moment. You can try to run the following statement.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

-- Get Index Fragmentation
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName
    , ind.name AS IndexName, indexstats.index_type_desc AS IndexType
    , indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
        ON ind.object_id = indexstats.object_id 
        AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 25 
ORDER BY indexstats.avg_fragmentation_in_percent DESC
GO

Beware, it might run very long and on your system which is already under pressure it might kill it. Only run it, if your system may to be affected by it.

Each row which will be returned is an index which is fragmented and may need an REBUILD or REORGANIZE. This may improve your overall system performance too, but during the rebuild itself, it will slow down!

ARITHABORT problem

It may be an problem with ARITHABORT which tends to make problems on older databases (for some users). Maybe you try to enable/disable it.

Many other causes...

As mentioned in the first sentences, it's pretty hard to see where your problem is. It may be another error - maybe even a hardware fault. I've had a error on a switch to my SAN which killed my performance too for some years (before it was redundant).

But if nothing of the above tends to help, you can still provide the output of your dm_os_wait_stats which may help me to get the right view on your system.

SELECT TOP 30 *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

This will return the biggest waitcounter and may help to find a bottleneck or error. You can do this using the following query:

-- Show all active processes with detailed informations
SELECT pr.spid, pr.loginame, pr.status, pr.blocked, pr.waittime, pr.lastwaittype,pr.cpu, pr.physical_io, pr.memusage, pr.last_batch, db.name AS databaseName, obj.name AS objectName, txt.text AS currentQuery, pr.request_id
FROM sys.sysprocesses as pr
INNER JOIN sys.databases as db
        ON pr.dbid = db.database_id
OUTER APPLY sys.dm_exec_sql_text(pr.sql_handle) as txt
LEFT JOIN sys.all_objects as obj
        ON txt.objectid = obj.object_id
WHERE pr.status <> 'sleeping'
    and pr.spid >= 50

Additionally it can be a process which produces locks or consume more than it should. But I think this isn't the best possibility. But anyway, you can take a look in your running processes. Maybe you see something which isn't correct.