AX Application DBA

Yesterday I recieved a newsletter from SSWUG titled

Are You an application DBA?

My first reflection was that I have been and that I partly still is an application DBA for AX (and also some Axapta solutions). My second thought was that this is an interesting question, since I very often see a total lack of DBA responsibilities and naturally even less DBA's that focus on AX. When I meet someone with a DBA role, it's often in relationship to hosted solutions (outsourcing) and these guys are general DBA's focusing on "the big picture" (at best).

One example was a customer with a consolidated SQL Server solution counting almost 60 user databases supporting the same number of applications. When facing these kinds of situations, the potential for optimizing the AX database is rather small. Take tempdb as an example. AX 4.0 and later benefits from Read Committed Snapshot Isolation as a consequence of introducing versioning and optimistic locking. This again increases the load on tempdb, since the version store are held in tempdb. Since tempdb is a system database shared between every database on the same instance, the total load on tempdb again impacts AX. And I can say that different applications uses tempdb differently and often not following best practice. Mixing OLTP and OLAP load on the same instance or database server, is another classic.

This is some examples of when you need to know how the application uses SQL Server and maybe not something a general DBA would pay attention to without looking into how each application actually utilizes tempdb. Another example is locking and lock escalations in other applications impacting for instance AX. Add databases consuming a considerable amount of CPU in combination with high I/O load.

So in my oppinion it's a great need for application DBA's and AX is a good example since AX is a ERP solution (very often mission critical). I would bet that all customers running AX would see a good return on investment (ROI) hiring an application DBA for AX responsible for pro active maintenance and follow up on queries not performing at the full potential, long running queries etc. But the economic downturn probably impact the willingness of customers spending money on this and this again justifies for calling in people like my self for short term activities (fire fighting). This is a bit of a paradox to me...