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

If you're using Azure SQL Database I'd look into the COPY DATABASE command in Powershell. That's going to let you capture a quiet copy of the database and then you...

Look through the system views and you ought to be able to figure it all out. sys.dm_exec_query_stats has a number of values like sql_handle for finding statements and the plan_handle...