Sage 100

 View Only

  • 1.  SQL Optimization Tips for v2025 Premium

    Posted 10-13-2025 16:56

    Upgraded client from v2022 ADV to v2025 Premium.

    In v2022 ADV, the client's daily 30K line JE VI import would take about 10 minutes or less (their words) to upload and another 10 to post.  In v2025 SQL, the upload is taking 30 plus minutes and about as long to post.  Over an hour in total.

    I increased the size of the VM which has more IO capacity (see below).  I would think 8 vCPU and 32GB of RAM would be more than enough for this setup.  I have also included the current utilization of both VMs below as well.  Neither are using half of the resources assigned to them.  The previous SAGE01 server was sized as an B8ms as well.  Let me know your thoughts. 

    Current Size

    • FF-ACT-CUS-SAGE01 - D8as v4 (8 vCPUs, 32GB RAM, Max IOPS 12800)
    • FF-ACT-CUS-SQL01 - D8ds v4 (8 vCPUs, 32GB RAM, Max IOPS 12800)

    Previous Size

    • FF-ACT-CUS-SAGE01 - B8ms (8 vCPUs, 32GB RAM, Max IOPS 4320)
    • FF-ACT-CUS-SQL01 - B8ms (8 vCPUs, 32GB RAM, Max IOPS 4320)

    Current Utilization

    • FF-ACT-CUS-SAGE01 
      • CPU: 7%
      • Memory: 20%
    • FF-ACT-CUS-SQL01 
      • CPU: 9%
      • Memory: 42%

    I increased the tempdb size in SQL Server from 8GB to 16GB. That reduced the allocation contention for tempdb. Before the expansion, I was seeing latch waits into the thousands and now it's hovering around 0.

    Once the full backup runs for the first time, the transaction log backups will run every 15 minutes. It will delete backups older than 1 week. 

    Upload is from an RDS to the Sage01 server.  Servers are hosted on Azure.  Trying to ascertain where the upload file is located (public drive somewhere, not on the RDS.

    Any optimization thoughts?



    ------------------------------
    Jeff Schwenk
    Owner
    Bottomline Software, Inc.
    Waynesboro VA
    (540) 221-4444

    Improving bottom lines for over 25 years!
    ------------------------------


  • 2.  RE: SQL Optimization Tips for v2025 Premium

    Posted 10-13-2025 20:01

    how much memory is allocated to SQL?  Have you tried copying the upload file to the Windows Server that SQL Server is installed on?



    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------



  • 3.  RE: SQL Optimization Tips for v2025 Premium

    Posted 10-13-2025 22:21

     FF-ACT-CUS-SQL01 - D8ds v4 (8 vCPUs, 32GB RAM, Max IOPS 12800).  That is my next step is to put it on the Sage server then upload



    ------------------------------
    Jeff Schwenk
    Owner
    Bottomline Software, Inc.
    Waynesboro VA
    (540) 221-4444

    Improving bottom lines for over 25 years!
    ------------------------------

    Original Message


  • 4.  RE: SQL Optimization Tips for v2025 Premium

    Posted 10-13-2025 22:28

    @Jeff Schwenk not sure what you mean.  Is SQL installed on the same VM as the Sage 100 applications? Is the SQL server on the same physical server as the Sage applications?  There is a difference between what is memory is allocated to the Windows Server that has SQL installed and the memory allocated specifically for the SQL server application.  If it were me, and the SQL server is different from the Sage 100 application server, I would put th eupload file on the Windows Server that contains the SQL Server instance.



    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Original Message


Related Content