Azure SQL database TOP n Issues

I have a dev database with dtu model under s3 tier and uat db with dtu model under s7 tier in Azure SQL databases.

The issue is: I have a stored procedure which runs differently in dev and uat. It’s basically related to top n and actual data in both databases are ~ similar no big gaps.

  • In DEV TOP 100 takes > 20 seconds, and TOP 1000 takes < 2 seconds
  • In UAT TOP 1000 takes ~ >= 8 seconds, and TOP 100000 takes ~ <= 2 seconds

But there are actually less than 12k rows in each database.

Read more here: Source link