5

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?

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 '17 at 15:59
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.

2
  • 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 '19 at 4:29
  • That's the only workaround I know since Azure SQL Database does not have DBCC DROPCLEANBUFFERS. – Alberto Morillo Jun 9 '19 at 16:52
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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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