Database DTU 100% and Parametrized Query Cache

Question

We are getting Database DTU Percentage 100% and application slow and timeout error some times due to Parameterized Queries Cache. In that case our application not able to perform with Database. How to clear Parameterized Queries Cache and reduce DTU Percentage

Answer 1

Hi @Anand Ramani ,

Welcome to Microsoft Q&A platform and thanks for using Azure Services.

As I understand your ask your Database DTU is reaching 100% due to Parametrized Queries cache.

Could you please share more information about the parametrized queries details which are causing 100% DTU.

If it is Procedure cache, we can use ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

For limiting DTU percentage, please try option MAXDOP in database scoped configuration detailed at link : alter-database-scoped-configuration-transact-sql.

Next, you may try Resumable Online Index.
Link for creation of ROI: resumable-online-index-create-is-in-public-preview-for-azure-sql-db
Link for Index maintenance: modernize-index-maintenance-with-resumable-online-index-rebuild

Additionally, Database service tier scale up could also help.

Please let us know if any of the above suggestions assisted in resolution. If not, please share more details and we would troubleshoot further.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification

Answer 2

Thanks for your feedback