Time to employ extended events and capture the query metrics. That's the single best thing you can do. Identify how long each query takes, how frequently they are called,...

The same as with any performance problem, you have to gather metrics to identify where the issues lie. I can't tell you what the problem is because other than high...

That looks OK to me. Does it work?

Check your databases. Sounds like one is corrupted. Run DBCC CHECKBD on them (during off hours, this is a fairly intensive query).

First thing I'd do is understand if you're doing a lift & shift and just moving to Azure VMs, or, if you're implementing Azure SQL Databases or Azure Managed Instances....

That suggests a strong and distinct difference in I/O behavior. Are the different databases on the different instances on different disks?

Please, capture the wait statistics for the query on both fast and slow instances. Just running SELECT * over and over isn't going to tell us much unless we gather...

So, the differences in index size and free space there suggests that the two databases are not identical. Was an index rebuild run on one of...

Ah, but "almost" is not identical. If everything is equal, performance should be identical (within a small margin of error, again,...

Identical database. Check?

Identical query. Check?

If either of those is wrong, one database is older than another or something, statistics updates were run, whatever, then everything changes. However, assuming identical databases...

I bet it's configured there by default

run the query on both servers :

SELECT *
FROM sys.configurations 
WHERE...

The thing is, SELECT * from a table without a where clause is just a test of server settings, database settings, load, contention, and hardware. It's not anything else. There's...

Did the recovery model get changed and the logs are now huge?

Just piling on here.

When comparing the performance between two systems, we have to know that every possible thing is identical before we can say "See, SystemA is sucky and SystemB...

Yes. If anything has changed in the structures such that the forced plan is no longer valid, it'll just get ignored.