1

Conclusion

You can not. Microsoft explicitly states: "you cannot manually remove an execution plan from the cache" in this article called 'Understanding the Procedure Cache on SQL Azure'.

Original Question

On SQL Server a single execution plan can be deleted from the cache using [DBCC FREEPROCCACHE(plan_handle varbinary(64))][1]. There is different [documentation about DBCC FREEPROCCACHE on SQL Azure][2]. It seems that it removes all cached execution plans from all compute nodes and or control nodes (whatever those nodes might be, I don't know). I do not understand why SQL on Azure of the Server version would differ in this aspect.

However, I am looking for a way to delete a single execution plan from the cache on Azure. Is there any way to delete a single execution plan on Azure? Maybe using a query instead of a DBCC?

Mike de Klerk
  • 11,906
  • 8
  • 54
  • 76

1 Answers1

2

There is no way to remove single execution plan from cache.

If your execution plan is related to only few tables/one table and if you are ok with removal of cache for those tables as well, then you can alter the table ,add a non null column and remove the column.This will force flush the cache ..

Changing schema of the tables causes cache flush(not single plan, all plans) for those tables involved

I do not understand why SQL on Azure of the Server version would differ in this aspect.

This has to do with database as a offering, you are offered a database(this may be in some server with multiple databases) and some dbcc commands affect whole instance,so they kind of banned all DBCC commands.There is a new offering called Managed instance(which is same as on premises server,but with high availabilty of Azure database), you may want to check that as well

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • Great to see a link to point to information describing why this is not possible on Azure. Also great to have a kind-of-a-hack alternative to mark the execution plan as outdated, or flushing the cache as you put it. – Mike de Klerk Mar 27 '18 at 06:40
  • We are also in process of migrating to managed instance, if you have only one db,then azure db fits well,other than that managed instance may be better – TheGameiswar Mar 27 '18 at 06:54