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

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.

hyperscale database slow performance

Accepted answer

Alberto Morillo 33,426 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.
Please sign in to rate this answer.
Binway 696 Reputation points

2021-08-11T05:10:15.793+00:00

Thanks Alberto,
I ran UPDATE STATISTICS "Table Name" and DBCC INDEXDEFRAG (databasename, 'tablename', 'index name') for each of the tables and it does not appear to have made any difference.
I have also run the queries to capture the waits:

/* identify those queries waiting the most.*/ select qt.query_text_id, q.query_id, p.plan_id, sum(total_query_wait_time_ms) as sum_total_wait_ms from sys.query_store_wait_stats ws join sys.query_store_plan p on ws.plan_id = p.plan_id join sys.query_store_query q on p.query_id = q.query_id join sys.query_store_query_text qt on q.query_text_id = qt.query_text_id group by qt.query_text_id, q.query_id, p.plan_id order by sum_total_wait_ms desc /*use the plan id to show the waits related to that plan id from above query*/ select wait_category_desc, sum(total_query_wait_time_ms) from sys.query_store_wait_stats where plan_id = 2166 group by wait_category_desc /*identify which query is related to that plan using query_text_id.*/ select query_sql_text from sys.query_store_query_text where query_text_id = 2098

These indicate that memory usage seems to be the highest:
eg Memory 80331940

But I am not certain how you increase memory in hyperscale or why it would happen all of a sudden. The jobs all ran fine on Monday at 6;45AM but then went awry at the 12:30PM schedule. The charts in the performance monitoring in Azure indicate the DB is doing very little eg CPU at .2% etc.

Alberto Morillo 33,426 Reputation points MVP

2021-08-11T16:41:47.443+00:00

Could you please share with the exact names of the waits prevalent? Did you run the query for missing indiexes? How many indexes are missing? Missing indexes usually increase memory usage, High IO on storage subsystem, and/or CPU.

Binway 696 Reputation points

2021-08-11T22:30:11.957+00:00

I am using data factory to load 17 tables. All but 5 load in the normal time of less than a minute, these 5 take upwards of 3 hours where they were only taking a few minutes. The data factory is just doing a direct copy from Salesforce to the DB. I ran the missing indexes query and it did not indicate there were any missing. For each of these tables, whilst the exact numbers are different, the waits present a similiar result to below. ![122458-consumption.jpg][1] [1]: /api/attachments/122458-consumption.jpg?platform=QnA IOn a positive note after running the defrag and update stats when I do a query such as count(*) they are now returning results in less than a second, previously they would run for a few minutes. So it appears it is just the data loading that is giving the issue. Thanks

Alberto Morillo 33,426 Reputation points MVP

2021-08-12T00:22:06.003+00:00

Wonderful, so we need to focus on ADF. Let me see what we can do.

Alberto Morillo 33,426 Reputation points MVP

2021-08-12T00:48:53.7+00:00

You know sometimes we think we selected an Azure SQL Database with a huge amount of RAM but we may be wrong. Please run the following: SELECT max_db_memory FROM sys.dm_user_db_resource_governance. On a P2 you may find that you have a limit of 11.3 GB of memory. Set an appropiate servicel level tier in accordance with the amount of data your are importing. You can scale up tier only for the duration of the ETL.

Binway 696 Reputation points

2021-08-12T03:41:32.083+00:00

Thanks so much for your time Alberto.
I doubt that any of my efforts have made any change to the data base given that it is just loading data direct from Salesforce. Anyway the runtimes have been resolved and are now completing the dataloads in approx 15 minutes.
The only things i did were your recommended Index defrag and Update statistics for each table which may have helped the tableau reports interrogating the data run a bit better. SELECT max_db_memory FROM sys.dm_user_db_resource_governance query produced this number - 7836980.
I will continue to monitor for a few days but think this has somehow resolved itself.

Thanks.

Alberto Morillo 33,426 Reputation points MVP

2021-08-12T04:35:55.49+00:00

With 7 GB of RAM you are doing very well. On any workload you run on that database keep the queries tuned for best performance. Use Ola Hallengreen scripts and create an Azure Automation job for database maintenance.
Sign in to comment

Share via

hyperscale database slow performance

0 additional answers