Take one database and try running a full backup on it, then running a log backup (both manually, both using standard SQL Server BACKUP commands). Maybe that first full backup...

Sure. Still, lots of coordination will be involved in putting the view in place over the table so that the code isn't affected, renaming the table underneath that (because you...

Since it's a primary key on the table, you are really limited in choices. I'd would experiment on the side with every one you outlined. Testing is going to be...

One exception to what I just typed, the very first full backup and the very first log backup ever taken on a given database are kind of linked. However, once...

Full backups and log backups are not directly connected (Differentials on the other hand...). Just talking about full backups, whether from commvault or not, shouldn't be affecting log backups. Instead,...

Specifically this stuff:

(covert(int,n.SSICatyear) = year(convert(int,ep.proceduredate)))

Is going to kill performance.

In terms of overall impact to the server, I'd pick extended events over audit, every day. Audit is much more likely to have a negative impact on the instance.

And, calculations like this are best done into a variable once:
@SearchTextLenght + @Q - 2

Having it in the filter criteria leads to problems.

Just so the link to my book is on the thread...

Look below in my signature.

Go with Muthukkamaran's advice. Change the Cost Threshold to a higher value and then leave everything else at the defaults until you see a reason to change through monitoring.

Maybe I don't understand what you're doing then. I don't know how you went from backups to Elastic Query as a way to solve a database refresh. Since the backups...

Nope. Any script will compile or...

Change the query. Add the columns to the GROUP BY clause, or make it a sub-select or...