Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

DynamicsPerf Slow Query

Posted on by 14
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
Categories:
  • ScottG17 Profile Picture
    ScottG17 14 on at
    DynamicsPerf Slow Query
    Thanks, Martin for your recommendations.  I will go ahead with renaming the DB, as you suggested, and create a new install.  
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 225,513 Super User on at
    DynamicsPerf Slow Query
    I don't remember details of DynamicsPerf very well, but here are at least some thoughts...
     
    I think a good idea might be renaming the current DynamicsPerf database and keep it around if you need some trend analysis or so. Then re-create DynamicsPerf DB (with 1-Create_Core_Objects.sql) and start collecting fresh data. After a few days, you'll have some data to work with, while the DB will be quite small.
     
    I don't think that looking at queries that used were problematic ten years ago is very relevant for your current system, therefore you might want to delete some data or add filters to view definitions if you want to work with the old DB at all. And of course, it may be worth looking at missing indexes, fragmentation and such things. You may want to add extra indexes on the archive DB without a fear of slowing down inserts (because that will happen just in the active DynamicsPerf DB).

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,773 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,513 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans