This is a question that I get asked a lot, and it’s a very natural one to ask. The short answer to this is almost nothing. We are simply collecting DMV data that SQL Sever already has in memory and inserting that into our DynamicsPerf database. For Dynamics AX customers, we are collecting some additional configuration information from the Dynamics AX database and inserting that into DynamicsPerf. On average, the first data capture of the daily capturestats job runs in 3-5 minutes with subsequent captures taking 1-2 minutes. The hourly performance capture job normally runs in about 5-6 seconds.
Now for a few details of what we call the DYNPERF_capturestats job. This job by default runs once per day or if you are following the Analysis Flowchart diagram every time you have a performance issue. This job is what reads the SQL Server DMV data and the Dynamics AX configuration data. It simply inserts both sets of data into the DynamicsPerf database. But, there is some usage of TempDb that I have to do in order to handle keeping only 1 copy of a query plan. You might ask, “But isn’t there only 1 copy of a query plan in cache?”. Good question. Actually, SQL Server internally keeps sys.dm_exec_query_stats and sys.dm_exec_plan_cache by SQL Handle and Plan Handle. We actually aggregate this data by QUERY_HASH and QUERY_PLAN_HASH in the DynamicsPerf database to simplify analysis. What this means is that we have multiple records in the sys.dm_exec_query_plan that I need to do a DISTINCT on so we only get 1 record. But, you can’t do a DISTINCT on a select clause that has blob data in this case the XML for the query plan. Thus, we use TempDb to build up a distinct list of QUERY_PLAN_HASH and then add the actual XML to that list when we insert into DynamicsPerf. So, if you see TempDb usage during data collection this is why.
So, why does the first data collection take longer? There are 2 reasons for this. One impacts everyone and one impacts only Dynamics AX. We actually collect database statistics by running DBCC SHOW_STATISTICS. This is done if statistics have never been collected.It is also done weekly on Sundays. The capturestats goes through every index on every table running this DBCC command, so it adds some time to the data collection when it runs. Since, we don’t need statistics updated frequently in DynamicsPerf data we only collect it on Sundays. For Dynamics AX customers, if you have enabled the long running trace functionality in Dynamics AX, which populates the SYSTRACETABLESQL table, capturestats collects this data. This can take a long time to collect when you first install DynamicsPerf and do your first data capture. We currently limit this collection to data from the last 2 weeks. After the first collection, it will only look for new records since you last collected data.
For Dynamics AX customers, there are 2 other parts to data collection that are external to SQL Server. There is an AOTEXPORT class that you import into your environment. The XPO that is part of the installation adds this class and 3 tables, AOTTABLEPROPERTIES, AOTFIELDPROPERTIES, and AOTINDEXPROPERTIES to your Dynamics AX database. This class should be run any time you update any Table properties in Dynamics AX. This class can be run as a batch job or you can just run it as part of your code deployment process. This process is what lets us see important table properties such as OCC and Cache type in the DynamicsPerf database. Those properties are stored as a blob field which we cannot parse in TSQL which is why we have the Dynamics AX class to normalize it into tables.
The next item for Dynamics AX customers is the data from AOSANALYSIS.CMD. This is a VBScript that is used to get the AOS Server Configuration registry settings from all of your AOS servers. There are some very important settings that impact transaction volumes on an AOS server that we look at in DynamicsPerf. For example, did you know that having X++ debug enabled on your AOS Server will cause it to process 20% less transactions? These are the types of items we are collecting in DynamicsPerf. You should run the AOSANALYSIS.CMD whenever you deploy a new AOS or make changes to your AOS Server configurations. It’s possible to use Windows Scheduler to run this once a week to automate it.
THE BIGGEST IMPACT !!!
There is one more area of impact that DynamicsPerf has on your deployment. It’s the most important reason for deploying DynamicsPerf in the first place.
It will help you solve Dynamics performance issues QUICKER !!!
For other articles in this series please go here:
Rod “Hotrod” Hansen
Sr. Premier Field Engineer Dynamics AX