Database Engine Tuning Advisor in Azure

Question

Post reply

Database Engine Tuning Advisor in Azure

BOR15K

SSCertifiable

Points: 5894
More actions
June 5, 2024 at 2:54 pm

#4426397

Hello, all
We have several performance issues, which I think could be mitigated by proper indices, better queries or even partitioning as some tables have over 1B records.
I was hoping to use Database Engine Tuning Advisor, as I would do on my local copy of the DB in my previous roles, but seems it does not work in Azure, so as per Dr Google's advise, I opened Azure's console, went to Performance recommendations and found... nothing: it recommends an index to two irrelevant to the problematic queries tables, which store various comments related information.
What is the best tool to perform Azure SQL DB analysis, please?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

I found a suggestion on another site (not sure I am allowed to mention here), which is based on sys.dm_db_tuning_recommendations table:

WITH DbTuneRec
AS (SELECT ddtr.reason,
           ddtr.score,
           pfd.query_id,
           pfd.regressedPlanId,
           pfd.recommendedPlanId,
           JSON_VALUE(ddtr.state,
                      '$.currentValue') AS CurrentState,
           JSON_VALUE(ddtr.state,
                      '$.reason') AS CurrentStateReason,
           JSON_VALUE(ddtr.details,
                      '$.implementationDetails.script') AS ImplementationScript,
  ddtr.details
    FROM sys.dm_db_tuning_recommendations AS ddtr
        CROSS APPLY
        OPENJSON(ddtr.details,
                 '$.planForceDetails')
        WITH (query_id INT '$.queryId',
              regressedPlanId INT '$.regressedPlanId',
              recommendedPlanId INT '$.recommendedPlanId') AS pfd)
SELECT qsq.query_id,
       dtr.reason,
       dtr.score,
       dtr.CurrentState,
       dtr.CurrentStateReason,
       qsqt.query_sql_text,
       CAST(rp.query_plan AS XML) AS RegressedPlan,
       CAST(sp.query_plan AS XML) AS SuggestedPlan,
       dtr.ImplementationScript,
   dtr.Details
FROM DbTuneRec AS dtr
    JOIN sys.query_store_plan AS rp
        ON rp.query_id = dtr.query_id
           AND rp.plan_id = dtr.regressedPlanId
    JOIN sys.query_store_plan AS sp
        ON sp.query_id = dtr.query_id
           AND sp.plan_id = dtr.recommendedPlanId
    JOIN sys.query_store_query AS qsq
        ON qsq.query_id = rp.query_id
    JOIN sys.query_store_query_text AS qsqt
        ON qsqt.query_text_id = qsq.query_text_id;