Dynamics AX 2009 - performance tuning - parameter sniffing and fill factor

Question

I am currently performance tuning a Microsoft Dynamics AX 2009 fully upgraded to the latest kernel / hotfix.

During this process I have come across database fill factor set to 80. Not sure why? I have altered it to 95 now - not daring the to make the final move to 100 just yet. Any thought on this?

Now the question I came here for:

What flags would be recommended on the SQL Server to support the Dynamics AX 2009?

As mentioned it is fully upgraded and its setup to call parameterized but with the DataAreaId as a literal in order to make a dedicated plan for each company.

During the past 10 years it has been performance tuned a few times. These flags are currently set: 1117, 1118, 1224, 2371, 4136, 4199, 7646

I would like to remove the 4136

Answer 1

A bit about myself

Database performance tuning for AX in my experience requires the intersection of two expertises (AX and SQL Server) that rarely exist in one person. I would consider myself on the AX expert side of things and have enough SQL Server knowledge to be dangerous (or to get by). So be aware of this when reading the rest of this answer.

General observations

First, two general observations:

• AX performance issues rarley come down to database performance issues. First check should always be in the application where the bottleneck is. 9 times out of 10, it is an issue with application, data or layer 8, but not the database.
• If you do SQL Server performance optimization, you should know what you are doing. It is far easier to make things worse than it is to make them better. You should have a good performance monitoring in place that tells you how things have changed after you changed a setting. Changing a setting just because it seems a good idea is not a good idea.

Your questions

Now, in your question you mention two separate points:

• index fill factor: I wouldn't change this setting without having a good reason to do so. Many tables in AX have a large number of rows and experience frequent updates. Increasing the fill factor would degrade performance for these tables. To quote from SQL Server Index Fill factor with a Performance Benchmark:

In this example we have used 80% Fill Factor, however it doesn’t make sense to push without any benchmarking for the table. In most cases SQL Server index Fill factor will help to get well performed when Table having large number of rows and frequent update over the rows. Before setting the Fill Factor we need to analyse the Datatype of columns, actual cell size of the rows, Average number of rows in the pages and estimated updated size of row cell. This proper calculation derives an actual Fill factor value which need to be applied on the table.

• removing flag 4136 (which disables parameter sniffing): I would remove this flag. As far as I know, this is not set in a standard installation of AX 2009. It was probably set with good intentions and because some blog articles on the internet mention it to improve performance. I suggest to read the following two links with some information on this flag and what should be used instead:
  • SQL Server Parameter Sniffing with Dynamics AX, just plain evil
  • SQL Trace Flag for Dynamics Ax: Do we need it?

More information

If you want to go further down the rabbit hole of performance optimization for AX, I suggest the following articles. They are mostly for AX 2012, but parts should apply for AX 2009, too and provide a starting point:

• AX Performance Troubleshooting Checklist Part 1A [Introduction and SQL Configuration]
• AX Performance Troubleshooting Checklist Part 1B [Application and AOS Configuration]
• AX Performance Troubleshooting Checklist Part 2
• AX Performance – Checking key SQL Server configuration and database settings

A bit of meta

Your question is in a very gray area of being on topic for Stack Overflow. You may have better luck on serverfault or Database Administrators.