Decreasing ODX transfer time

I would also be intrested to know the reason behind this. 

Hi @Victor 

Please try to use SQL profiler in Azure Data Studio to identify which step is taking the longest time.

Also are you using incremental load? Can you please share screenshots of table settings?

Have you considered using simple mode for these tables without transformations in an attempt to improve performance?  

We have had some customers reporting performance issues with hyperscale recently, so it might be worth reaching out to somebody at Microsoft as well.

Hello @Christian Hauggaard

We are running incremental load on as many tables as we can. The load example (an hourly load) therefore has a combination of fully loaded and incremental tables. 

We have simple mode on every table in the mdw + any DSA tables that are not having transformations. 

We made the switch from Premium DTU (1000)  to hyperscale two weeks ago to try and get a increase in performance, but it has stayed the same. We first tried an increase in dtu and then later increased vcores from 8=>12 =>16 without an improvement in odx tranfser (only data cleansing got better).

We are seeing an increase in ODX data transfers over the past months. Alot of smaller tables taking longer time to run odx execute on.

It reminds me of a situation a year back when our odx backlog file was corrupted which made every table take slightly longer time every load. However, we are not seeing the same error messages to indicate it’s the same problem.

Previously when we needed better performance we tweaked the database, however this is not improving the odx transfer.

DSA => MDW step is extremly quick and barely noticeable in the execution package.

The issues seems to lie in the data lake => dsa step.

Here is a table in the dsa with 3 rows and 3 columns I ran twice, that took 60s  to run execute on in the dsa while no other package was running.

Today while monitoring the loads, I saw the CPU usage on our vm machine running the ODX server sometimes spike to 90-100%.

I assume this bottleneck ado.net transfer?  However, most of the time it is running on sub 60% which was the case during the above example.

Hi,

there are quite a few things to look at here:

• look at your database metrics at a fine time granularity: you are probably running into log i/o governor waits (100% log I/O). Make sure you choose the max-variant of the metrics and choose a fine enough time granularity so your peaks are not hidden
• look at the query store in your database through SSMS and analyze your waits. If the log i/o governor is a fairly high share then you are bottle-necked by that. The only way to get faster log i/o is by running IaaS or changing the amount of data or ordering of load.
• look at the most expensive queries in your query store: if you are spooling to TempDB, that is slower on Hyperscale than on the non-Hyperscale databases
• if you are using ADO.Net to transfer from ADLS to Azure SQL DB Hyperscale, you may be limited by the memory on your TimeXtender application server. The parquet files need to be transferred to the VM and decompressed there before being sent to the Hyperscale DB.There is a setting to limit the memory, if you are not running out of memory you can increase this. If you are running out of CPU you can of course add more cores to the VM. If you are running a VM in the D-series, consider scaling it to an E-series for more RAM per core
• You can consider using ADF with an Azure runtime or self-hosted integration runtime on a dedicated VM (or cluster) to make this transfer (potentially) faster. This has some settings you will need to tweak to ensure fast flows
• If your ODX is refreshing data, any DSA transfer will wait for that refresh to finish. This is shown as Data Movement time and not separately visible
• If your ODX Data Source runs out of parallel connections (DSA transfers count), your DSA transfer will be pending and waiting in the queue. This shows up as Data Movement time and is not separately visible
• I have not seen much benefit from the premium memory-optimized compared to regular premium hardware, but that will depend strongly on the specific cleansing you are doing. Adding cores does not increase log i/o that is 100 mb/s for all scalings, premium hardware simply ensures you always get gen8 CPU instead of something from gen5 to gen8
• There have been some issues with Hyperscale resources about a month ago, but those are resolved now as far as I am aware

Hello Rory,

Thank you for your feedback.

I have looked into each point before but I went through them again today running tests.

I have been keeping track of the log io and while we reach 100% at later stages (understandable due to data size) it is not during the time where we run alot of small tables where we have a MAX log io of 40%, nor during my testing when schedulings är turned off.

CPU or Memory on the VM machine doing ado.net never went about 30%.

We do not have any sources running ODX, syncs, transfers or any other tasks during scheduled loads or during my testing.

But the question remain the same. 

Why is a 3 row table taking 60s-120s for Timextender to transfer from the odx data lake to a sql database? The ODX transfer tasks takes 1s in comparison. 

This table is not running during expensive queries during any of my testing.

The log/io, cpu usage and any other metric on the sql database is below 20% during all testing on the above table. The vm machine is running at 20-30% cpu and memory. No ODX tasks are running that should be affecting it.

Please note I took a small table as an example, but this is affecting alot of other “small” tables aswell.

I can find no metrics that can explain this bottleneck nor can I find any way to improve it.
 

@Victor do you have an update on Rory’s questions above? Were you able to restore a backup of your Hyperscale database to a new database?

Hello,

@Christian Hauggaard, sorry for the late response.

During the past month we have finally had time to run further tests.

We tried several different setups and combinations on the same data sets running for several days.

1,We went over to business critical again and got the same result.

2,We restored aswell as went back to hyperscale and got the same result.

3.We tested several different VM machines and while we found one that resulted in a slightly better transfer compared to the others it was not noticeable enough.

I also had the opporunity to test the ODX with a data lake (as we had above) and the same ODX with a SQL database to compare the differences in another enviornment.

The performance between the two were very different and the results suprised me.With the ODX running on a SQL database having on average 3x fast transfer times with ODX transfer tasks (source => odx) and aswell as better odx => dsa performance.This was tested across multiple sources and data sizes.

While I do not have the technical knowledge to fully understand the infrastructure behind the solution at a depth, this testing leaves me to believe the culprit lies in the parquet and/or data lake structure as the difference between them was so big. 

Ignoring the cost difference for the moment, I would have a hard time recommending data lake over sql database for any solution requiring quicker loads and data based on the results we got.

Has anyone else tested this and seen different or similar results?

Thanks,
Victor