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?

Improve this question
1
  • Why not just drop the db and recreate it ? You can only clear proc cache in Azure as of now.
    – Kin Shah
    Nov 27, 2017 at 15:59

2 Answers 2

Reset to default
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.

Improve this answer
3
  • 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 7, 2019 at 4:29
  • That's the only workaround I know since Azure SQL Database does not have DBCC DROPCLEANBUFFERS.
    – Alberto Morillo
    Jun 9, 2019 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 2, 2021 at 5: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.

Improve this answer
1
  • 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, 2020 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.