Azure SQL Database directly supports clearing the proc cache of the current user database without any hacks:
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
Additional Information
The following script (by Shannon Gowen) can be used to watch the process step-by-step:
-- run this script against a user database, not master
-- count number of plans currently in cache
select count(*) from sys.dm_exec_cached_plans;
-- Executing this statement will clear the procedure cache in the current database, which means that all queries will have to recompile.
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
-- count number of plans in cache now, after they were cleared from cache
select count(*) from sys.dm_exec_cached_plans;
-- list available plans
select * from sys.dm_exec_cached_plans;
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:
SET STATISTICS IO ON;
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.)
Best Answer
Azure SQL Database does not have at this time something like DBCC DROPCLEANBUFFERS available for cleaning the data on buffer pool, but scaling up or down the tier of the database has the same effect of DBCC DROPCLEANBUFFERS and reduces the memory allocation also for the first few minutes.
Hope this helps.