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.
Best Answer
What you're basically trying to do is measure storage speeds in the cloud. Unfortunately, that's not necessarily repeatable or reliable - it can suffer from noisy neighbors, other people doing development on the same database, varies with your storage type, etc.
Rather than clearing the buffers or measuring query runtime, try starting with measuring logical reads. It's the number of 8KB pages a query reads in order to accomplish its goals.
In either SSMS or Operations Studio, run:
And then run your query, and look in the output messages for the logical reads on each table - that's the number of 8KB pages it read. (Ignore physical reads - those can change from time to time based on what's in cache.) Generally speaking, reading less pages means a faster query - regardless of whether those pages are read from memory, or from disk.
If your query references a lot of tables, then instead of trying to add up those numbers with a calculator, copy/paste the messages into StatisticsParser.com. Gives you a nice summary of the logical reads overall, even across multiple statements in a batch. (Disclaimer: that open source site was written by one of my employees, but it's not affiliated with my company.)