axinthefield: How to accurately predict database growth in Dynamics AX

[Blog bot]

Источник: http://blogs.msdn.com/b/axinthefield...namics-ax.aspx
==============

One of the biggest challenges the Dynamics AX database, is predicting database growth. Many customers are simply using the size of the backups to judge database growth. Although this process works, there are some flaws in doing this such as Transaction Log size is included in that backup. For example, if you imported 1GB of new data into the database and then did a backup, the backup would have grown 2GB in size. This would be 1GB for the data and 1GB for the log data. So using the backup size method would tell us the database grew 2GB when it actually only grew 1GB.

One of the great abilities of Performance Analyzer for Dynamics is collecting data at different points in time and then doing a delta difference to determine a value such as growth in rows or growth in size over that timeframe. One of the tables in the DYNAMICSPERF database is called INDEX_STATS. In this table, we have the row_count and size of all of your Dynamics AX tables. To get an accurate an accurate growth rate over a period of time, we simply need to capture data at 2 different points such as Monday morning and Friday evening. This would give us the growth rate for an entire week.

Since Nov. 2010, Performance Analyzer 1.0 has had a function in the database called fn_dbstats. This procedure does the calculation of database growth per table and requires 2 parameters. The 2 parameters are simply the RUN_NAME of the 2 data captures. You can find this by running the following query in DYANMICSPERF:

SELECT RUN_NAME FROM STATS_COLLECTION_SUMMARY

Choose 2 RUN_NAMEs that are a fixed workload such as 1 day, 1 week, etc. Once you have selected the values you can run the following SQL statement:

SELECT * FROM fn_dbstats('', 'RUN_NAME2>')

The results will look as follows:

TABLE_NAME

Original_PageCount

New_PageCount

Original_SIZEMB

New_SIZEMB

Delta_SIZEMB

Table_Updates

Delta_In_Rows

Days

LEDGERTRANS

13375

13730

104

107

3

8761

8635

1

LEDGERBALANCESDIMTRANS

4364

4526

34

35

1

1477

7141

1

LEDGERBALANCESTRANS

3132

3248

24

25

1

1477

6138

1

LEDGERJOURNALTRANS

1258

1274

9

9

0

450

159

1

Now you can accuratly, predicte database growth based upon actual sizes collected from every table. Another important feature of this data is that this is your REAL transaction volumes in each and every table in your Dynamics AX database for that time period.




Источник: http://blogs.msdn.com/b/axinthefield...namics-ax.aspx