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...

I'd urge you not to write a query based only on current data, but to make it more flexible:

--
insert into #tbl_backup_path values ('\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\DB_5\DIFF\ServerName$SQLEXPRESS_DB_4_FULL_20220327_091554.diff')


SELECT backup_path, LEFT(backup_path, LEN(backup_path) -...

It comes from the "ELSE 0", as ratbak implied.  The "0" by itself is a numeric.  Use "'0'" instead.

Btw, I think it would be better if you used a separate column for the string message to keep the diff column as only numeric, but that's just IMO.

            
April 4, 2022 at 2:22 pm
            
#4010543

            
            
                                                
        

(1) You need to verify that the 3 values are unique on at least one table; otherwise you will get the equivalent of a CROSS JOIN for all non-unique groups. ...

If you do not need full-text indexing nor filestream data on the reporting db, you could consider using a db snapshot as "DB2".  The initial create of the snapshot is...

I suggested to add them to BLOCKS...