Database DTU 100% and Parametrized Query Cache

Anand Ramani 1 Reputation point
2022-09-01T07:04:00.003+00:00

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

Azure SQL Database
{count} votes

2 answers

Sort by: Most helpful
  1. ShaktiSingh-MSFT 13,271 Reputation points Microsoft Employee
    2022-09-02T06:14:18.213+00:00

    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 130616-image.png or upvote 130671-image.png 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
    0 comments No comments

  2. Anand Ramani 1 Reputation point
    2022-09-14T08:07:47.213+00:00

    Thanks for your feedback

    0 comments No comments