AX and SQL Server - DBCC Freeproccache?

(0) Share

Report

Posted on by Community Member

Hi guys,

i asked this question already here but received no answer.

We are running AX 4 (Kernel version 4.0.2503.551) and SQL Server 2008 R2 (SP2 CU 10).

Every morning (when the user starts working) and several time a day (changing) the AX System "hangs" or is running very slow. Clearing the cache (running DBCC Freeproccache) "resolves" the problem.

In the past i found an article which says "This is a bug between AX and SQL Server". Is there a KB Article or something which describes the problem?

And: We are migrating to AX 2012 with SQL Server 2012 and it seems the bug also exists? Is this true?

Thanks in advance!
Frank

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Community Member on at

Like (1)

Report

RE: AX and SQL Server - DBCC Freeproccache?

Hi all,
in the past i fixed the maintanence plans but never tried again to enable parmeter sniffing (AX 4). After all i tried it again - now that works for the performance but there were side affects so we had to disable it.

Our test in AX 2012 using the parameters (blogs.msdn.microsoft.com/.../sql-server-parameter-sniffing-with-dynamics-ax-just-plain-evil-redux-5-19-2014) seems good. So in AX 2012 things will hopefully get better :)

Thanks,
Frank

Was this reply helpful? Yes No
Mariano Gracia on at

Like (1)

Report

RE: AX and SQL Server - DBCC Freeproccache?

We have a customer with ax 4.0 and we did the same, every morning and several times a day we did run DBCC Freeproccache command, it wasn't a bug, it was just poor performance due to a lot of factors, bad coding, bad indexes maintenance, not using cluster index in our own tables, not creating a single tempdb file for each core in our database server... After an audit with Microsoft enginier (and a lot of changes) we did solve our problem. With AX2012 you will face the same problem if you don't fix it in AX 4.0, just becase I'm pretty sure your problem is in your code and in your installation (be sure your tempdb, log file and data file are separated in differents physical hdds). But it isn't easy to find the origin of poor performance, in AX 2012 you have DynamicsPerf which is an outstanding tool to check what is happening in sql while you're working with Dynamics and you can analyze a lot of thinks like missing cluster index, not used indexes, bad queries...

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: AX and SQL Server - DBCC Freeproccache?

Hi Vilmos,

we do regular maintenance of our index and statistics.

The articles seems interesting - i will give it a try :).

Btw.: We are facing the problem even if nearly nobody is working and Batch Requests are low.

Frank

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: AX and SQL Server - DBCC Freeproccache?

Hi Denis,

i know the article and in the past we try to enable the sniffing but it had no effect. Sad but true ;)

Frank

Was this reply helpful? Yes No
Verified answer

Denis Macchinetti 16,444 on at

Like (0)

Report

RE: AX and SQL Server - DBCC Freeproccache?

Hi Frank

Yes, as per rmy experience, this is an issue that I faced sometimes with Dynamics AX, especially during Data Migration where the Database was empty or more companies with different sizing.

From my side, I have partially solved the issue enabling the sniffing.

Check the link below

blogs.msdn.microsoft.com/.../sql-server-parameter-sniffing-with-dynamics-ax-just-plain-evil-redux-5-19-2014

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (1)

Report

RE: AX and SQL Server - DBCC Freeproccache?

Hi

First of all, make sure you are doing regular indexing and statistics maintenance for the table involved in the slowness.

Next step is to check the Execution plan of the SQL statement that is running when it is slow, and when it is performing fast. If you see a difference, then it is either that SQL is unable to compute the fastest query plan within a reasonable time (Query plan timeout), or it has decided to try a different query plan due to the changing statistics.

If the running SQL statement is not using a temp table, then you can create an SQL Plan guide to cover the SQL statement with the correct plan, instead of throwing out all plans with freeproccache.

We have been facing similar issues when posting purchase and sales transactions in large volumes, and the solution was creating a Plan guide for them. You may find some resources here:

www.daxrunbase.com/.../improve-ax-performace-by-fixing-bad-query-plans

technet.microsoft.com/.../bb895390%28v=sql.105%29.aspx

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: AX and SQL Server - DBCC Freeproccache?

Hi Andre,

thanks for answering. I am pretty sure, this is a bug - because in the past i found an article about that (with reference to a Knowledgebase article) ) :). I only was looking for this (kb-)article. Maybe one day there is a hotfix or something else.

Frank

Was this reply helpful? Yes No
André Arnaud de Cal... 297,231 Super User 2025 Season 2 on at

Like (0)

Report

RE: AX and SQL Server - DBCC Freeproccache?

Hi Frank,

It is not possible to say if this is related to a bug or not. Hanging might be caused by caching data or objects. If there are problems you should monitor what is really going on. Is it caused by hardware, missing indexes, slow storage? This is not possible to tell you on a forum.

Was this reply helpful? Yes No