You don't need multiple counts.

DROP TABLE IF EXISTS #actions;
CREATE TABLE #actions ( action nvarchar(10) NULL )

MERGE
...
OUTPUT $ACTION into #actions
...

DECLARE @insert_count int
DECLARE @update_count int

SELECT @insert_count = SUM(CASE WHEN...

Keys locks are normal with a clustered index (in fact, they must be key locks, since you can't get rid locks on a ci).  But I wouldn't expect that many...

I think something like this is what you need:

select @v1 = max(case when type = 1 then amt end),
@v2 = max(case when type = 2 then amt...

No, doesn't sound normal.  How did you determine the locking that was occurring?  Does the table have a lot of partitions?

No, that is purely the physical name.

On a cluster, SERVERPROPERTY('MachineName') shows the instance name.

For a non-clustered named instance, if that doesn't show the instance name, then use:

SERVERPROPERTY('InstanceName')

Isn't the physical machine name different?

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

Is the report not able to do a LEFT JOIN instead of an INNER JOIN?  That would be the standard way to do that, without having to create a dummy...

You might want to consider assigning an alias to the modified value, so that if it ever changes later the definition of it is only in 1 place:

            
October 21, 2019 at 8:01 pm
            
#3690734

            
            
                                                
        

Maybe this will give you what you want:

--** Data set up ***************************************************************
CREATE TABLE dbo.questions (
    question_id int PRIMARY KEY,
    question varchar(1000)...

SSIS is a good (enough) tool, for what it does.  It is actually very good at moving data around, to/from SQL Server and other platforms as well.

SSIS also has another...

case

when columnA <> 0

then columnB = 1

else columnB

end

The main thing to understand about CASE is that every result from a CASE must be a single value.  The expression leading to...

Certain errors by default won't fail the entire transaction/batch.  Before the transaction, use:

SET XACT_ABORT ON

to make SQL fail the entire transaction if an error like that occurs.

 

I think we may have interpreted the q differently.  I took "statistics i/o" to mean the results from:

SET STATISTICS IO ON;

The logical i/o results are directly comparable, aren't they?

I think maybe...

Really it's best to use them in combination.

High I/O numbers tell you to look for better ways to do related part(s) of a query.  The execution plan shows you what...

Something along these lines:

ALTER TRIGGER [dbo].[trig_UpdatePlacementCount]
ON [dbo].[DebtorHistory]
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;

UPDATE DI
SET placement_count = dh.file_no_count
FROM dbo.DebtorInfo DI
INNER JOIN inserted i ON i.file_no = DI.file_no
INNER JOIN (
 ...