I am trying to fine tune my queries and Stored Procedures. The Issue that I am facing is that there is no way to clear the Database buffers in Azure. Which means that I cannot use
DBCC DROPCLEANBUFFERS on Azure.
Is there some workaround by which I can clean the buffers so that I can test the Stored procedures' actual time? I was thinking of reduing the Page Life expectancy (though I am not sure if I can actually do it).
I tried changing the tier's of the DB as it was mentioned in one article that it might clear the buffer, but it didn't have any effect.
I need this as the local machines (I5 with 8 GB ram) are too fast and the query which takes a second to execute on the local machine takes around 30 - 40 seconds on an Azure S2(20 DTU) SQL DB.