Technically that's just a unique index,...

The indexes will come along.  Since they are part of the db, they get restored with it.

For more specific tuning, yes, we must see the query plan and the DDL for the table(s) involved, including all indexes.

Definitely not for the first one.  Bump that up to at least 40:

EXEC sys.sp_configure...

Note the "<" in the comparison; that is required.  This handles due_date correctly whether it is a datetime data type or a date.

;WITH cte_get_max_due_date AS (
 ...

--Removed, double post.

Definitely not for the first one.  Bump that up to at least 40:

EXEC sys.sp_configure 'cost threshold...

I have extensive experience in health care as well.  Experience directly in that field is in fact very useful.

Obviously industry terms may be different and have different connotations in each...

Look at table msdb.dbo.restorehistory.

You can clean up the existing code some, but the condition:

Prop_TYPE NOT LIKE '%Rond%'

is still going to require a table scan or a covering index scan (if a covering index...

I don't believe SQL will use an existing index for a constraint, even if the index perfectly matches the constraint to be added.

In your situation, the index and the constraint...

(1) Did you set on the "optimize for ad-hoc workloads" SQL Server setting?

(2) What is the "threshold for parallelism setting"?  In particular, did you leave it at the default of...

I just wanted to add in that I am not a big fan of the "SELECT * INTO" approach to table creation.  It recreates the table, but only the...

Typically I/O causes the most issues.

Therefore, another first quick thing to do is to look at the queries with the TOP (10) Avg I/O.  You can find these from SSMS.

Right-click...

Sorry, I wasn't being clear before about how to use transactions here.

If there are no BEGIN TRAN / COMMIT TRAN, then every modification (DELETE / INSERT / UPDATE) will be...