8

I want to do some performance testing on an Azure SQL database.

For SQL Server, I've read that DBCC DROPCLEANBUFFERS can be used to clear the data buffer cache, and DBCC FREEPROCCACHE can be used to clear the execution plan cache.

But neither are supported for Azure SQL databases. For the execution plan cache, ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE seems to be an alternative. But I can't find an alternative for the data buffer cache.

How do I clear the data buffer cache for an Azure SQL database?

mtmacdonald
  • 421
  • 2
  • 4
  • 6

2 Answers2

3

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.

Alberto Morillo
  • 1,600
  • 7
  • 8
  • Changing database tiers on my 130 GB database takes more than 12 hours! That's not a very good solution if your data is non-trivial. – Tony Isaac Jun 07 '19 at 04:29
  • That's the only workaround I know since Azure SQL Database does not have DBCC DROPCLEANBUFFERS. – Alberto Morillo Jun 09 '19 at 16:52
  • @TonyIsaac Why does it take 12 hours to change tiers? It's never taken more than 5 minutes for me, regardless of the size or scale of the database. – Dai Dec 02 '21 at 05:30
2

You can use ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) to Clear procedure cache. This article talks about Primary and Secondary in the context of Active Geo-Replication in Azure SQL Database.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ;

CLEAR PROCEDURE_CACHE

Clears the procedure (plan) cache for the database. This can be executed both on the primary and the secondaries.

Executing this statement will clear the procedure cache in the current database, which means that all queries will have to recompile.

Another resource:

(SQL) Tip of the Day: Clear the Proc Cache in SQL Azure by Shannon Gowen.

SqlWorldWide
  • 13,153
  • 3
  • 28
  • 52
  • 1
    This does not appear to have the effect. Taking some known long running queries as examples, I run them, they of course take a long time the first time around, and then are nearly instant on subsequent executions. I then execute ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; and the processing time is still instant. – Matthew Sep 20 '20 at 21:40