did you follow the steps here https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-two-instances-and-sql-server-configure-tutorial?view=azuresql

SQL MI to onprem does not follow standard way of doing it between 2 onprem servers.

as far as I remember the latest version of SQL BPA is for 2012 - https://www.microsoft.com/en-in/download/details.aspx?id=29302

nothing seems to have been done (or will be done ) on that tool so...

not much you can do about that query (which is the version I gave you) - the nature of the query does not really allow for pre-filtering or use of...

did you do the steps highlighted at https://cloudblogs.microsoft.com/sqlserver/2022/07/28/azure-active-directory-authentication-for-sql-server-2022/

times on a I7 6 core laptop

according to explan plan query cost (relative to batch= are 0, 0, 100% respectively  - so DEFINITELY NOT something to use.

- with while loop...

option 1 is incorrect - just discard it.

can you give the plan for the second? was expecting it to be a bit faster than that.

regarding your last query (from the second plan). two options below

option 1 is just a adjustment of your original query - does same thing but with 1 less join. may...

can you try this slight variation of my second example and give runtime and plan as well.

drop table if exists #weeklastday;
drop table if exists #temp1;

select distinct t1.EXPIRATION
into...

that plan is from you version of the change e.g. where you still keep the dateadd and your temp table does not filter or change the expiration date.

can you give...

how much faster? can can you also put new explain plan.

small error on my original code - the temp table was referring an non existing alias (t1)- easy to fix I think.

another attempt at simplifying it assuming that I correctly...

no difference.

your code was only looking for the the friday date on a particular situation e.g. when the EXPIRATION = Thursday

so by determining all the FRIDAY dates in advance, subtracting...

adding further indexes on this case likely not the best - the underlying table is a columnstore table.

the thing that would speed up the most is to have a computed...

a image of the plans don't help much - please post the real explain plan - actual if possible.

use https://www.brentozar.com/pastetheplan/ for it

SSIS has behind the scenes to retrieve the table metadata - this error seems to indicate a mismatch between the server and the client driver - or some parameters need...