I have been tasked with determining what DynamicsPerf offers to help keep DAX (Dynamics AX 2012) running optimally and put together a plan for how we should be leveraging on a continual basis. It should be noted we will likely move to D365 in the next year or two, but in the meantime we need to keep AX 2012 running smoothly. DynamicsPerf was installed on our AX Production server almost 10 years ago but has never been fully utilized. I have been reviewing the database and running some of the out-of-the-box scripts to get a feel for what it can do. One such script is intended to find Dynamics queries that are scanning. Here is the script:
SELECT TOP 100 *
FROM USER_SCANS_CURR_VW
ORDER BY TOTAL_ELAPSED_TIME DESC
The only issue is it takes a very long time to run. Actually, I have not waited long enough to even see if complete - 30 minutes is the longest I've let it keep running. My questions are:
1. Being there is data in the DynamicsPerf database going back to 2014, guessing the tables the view is joining contain a lot of data. Wondering if there is a prebuilt script to truncate all the tables, essentially getting the DynamicsPerf database back to day 1? Or do I just reinstall DynamicsPerf on another machine and point it to our AX production environment?
2. The DynamicsPerf database is 60GB. Not really huge IMHO - is clearing the data overthinking this, or do I just need to be patient with this query as maybe it's common for it to take a long time to complete?
Thanks,
Scott
