Could it be that the SQL Server being Standard edition caps the performance, thus it could be solved by upgrading to Enterprise?

Yes: Standard has a memory limit of 128GB &...

You don't hire good managers, you train good managers.

You don't hire good employees, you train good employees.

I agree, but unfortunately many companies subscribe to management theory that teaches exactly the...

How many rows in dbo.ELIGIBILITY? What is definition of dbo.ELIGIBILITY (including id_ELIGIBILITY_PREMIUM_GR...)?

What's actually in the where clause? Any user-defined functions? functions wrapping columns (ISNULL, COALESCE, CAST/CONVERT FORMAT, etc.)? Views (especially...

😀

Maybe

(target.tblAwardID =  source.tblAwardID or  source.tblAwardID is null)

Look at this line:

and C.nSerialNo = C.nSerialNo

Did you mean for C.nSerialNo to join to a different table?

If so, fix it.

If not, remove it -- It's of no...

Phone call? No.

But for mobile phone carriers with an email gateway you can send SMS via email.

As you discovered, case returns an expression. It does not execute an expression.

Uncomment it? (remove the double hyphens before ,[Date])

Does that code actually work with the extra comma?

In addition to his other tips, as Lowell illustrated but didn't explicitly advise, make sure you are enabling compression on your backups.

Is server virtualized? Make sure you have sufficient I/O...

What about SQL Server native backup does not fit your requirements?

If refnum is int in both tables, why are you comparing it to a string ('123456')?

Pass it as an integer without quotes.

Are both tables in dbo schema? I recommend you...

"this is not enough of an annoyance for most people to seriously start looking at something like PostgreSQL"

Most? Probably still true. But they might want to re-evaluate their over-confidence and...

RedGate SQL Search is an incredibly valuable tool!