web
You’re offline. This is a read only version of the page.
close
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

I have the same question (0)
  • Richard Wheeler Profile Picture
    Richard Wheeler 75,850 Moderator on at

    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.

  • steveendow Profile Picture
    steveendow 2,281 on at

    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,850 Moderator on at

    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.

  • WindyCityGP Profile Picture
    WindyCityGP 1,069 on at

    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!

  • Verified answer
    steveendow Profile Picture
    steveendow 2,281 on at

    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,069 on at

    It does indeed help!!

    Thank you.

  • RogerRogerATX Profile Picture
    RogerRogerATX 1,515 on at

    >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

  • steveendow Profile Picture
    steveendow 2,281 on at

    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.  ;-)

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans