Thank you, this is helpful information. If the data values of a column are more evenly distributed then the idea is that the three plans will be similar if not...

You posted "After you create the Availability Group, SQL Server automatically adds two additional system databases—SQLAG-2022_master and SQLAG-2022_msdb". However, in the picture the new system databases are not in the...

Here is an illustration of the basic commands, from here you can learn what each means. You probably will only need to use a few.

Thank you for the informative article. I think a diagram of the work flow would provide a better understanding.

I would guess the answer lies in upgrading the design of the database so less of the values you are after need to be calculated with an inner select that...

It would be good to see the actual SELECT from that table. As mentioned before there could be function(s) there. Another possibility is a cartersian join.

"The maintenance installation script that creates all of the stored procedures is over 9000 lines. I am not going to spend the hours reviewing that "

I dont review the 9000 lines...

Thank you for taking the time to experiment and share your findings. This is important to note. I wonder though, if you would temporary switch the recovery_model to bulk_insert ,...

"determine whether the query optimizer chose to use a primary key index"

The execution plan will definitely provide that information. However so would sys.dm_db_index_usage_stats in controlled cases where you know...

The functions look quite standard. I could port these from SQL Server (not sure about the agg function though).

"Data Integrity:

Is data transferred reliably?

Are full and incremental loads supported?

Finally, does the tool validate post-migration data for consistency?"

1. This is a vague perspective as there is usually a all or...

I may be mistaken as I have not tested this out

INSERT INTO @Hierarchy

SELECT

h.ID

, h.ParentID

, h.Name

, @Level + 1

FROM

HierarchyBig h

INNER JOIN @Hierarchy t

ON h.ParentID = t.ID;

For this and the temp table,...

I notice you didnt post the query. Are you doing any SORT operations anywhere ?

ISNULL(d.DepartmentValidTo, DATEADD(d, 1, SYSDATETIME()))

A function in the ON clause can cause indexes to not be used. Can you tell from your newer execution plan after the index was created?

You could...

In your design you can create a batch history table. Then whatever script you run to insert into the tables from the data that only comes in twice weekly, add...