Implement Optimize for Ad Hoc Workloads Option for Azure Database
Please consider implementing a feature to allow turning on the optimize for ad hoc workloads option on a per-database basis for Azure databases. Right now this option is only available in on-premises SQL Server on a server-wide basis, where it's a great help for managing plan cache memory when dealing with 3rd party applications where we have no control over the SQL that gets thrown at the database. It would be great to be able to enable this option on a per-database basis in Azure. If you decide to implement this feature, then it would seem logical that turning the option on would also eliminate the related ad hoc SQL performance warning. Thank you!
Thanks for providing this feedback. There are merits to this suggestion and it is under review.
Joe Sack, Principal Program Manager, Microsoft
As a next best option, consider implementing "DBCC FREESYSTEMCACHE('SQL Plans')" for an Azure SQL database, which could be run periodically to remove the ad hoc plans from the plan cache. Currently, this command isn't supported for an Azure SQL database.