Data Purging

(0) Share

Report

Posted on by Community Member

Hi All,

Is there any possibilities of data purging or data compressing from certain possible tables in AX 2012?

The reason behind this requirement is that, due to heavy volumes of transactions, we are not able to generate any of the reporting and also performance is being badly impacted.

We are taking all the required precautions for the DB optimizations however it is not helping us.

Our client is having 100+ Hypermarkets, Super Markets & Electronics outlets and transaction volume is immense. Just to give a hint, Database size is growing at 8-10 GB on a daily basis.

Regards,

Vishal Salot

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Brandon Wiese 17,788 on at

Like (0)

Report

How big is the database already?

Have you done some analysis on query performance? Which queries are slow? When was the last time you updated the statistics? Have you implemented DATAAREAID and PARTITION literals and turned off trace flag 4136?

I doubt very much you're going to solve your issues through data purging at that volume. You're going to have to tune the database performance to get the system up to par.

Was this reply helpful? Yes No
Suggested answer

Divya Lakshmi 745 on at

Like (0)

Report

Hi,

Try whether IDMF will be applicable to your scenario.

Regards,

Divya Lakshmi.J

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

I understand that, data purging may not help to an extent.

Actually, we have used the DynamicsPerf analysis tool to understand the various queries, which are taking longer duration for execution.

Just to give you a hint, certain Inventory related reporting like Inventory Ageing, Inventory Valuations, Sales related Reporting are taking longer time, as data is being continuously inserted in these tables.

We have placed all the required infrastructures and SQL setups / fine tuning as per consultation with MS. Using the faster storage, multiple partitions, multiple servers for better performance.

Already followed performance guide provided by MS. Changed all the possible set-ups in AX & SQL as well.

Also, updating statistics almost on daily basis. Scheduled re-indexing on daily basis on important tables.

Various trace flags are tuned off on SQL as per guidelines from MS. Also using the latest AX 2012 R3, CU10 application for better performance.

So as a final option, I thought of purging / compressing data from important tables might help for faster execution of reports / posting of various transactions.

Would appreciate further suggestions on the same.

Regards,

Vishal Salot

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (0)

Report

Sounds like you've made an excellent start on the problem.

Given all that data, where does the bottleneck tend to be? Locking? Transaction blocking? Deadlocks? SQL Server IO?

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (0)

Report

Have you already tried moving reporting off to a secondary SQL Server or snapshot? You mentioned you have multiple servers for performance. Was that multiple AOS, multiple SQL Servers in a cluster, multiple SSRS servers in a cluster?

AX does not yet properly support Availability Groups fully. There are some good blog posts on how to hack it to get past certain issues. Even then I don't think it properly manages its connections to let the listener assist in write/read connection routing. There's always good old fashioned log shipping to a secondary reporting server.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Main problem is deadlocks & at times locking.

We have multiple AOSs, SQL Servers in Clusters etc. Multiple SSRS servers, but not helping as expected.

Hence as you mentioned, I am thinking about moving reporting to secondary SQL server / Snapshot.

Just need to understand, if anybody has tried that before?

Can we divert AX reporting to snapshot database (mirror / read only DB) using AX client?

I mean, transaction needs to be performed on primary transactional database, however reporting should be fetched from secondary SQL database (ready only DB for reporting purpose only).

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report
I'm looking for an official support statement from Microsoft on this topology. I know it's been done, though I'm not currently doing it in my primary environment.

https://technet.microsoft.com/en-us/library/dd309577.aspx

This document mentions using a read-only replica to offload OLAP processing, which another common issue. I don't see a difference here between cubes and SSRS, in that either should work off a read-only replica.

AlwaysOn

SQL Server AlwaysOn is a capability that enables highly available, SQL Server databases. This capability has been available since SQL Server 2012. Key points to keep in mind:

Systems administrators can deploy AlwaysOn capabilities to create a robust environment for databases, including the Microsoft Dynamics AX database, as well as other staging databases associated with a Microsoft Dynamics AX implementation, such as the SSRS catalog database.

The AlwaysOn feature enables retaining a read-only replica of the primary Microsoft Dynamics AX database. This read-only replica can be used for processing cubes, thereby relieving the load on the primary Microsoft Dynamics AX database.

Secondary databases created as a result of enabling SQL Server AlwaysOn cannot be used to scale-out Reporting Services. To scale-out your Microsoft Dynamics AX SQL Server Reporting Services deployments see the information here.

Was this reply helpful? Yes No