SQL Server performance with Dynamics Axapta

The last couple of years I have spent a lot of time of and on tuning SQL Server performance in combination with Dynamics Axapta. There are some good blogs on this topic out on the internet, such as this one http://blogs.msdn.com/b/axinthefield/archive/2010/11/04/sql-server-trace-flags-for-dynamics-ax.aspx and this one http://sqlcat.com/sqlcat/b/top10lists/archive/2010/05/20/top-tips-for-maximizing-the-performance-amp-scalability-of-dynamics-ax-2009-systems-on-sql-server-2008.aspx for example. In this short blog I will summarize a litte bit, and also add my own thoughts and findings on some subjects. Please remember to always test these suggestions first, and that the tips might pre-req good knowledge in SQL Server to make sense in some cases! 

Trace Flags

You should consider using the following trace flags in SQL Server in conjunction with Axapta: 1117, 1118, 4136 and 845 (On SQL Standard edition)

Indexes

You will need more than the standard indexes. And thy must be created from within AX. However AX does not generally support all SQL Server index options. The trick then is to CREATE the index from AX, then ALTER it from SQL to suit your needs. Rebuild your indexes when needed. Ola Hallngrens scripts at http://ola.hallengren.com are a very good place to start

Statistics

Some tables might need their statistics to be updated several times a day. If you experience declining performance, worse the longer time runs from your UPDATE STATISTICS job, you might have this problem. In addition, some tables needs to have their statistics updated WITH SAMPLE 40 PERCENT or higher Leave AUTO CREATE STATS and AUTO UPDATE STATS on on your AX database

Query Plans

Beware of problems with OPTION (FAST 1) wich exists in several places. Use Plan Guides to control this from the SQL side if needed. But the best is if you can get the AX developers to fix it on the application side. Parametersniffing is a big issue, especially if you have several companies in the same database, with uneven amounts of data. Trace Flag 4136 can help.

Other

Set Max Degree Of Parallellism to 1 Configure Tempdb to use the same number of files as the number of cores in your SQL Server machine. Set the files to the same size, and settings Make sure you limit SQL Server memory usage, so that enough memory is left for Windows, Monitoring, Backups etc. If you run clustered SQL Server, consider using SQL Server 2012, and place Tempdb on local SSD disks, as AX tends to use Tempdb a lot Beware of blocking that can go on for a long time. You might need block monitoring. Orphaned sessions can happen, even if you kill a session in AX, it might not kill the corresponding SPID in SQL Server. Also see blocking above.

And of course, you can always contact us if you need further help!