A less than ideal Dynamics GP SQL Server setup

By Steve Endow

I recently wrote a post about a customer where Dynamics GP took 10 times longer to post a GL batch than one of my development virtual machines.  So a GL batch with 1200 lines that took 6 seconds to post on my server would take 60 seconds in the customer's environment.

I had another call with the GP partner today to confirm the symptoms and get some information about the customer's SQL Server.  During the call, I saw another GL batch with 1500 lines that took 88 seconds to post.  Not very good.  That's only 17 records per second, which is abysmal performance for SQL Server.

The SQL Server is a quad core machine with 16GB RAM.  The consultant didn't know if the machine was physical or virtual.  The customer has a single production company database with an MDF file that is 20.5GB, and an LDF file that is 14GB.

But, they have a TEST database, which is a recent copy of production, which has a 20.5GB MDF and a 7GB LDF.

And then they have an additional backup copy of their production database for some reason, which has a 25GB MDF and a 14GB LDF.  They also have an old copy of their production database from 2015, which has a 17GB MDF and a 14GB LDF.  And there's another random test copy that has a 14GB MDF.

But wait, there's more!  There is the active Dynamics database, which has a 5.6GB MDF and 4.6GB LDF.  And there is not just one, but TWO other copies of the Dynamics database--one 3.2GB and the other 2.7GB.

So the server only has 16GB of RAM, but there is well over 100GB of online databases on the server.  If we're optimistic, let's say that only two databases actually have any activity: the main production and test companies.  Those two databases, plus the Dynamics database, total over 45GB.

So 45GB of active databases on a server with 16GB of physical RAM.

I then check the SQL Server Maximum Server Memory setting, and no surprise, it had not been changed from the default value.

The combination of insufficient RAM and lack of a reasonable Maximum Server Memory value is likely putting significant memory pressure on Windows, which then contributes to abysmal SQL Server performance.  I've seen a similar SQL Server with just 4 GP users become unresponsive, lock up GP clients, and drop network connections when under load.

The Dynamics GP consultant I spoke with was not familiar with SQL Server configuration or memory management, so I recommended that the consultant speak with his team and the customer about increasing the RAM on the server and setting the Maximum Server Memory setting to a reasonable value.

Unfortunately, I can't be certain that those two items will dramatically improve their GP batch posting performance--although I'm pretty sure it won't hurt.  Maybe the databases need to be reindexed or optimized, or maybe there is some other issue causing the poor performance. If they do upgrade the server memory, I'll try and follow up with them to see if the changes improve Dynamics GP posting performance.

If this topic is of interest to you, I recommend checking out the book Troubleshooting SQL Server by Jonathan Kehayias and Ted Kreuger.  There is a link on the page to download a free PDF of the book.  It's a few years old, but many of the SQL Server fundamentals remain the same.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+

http://www.precipioservices.com