hyperscale database slow performance

Wayne Bartkowski 676 Reputation points
2021-08-10T06:01:15.097+00:00

I have a Data Factory connected to Salesforce and is doing straight data loads eg no transformations, into a Hyperscale database. This was doing two scheduled loads per day and working perfectly until yesterday when the loads started to take inordinately large amounts of time. I opened up the development environment and connected the data factory to the same Salesforce tables and ran a few loads into the dev serverless sql db (not hyperscale) that all ran in the normal amount of time. Which seems to me to point to a database issue. When I connect to the production database using SSMS and run small queries such as count or truncate table ( these are only a few hundred thousand records) the queries run for a long period so i cancel them. If i run the data Factory loads manually the pre-run script "tuncate table" will run for hours even though the table is not big and the charts in Azure Performance area indicate the database is not being utilised at all. I was looking for a "stop and restart" but this does not appear to be an option with Hyperscale and i can't see where else to look to determine what is slowing it down.
I created some customised charts in the query performance insights area looking for locks and various other metrics but nothing indicates the reason for slowness.
Any pointers on where to look to see what is slowing the DB down.

Thanks

Azure SQL Database
{count} vote

Accepted answer
  1. Alberto Morillo 32,996 Reputation points MVP
    2021-08-10T17:41:52.357+00:00

    My suggestion is to identify the prevalent waits involved on those queries processes involved with the Hyperscale. Please use this article to capture waits per query an per session. Focus on the processes you find with the worst performance.

    Please make sure you do regular maintenance of your database. Defragment indexes and update statistics and run your queries/stored procedures/processes again. You may find no performance issues after database maintenance.

    Also make sure your database is not missing some important indexes. How many indexes your database is missing based on this query.


0 additional answers

Sort by: Most helpful