Thanks Tim and Phil!

It is nice to hear that our setup isn't anything out of the ordinary and that other experts have similar issues with it that we do.

Our plan...

• This reply was modified 5 years, 9 months ago by Mr. Brian Gale. Reason: changing some "my"s to "our"s, I's to we's and fix some grammar

I imagine you could.  I am currently not using Query Store on my servers (too old... need to do upgrades), but my understanding is that it stores the execution plan...

Pretty sure the problem there is with the XML NameSpace (xmlns).  You are defining a namespace in the XML, but then not specifying the namespace in the SQL.  You have...

Quick and easy fix to this - need to mark the variable as an OUTPUT variable in the parameter assignment part of sp_executesql.

In your EXEC statement, change:

@xml=@xml

to

@xml=@xml OUTPUT

Something to note...

I'd recommend against implicit conversions wherever possible.  There are a lot of articles online about implicit conversion and even SQL Server, via the execution plan, will give a warning about...

First, this sounds like a huge security risk.  linked servers with sysadmin permissions on them sounds like a good way for someone to toss a "drop database" or a shutdown...

I think either are acceptable, but I think one will have better performance.  I am pretty sure "NOT NULL" is not sargable so you may have a performance hit by...

That sounds like a maxdop and cost threshold configuration issue.

But if you execute sp_who2 and look for multiple copies of the same SPID, that'll show you how many threads are...

this page has a good writeup on page splits:

https://blog.waynesheffield.com/wayne/archive/2016/10/smart-page-split/

but if you think of it like a phone book, with each page in the phone book holding 8 KB of data,...

After the restore completes, are you still seeing those errors?  The line:

06/03/2020 17:43:21,spid56,Unknown,The tail of the log for database DATABASE is being rewritten to match the new sector size of...

To fix problem number 1 (LEFT or SUBSTRING error), lets look at your query for all cases of LEFT or SUBSTRING.  That gives us this small chunk of  your code: