Actually we have a database which is not part of availability group and it's reading the data from secondary replica and inserting into the database which is not a...

Take a look at the execution plan. It's going to be all one allocation.

UNION ALL isn't a bad performer in general. In fact, usually, you get better performance with UNION...

Agreed. 5 is too low. You can query the plans in cache to get a sense of the costs. I have a blog post on it.

Literally no magic switch or setting that's going to improve performance most of the time, like Michael says.

In addition to his questions, what is your cost threshold for parallelism. Adjusting...

Capture wait statistics for the individual query. Also, get the execution plan when it's slow and when it's fast and compare the two.

I'd suggest getting performance metrics on individual statements within the query. AND, as has been said several times, the execution plans for the queries. Those two bits of data are...

Try batching based on 102,400 rows. The reason I say that is because less than 100,000 rows in a batch and columnstore indexes use the delta store until you get...

Hi Jonathan, Thanks for the input. I would like to know if there is any implications/perf issues of using MERGE statement especially when dealing large sets of data?...

Hi Grant Sir,

Thanks for the pointers and sharing the link of capturing Waits. will check with them replace old join syntax's with ANSI joins.

Couple of questions:

1. for conditions like...

Literally no way to really answer this question for you. Scale depends on so many different factors.

Let's look at it like this. The various different data management systems that are...

More data. "Something is slow and here are a bunch of wait stats" doesn't tell us anything. I'd suggest enabling Query Store on the database in question. It's relatively lightweight...

First, not trace. Use Extended Events. It puts far, far less of a load on the system. Plus, you're on SQL Server 2017. Every single piece of new functionality is...

Weeks? You're doing it wrong. You should be able to...

You can absolutely create logins (and there are several choices on how to do this) that only have enough privileges to do certain things and not others. And then, yeah,...

Can a column contain more than one data type? The short answer is no, unless the column is of data type variant. I do not recommend using this. I...