Two lesser known SQL Server performance tips: Appropriate for Dynamics GP?

By Steve Endow

At the amazing GPUG Summit 2016 mega conference in gorgeous Tampa, his eminence John Lowther, MVP, hosted a session "SQL Administration Made Easy", where he discussed several great tips for simplifying SQL Server administration for Dynamics GP.

Sunny Tampa, October 2016

In his role at nJevity, John and his colleagues have to manage hundreds of SQL Server instances, so they've worked hard to optimize their SQL configurations to work well for Dynamics GP.

As a result of that session, I've kept my eye out for SQL optimization tips, and I have come across two lesser known configuration settings related to SQL Server.



Lock Pages In Memory

One question that came up in the session was whether the Lock Pages In Memory (LPIM) setting should be used with SQL Server in a Dynamics GP environment.  My comment at the time was that I had read that it is generally not recommended.

If you have never heard of this setting, don't feel bad.  I only learned about it while troubleshooting some strange SQL performance issues for a customer--and only then after many hours of research.

Yesterday I found this excellent article on LPIM, which helped me better understand why you might want to consider enabling it, despite common recommendations to the contrary (and even contrary to Microsoft and some SQL Server MVPs).

https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/

Side note:  If you are a SQL geek like me, the author of the article also has an excellent free e-book on SQL Server that I highly recommend.  The book discusses SQL server memory management better than any other source I've found so far.

https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/


So back to the question:  Should you enable LPIM?  Before I read Jonathan Kehayias's article on LPIM this week, I would have said No, it isn't necessary for a typical Dynamics GP customer, as that is the advice that I have read previously--don't enable it unless you are in a VMWare environment using a Balloon Memory driver.

But now that I've read Jonathan's post on LPIM, I'm wondering if some of my customers who are experiencing SQL (and therefore Dynamics GP) performance issues might be having SQL memory issues, perhaps due to a hard working set trim.

However, I'm a bit skeptical about hard trims being the cause of the GP SQL issues I have seen.  I typically see that the SQL Server Maximum Server Memory setting is too high.  For example, the customer might set the SQL max memory to 14GB on a server with only 16GB--or they don't set it at all and use the huge default value.  SQL Server then uses all 14GB for its buffer pool, and additional processes and non-paged pool end up consuming more memory, putting severe memory pressure on Windows.

SQL Server is supposed to respond to low memory notifications from Windows, but in my experience, that process just doesn't work--or it is so slow that it can't respond before the server performance degrades significantly.  When SQL Server is under load, it will crush Windows if sufficient memory isn't reserved for the OS.  I've had a SQL Server stop responding, lose network connectivity, and disconnect me from the RDP session due to memory pressure from SQL.  So based on my experience, SQL Server seems to always win memory fights, which is why I don't think I've seen a SQL performance issue due to hard working set trims by Windows.

But, as Jonathan notes in his article, unless the hard trim is significant, you may never realize it has occurred unless you know where to look for it.  I could have been observing the consequences of a hard working set trim and misinterpreted it as SQL Server memory pressure on Windows.

So while I don't have a definitive answer as to whether a Dynamics GP customer should enable Lock Pages In Memory for the SQL Server service account as a standard practice, I would recommend being aware of it.  If you experience strange performance issues with SQL, you can at least consider it as an option--but you should be aware of how it will work in your particular environment.  If you are running SQL in VMWare with overcommitted RAM, you will have different concerns than if your SQL Server is running on bare metal with plenty of RAM.

My recommendation is to ensure that your Dynamics GP SQL Server has a more than enough RAM and avoid any situation where either SQL or Windows needs to request more memory, thereby avoiding the entire LPIM discussion.  Most Dynamics GP databases / environments are not very large, so 16GB or 32GB is often plenty of RAM for the SQL Server.  But large GP environments likely have more data than RAM, which is where the LPIM discussion becomes more important.

UPDATE: I received a note about one key item that I forgot about: Management Reporter.  If you use Management Reporter, particularly with the MR Data Mart, then you likely know that it is a massive SQL Server hog, and any "typical" Dynamics GP SQL Server memory recommendations are insufficient.  Management Reporter often has databases that are much larger than than the Dynamics GP company databases, and the MR report processing often consumes many gigabytes of RAM.  So if you run MR, one option might be to install it on a separate SQL Server so that it does not negatively impact Dynamics GP, or if you do install it on your GP SQL instance, make sure you have plenty of RAM, and that you monitor the SQL memory consumption.


Perform Volume Maintenance Tasks

Last night, in my abundant free time, I was reading the course materials for "SQL Optimization for Microsoft Dynamics AX 2012" (Course 80428).   This seemingly innocuous paragraph jumped out at me.

SQL Server Service Account Configuration
When you create the SQL Server service account, make sure that it is run with the
minimum required credentials. Assign the service account the Lock pages in
memory local security policy credentials, and assign the service account the
Perform Volume Maintenance Tasks local security policy.
The Lock pages in memory policy stops the SQL Server process working set
(committed memory) from being paged out or trimmed by the operating system.
The Perform Volume Maintenance Tasks policy allows the SQL Server to
perform instant file initialization. This allows for the fast execution of file
operations. Instant file initialization reclaims the used disk space without having to
fill that space with zeros. Instead, disk content is overwritten as new data is written
to the files.  

So in addition to recommending enabling LPIM, the AX documentation also recommends enabling the Perform Volume Maintenance Tasks policy.  Here is some information about the Volume Maintenance Tasks policy.

https://msdn.microsoft.com/en-us/library/ms175935.aspx

https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/12/22/how-and-why-to-enable-instant-file-initialization/

Note this key statement in the first MSDN article:  "Log files cannot be initialized instantaneously."  The second file emphasizes this point about Instant File Initialization not being applicable to SQL Log files, which I believe is an important distinction.

So given the larger size and higher expected throughput for a Dynamics AX environment, I can understand why the Volume Maintenance option would be recommended, but what about a Dynamics GP environment?

I'm guessing that for a typical 10 user Dynamics GP implementation that does not involve high transaction volumes, the Volume Maintenance / Instant File Initialization setting is probably not going to make much of a difference, if any.  As long as the database files are set to grow in a reasonable manner (in fixed Megabytes, not by percentage), the mdf data files probably won't need to grow very often, so the benefits of Instant File Initialization will be limited.

But if you are in a very high volume Dynamics GP environment where file growth occurs on a more frequent basis, then this option might make sense.  I'm aware of a few Dynamics GP customers who are literally processing transactions in Dynamics GP 24 hours per day--millions of transactions.  In those environments, these seemingly small settings will likely have a more significant impact.


Do you use either the Lock Pages In Memory or Perform Volume Maintenance Tasks in your Dynamics GP environment?  If so, I'm curious to know if there was a specific performance issue or indicator that you saw that motivated you to enable either feature.

May your SQL Servers stay happy.

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 Google+ and Twitter

http://www.precipioservices.com