How can I clear buffer for a SQL Server DB Hosted On Azure?

Question

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.

Answer 1

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.)

• Md Haidar Ali Khan: SET STATISTICS IO ON; for +1
• Saurabh: Sure I will try it and find the actual logical reads and try to reduce them. Will get back with an update.
• Md Haidar Ali Khan: @BrentOzar, sometimes I run the ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) to Clear the procedure cache in Microsoft Azure.
• Saurabh: @Haider doesnt ALTER DATABASE SCOPED CONFIGURATION just clear the stored execution plan for the SP and not the actual buffered data?
• Brent Ozar: @Saurabh that's about 589MB worth of data. Understanding whether it's good enough will depend on how frequently the query runs, how big your Azure SQL DB hardware is, etc. For help with a specific query, post a different question and follow these guidelines: brentozar.com/archive/2009/03/getting-help-with-a-slow-query