search

PFEDynamics / DynamicsPerf

DynamicsPerf is a performance analysis tool for Microsoft Dynamics AX
Microsoft Public License
75 stars 20 forks source link

DynamicsPerf Database Configuration #6

Open patrickjolliffe opened 5 years ago

patrickjolliffe commented 5 years ago

Forgive me if this is not the best place to post this question, but we've recently inherited a Dynamics AX Database on which DynamicsPerf has been installed and DynamicsPerf seems to be causing majority of the problems we are seeing on the database. Just to give you an idea of what we are seeing, sp_capturestats job that is running every 5 minutes is taking average 20 minutes to complete.
One thing we have started looking at is the "Allow Snapshot Isolation" and "Is Read Committed Snapshot On", these setting are "true" both Dynamics AX database and DynamicsPerf. I guess DynamicsAX is out of scope for this repository, but is there a recommendations for these settings on DynamicsPerf database?

Alexander-Weurding commented 5 years ago

Hey Hello [name]

Thanks for reaching out and never a problem. I would only use DynamicPerf in troubleshooting times, and disable it afterwords. Its a great tool but costing to much to run always.

I'm curious how the SQL is running do you have a screenshot like this?

[image: image.png]

Best regards Alexander

On Wed, Feb 27, 2019 at 9:35 AM pjolliffe notifications@github.com wrote:

Forgive me if this is not the best place to post this question, but we've recently inherited a Dynamics AX Database on which DynamicsPerf has been installed and DynamicsPerf seems to be causing majority of the problems we are seeing on the database. Just to give you an idea of what we are seeing, sp_capturestats job that is running every 5 minutes is taking average 20 minutes to complete. One thing we have started looking

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/PFEDynamics/DynamicsPerf/issues/6, or mute the thread https://github.com/notifications/unsubscribe-auth/AFrlpCdQ179unLyA9_2FbjWJYzmnyvgXks5vRkNbgaJpZM4bT-fW .

-- Groeten Alexander

rayzorben commented 5 years ago

@pjolliffe Allow Snapshot Isolation should be set to TRUE. It is primarily not used by Dynamics AX - to use this you have to use SET TRANSACTION ISOLATION LEVEL on each query you execute, Dynamics AX does not do this. However some 3rd party tools do and so it should be left enabled.

Read Committed Snapshot Isolation absolutely should be on/true. When a row/page is locked, a version of it is stored in tempdb to allow readers to read a version of the data before it was changed. AX relies on never reading uncommitted data. This functionality also allows it so that writers never block readers. Disabling this option can cause significant blocking on your AX implementation.