Share via

Data load is too Slow between On-Premise AX 2012 and Azure VM SQL Server

Shivendoo Kumar 736 Reputation points
2020-07-10T00:19:51.283+00:00

Hi All,
Yesterday only I have migrated one of our Databases called "EDW_STG" to Azure VM SQL Server and Noticed that It started taking double time than usual. Example: When DB: EDW_STG was on On-Premise Server and to load 20 Million records using SSIS (From On-Premise 2012 AX to SQL Server on VM) If it was taking 2 hours, now after moving to Azure VM, it is taking around 4-5 hours.

I tried investigating what is going on using query: SELECT * FROM sys.dm_exec_connections WHERE session_id=54
and noticed that Write is very slow. Refer below screenshot.
11722-capture30.png

After checking with Infra Team, They confirmed that there is a network issue and they are trying to fix it. We have setup Site-to-Site VPN.

But I am bit worried as yesterday only I migrated to Azure VM and hope slowness has not do with anything else which I have missed. Any suggestion..?

Azure VM Size, SQL Database on Azure VM and Data Disk Details:
11773-capture29.png

Azure Virtual Machines
Azure Virtual Machines
An Azure service that is used to provision Windows and Linux virtual machines.
7,544 questions
Azure VPN Gateway
Azure VPN Gateway
An Azure service that enables the connection of on-premises networks to Azure through site-to-site virtual private networks.
1,450 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Didier3001 986 Reputation points Microsoft Employee
    2020-07-12T08:51:38.653+00:00

    Hi @shivendookumar-8455

    If you have only one disk for both the OS and the DB / Log, I would start by changing the disk configuration, add more disks but smaller and configure the cache as recommended below:
    A very good starting point are these two articles below:
    Performance guidelines for SQL Server on Azure Virtual Machines

    Disk caching

    --please don't forget to Accept as answer if the reply is helpful--

    Hope it helps,
    Didier

    1 person found this answer helpful.
  2. Manish Jha 236 Reputation points
    2020-07-17T12:30:01.88+00:00

    Hi Shivendoo

    What is the Disk Config type is it Premium SSD, Standard SSD or Standard HDD ? Is the Data and the OS on the same disk or have you created a new disk for the same

    And what about the caching

    Azure VMs running SQL Server, enabling Read-only caching on the data disks (for regular and TempDB data) can result in significant performance improvements. Log files, on the other hand, are good candidates for data disks with no caching.

    1 person found this answer helpful.
  3. Didier3001 986 Reputation points Microsoft Employee
    2020-08-01T11:18:21.343+00:00

    @Shivendoo Kumar

    Did you get a chance to go review the docs. Just wanted to verify that everything is clear for you now?

    --I hope this helps. Please Accept it as an answer and "Up-Vote" the answer or message(s) that helped you so that it can help others in the community looking for help on similar topics

    0 comments