Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Payroll with Analytica...
Microsoft Dynamics GP (Archived)

Payroll with Analytical Accounting will hang when Building and Calculating payroll batch

(0) ShareShare
ReportReport
Posted on by steveendow 2,281

I have a client that uses Payroll with Analytical Accounting on GP 2015.  A typical payroll batch will have approximately 2,400 transactions.

When the user attempts to run Build or Calculate, Dynamics GP will regularly hang.  All GP windows will stop refreshing, the windows cannot be moved, GP is unresponsive, etc.

Sometimes other GP users will also experience a GP hang at the same time, causing their GP instance to become unresponsive.

I am speculating that other user activity is causing a lock, block, and likely a deadlock with the payroll process.  We are going to be testing some scenarios to try and verify this theory.

Does anyone have experience using Payroll with AA for batches > 1,000 transactions?  Has anyone seen this type of behavior during the payroll Build and Calculate process?

*This post is locked for comments

  • steveendow Profile Picture
    steveendow 2,281 on at
    RE: Payroll with Analytical Accounting will hang when Building and Calculating payroll batch

    Ya, I hear you.  The data grows pretty quickly, and MR seems to get pretty big.

    Glad to meet another 'big fan' of AA.  It's a gem.  ;-)

  • RogerRogerATX Profile Picture
    RogerRogerATX 1,515 on at
    RE: Payroll with Analytical Accounting will hang when Building and Calculating payroll batch

    >preferrably total RAM > total database size)

    Oh man.... I wish.

    Total GP databases approx 70-80 GB

    MR database (not the DM) - 203 GB

    tempdb allocation 604 GB (and we still run out...already have a 1 TB SSD for data.  I love AA.  Really love it.)

    96GB RAM

  • WindyCityGP Profile Picture
    WindyCityGP 1,051 on at
    RE: Payroll with Analytical Accounting will hang when Building and Calculating payroll batch

    It does indeed help!!

    Thank you.

  • Verified answer
    steveendow Profile Picture
    steveendow 2,281 on at
    RE: Payroll with Analytical Accounting will hang when Building and Calculating payroll batch

    Hi,

    After two days of load testing at the client, we believe the issue was caused by insufficient memory on the SQL Server.  They had 16GB of RAM for the server, and a SQL Server Maximum Server Memory setting of 13GB.  Their total of their databases was 13GB.  When under load, SQL was maxing out its memory and also putting memory pressure on Windows, causing all sorts of problems and instability with their server.  We even lost RDP connection to the server when we did the load testing.

    The client increased the server RAM to 32GB for the SQL VM and increased the SQL Max Memory to 24GB, and all of their issues went away.  They were able to run multiple payroll runs with AA to completion without issue, and the errors that the AP users were experiencing also went away.  So I think it was a combination of insufficient RAM for SQL, and insufficient RAM for the OS once SQL took its share.  I've now seen this problem at several other customers.

    My recommendation is to make sure you have plenty of RAM for your SQL Server (preferrably total RAM > total database size), and make sure that you do not set the SQL Max Memory setting too high.  Make sure to reserve lots of memory for Windows, otherwise SQL will starve the OS of memory.  

    Hope that helps.

    Steve

  • WindyCityGP Profile Picture
    WindyCityGP 1,051 on at
    RE: Payroll with Analytical Accounting will hang when Building and Calculating payroll batch

    Hi Steve,

    I would be interested in hearing if your client is still experiencing GP hanging during the build/calculate when using AA with payroll on GP 2015.  I started doing some preliminary testing of AA with payroll for my company, which has similar transaction levels, and if there is going to be significant performance hits, I'll have to look at other solutions.  --Thanks!

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,788 Moderator on at
    RE: Payroll with Analytical Accounting will hang when Building and Calculating payroll batch

    I had a less than pleasant experience with one of our clients because of payroll and they did not even use AA. They had a less than powerful server and every Friday when they ran payroll for 400 people the server would come to a crawl. Most times it would make it through but there were those days when it would not and then we had corruption. It got so bad they eventually went to a payroll service. I am looking forward to your post.

  • steveendow Profile Picture
    steveendow 2,281 on at
    RE: Payroll with Analytical Accounting will hang when Building and Calculating payroll batch

    Hi Richard,

    Thanks for the note.

    Today I did capture traces of all activity from the Build and Calculate processes while testing.  About 1.5 million records each.  ;-)

    Unsurprisingly, the payroll processes do have tons of queries against the AAG tables, but they also have tons of calls to the aag stored procedures, many of which are horrific, with cursors and looping in the procs.  It makes for a giant nested recursive mess of calls against the AA tables.

    And as a test, I traced the SQL activity when the AA window is opened from the Payables Trx Dist Entry window.  Tons of calls against the AA tables, many of which will produce shared locks on the entire table.

    When you add in the fact that the payroll processes make extensive use of SQL Server Transactions, I would not be surprised in the least if there is a deadlock when users interact with AA in other modules while payroll (with AA) is processing.

    We'll be doing a simulated load test tomorrow to try and induce the lock / block / deadlock.

    I suspect I'll have an interesting blog post when I'm done...

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,788 Moderator on at
    RE: Payroll with Analytical Accounting will hang when Building and Calculating payroll batch

    While I cannot specifically state that I have experience with this large number of payroll transactions, since Analytical Accounting adds triggers to many tables it does not surprise me to hear that it appears that GP will freeze. Perhaps you could run SQL Profiler while in the calculate checks process while you are the only user in GP just to see if it is progressing.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,494 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,307 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans