Tag Archives: performance

Checking AX2009 ‘EntireTable’ Caching Setup

We are going to look at how you can review your ‘EntireTable’ Cache settings on tables within the AOT and why performance can be improved if your settings are incorrect.

When a table’s Cache Lookup in the AOT is set to ‘EntireTable’ it means a maximum of 2,000 records are loaded into the AOS servers cache for that table which saves having to go back to the SQL Server Database each time a query on that table is performed.

Each time a record is Inserted / Updated / Deleted the cache on the AOS has to be dropped and reloaded for that particular table, if you have a large table that is set to ‘EntireTable’ type caching and the table is being changed often this can cause detrimental effects on performance for your users as the table is having to be dropped from the cache and reloaded often and the AOS may have to go to back to the database anyway if the record isn’t in the cache.

We can check quite easily to see what tables are set in the AOT to ‘EntireTable’ caching and their associated row count in the database using the scripts below, I’ve provided two scripts one for real-time analysis and one which can be used with DynamicsPerf if you have this setup and configured for your environment:

Real-Time Analysis:

/* AX EntireTable Caching Row Counts (Real Time).sql
-- D.Coupland 24/03/13
-- Find rows counts of tables with cache lookup set to 'EntireCache' 
-- Investigate any row counts over 2,000+ to ensure they are not affecting performance
*/

USE <MyDax09Database>

SELECT  
AOT.TABLENAME
,APPLICATION_LAYER = CASE
                        WHEN AOT.TABID BETWEEN 1 AND 7999 THEN 'SYS'
                        WHEN AOT.TABID BETWEEN 8001 AND 15999 THEN 'GLS'
                        WHEN AOT.TABID BETWEEN 16001 AND 17999 THEN 'DIS'
                        WHEN AOT.TABID BETWEEN 18001 AND 19999 THEN 'LOS'
                        WHEN AOT.TABID BETWEEN 20001 AND 29999 THEN 'BUS'
                        WHEN AOT.TABID BETWEEN 30001 AND 39999 THEN 'VAR'
                        WHEN AOT.TABID BETWEEN 40001 AND 49999 THEN 'CUS'
                        WHEN AOT.TABID BETWEEN 50001 AND 59999 THEN 'USR'
                        WHEN AOT.TABID >= 65000 THEN 'System Table'
                        ELSE Ltrim(Str(AOT.TABID))
                     END
,ST.ROW_COUNT
FROM 
AOTTABLEPROPERTIES AOT
	INNER JOIN 
SYS.DM_DB_PARTITION_STATS ST
	ON OBJECT_NAME(ST.OBJECT_ID) = AOT.TableName
WHERE
ST.INDEX_ID < 2
AND AOT.CACHELOOKUP = 4 -- EntireTable Cache Enabled
AND ST.ROW_COUNT > 0 -- Ignore Tables with no rows
ORDER BY ST.ROW_COUNT DESC

Using DynamicsPerf DB

/* AX EntireTable Caching Row Counts (Using DynamicsPerf DB).sql
-- D.Coupland 24/03/13
-- Find rows counts of tables with cache lookup set to 'EntireCache' 
-- Investigate any row counts over 2,000+ to ensure they are not affecting performance
*/

USE <MyDynamicsPerfDB>

SELECT DISTINCT 
 TD.TABLE_NAME
,TD.APPLICATION_LAYER
,TD.CACHE_LOOKUP
,ISC.ROW_COUNT
FROM 
AX_TABLE_DETAIL_CURR_VW TD 
	INNER JOIN 
INDEX_STATS_CURR_VW ISC 
	ON TD.TABLE_NAME = ISC.TABLE_NAME
WHERE
ISC.ROW_COUNT > 0  AND -- Ignore Tables with no rows
CACHE_LOOKUP = 'EntireTable' 
ORDER BY ISC.ROW_COUNT DESC

Download the scripts here: AX EntireTable Caching Row Counts (906 downloads)

I recommend you review any tables over 2,000+ rows in your ‘EntireTable’ caching strategy regularly ensuring you test any changes prior to making the changes in your Production Environment.

For further reading on AX 2009 Caching please take a look at the following articles:

http://msdn.microsoft.com/en-us/library/bb314693(v=ax.50).aspx

http://dynamicsuser.net/forums/p/30043/157813.aspx

New Version of sp_Blitz v14 Released – Adds VLFs, DBCC, Failsafe Operators, and More

Dynamics AX 2009 Tracing on Application Object Servers the easy way

Leave a reply

Troubleshooting AX 2009 Performance Issues can be sometimes frustrating when you’ve got a lot of users using the system and you want to trace one particular users session.

One way that can ease this is to log the user onto an AOS where the service account it runs under is different to your other AOS’s and where they are the only user on the AOS. This means you can trace only their session using the “Microsoft Dynamics AX 2009 Server Configuration” application along with SQL Server Profiler (In Profiler you can specify the service account to be traced).

In order to set this up there are two different options, option 1 is the preferred option.

Option 1: Install a new AOS on an existing server that has the capacity or a new server – you can allocate specific CPU’s for each AOS Service on your server within the “Microsoft Dynamics AX 2009 Server Configuration” application, I will cover this in another article at a later date.

Option 2: Use an existing AOS Service

Option 1: New AOS Service

1. Create a new Active Directory Login

e.g. CONTOSO\PerfMonAosSvc

2. Add user to ‘Performance Log Users’ group

Performance Log Users Group This is required so AOS Tracing can be started via the Tracing tab found within the
“Microsoft Dynamics AX 2009 Server Configuration” application.

3. Install a new AOS specifying the new Service Account login details when prompted, specify not to start service after the installation is completed, this ensures you can install Service Packs and Hotfixes on the new AOS service.

4. Ensure you install any Service Packs or Hotfixes to your current environments level. During each install specify not to start service after the installation is complete and ensure you only select to update the AOS.

5. You may need to add ‘EXECUTE‘ permissions on any additional custom Stored Procedures that you may have in your AX Database for your new account.

6. Start the new AOS Service and check the application event log to ensure no warnings/errors are reported.

Option 2: Changing an Existing AOS Service

If you already have an existing AOS Service you want to configure for Performance Monitoring there are different steps to complete:

1. Create a new Active Directory Login

e.g. CONTOSO\PerfMonAosSvc

2. Create a SQL Server Login

USE [master]
CREATE LOGIN [CONTOSO\PerfMonAosSvc] 
FROM WINDOWS 
WITH DEFAULT_DATABASE=[MY_DAX09_DB]

3. Create the Database Login

USE [MY_DAX09_DB]
CREATE USER [CONTOSO\PerfMonAosSvc] 
FOR LOGIN [CONTOSO\PerfMonAosSvc]

4. Grant Database Role Permissions

USE [MY_DAX09_DB]
EXEC sp_addrolemember N'db_ddladmin', N'CONTOSO\PerfMonAosSvc'
EXEC sp_addrolemember N'db_datawriter', N'CONTOSO\PerfMonAosSvc'
EXEC sp_addrolemember N'db_datareader', N'CONTOSO\PerfMonAosSvc'

5. Grant Stored Procedure Permissions

USE [MY_DAX09_DB]
GRANT EXECUTE ON [dbo].[CREATESERVERSESSIONS] 
TO [CONTOSO\PerfMonAosSvc]
GRANT EXECUTE ON [dbo].[CREATEUSERSESSIONS] 
TO [CONTOSO\PerfMonAosSvc]

Note: You may need to add ‘EXECUTE’ permissions on any additional custom Stored Procedures that you may have in your AX Database.

6. Add user to ‘Performance Log Users’ group
Performance Log Users Group

This is required so AOS Tracing can be started via the Tracing tab found within the “Microsoft Dynamics AX 2009 Server Configuration” application.

7. Ensure the Service Account has Modify, Read & Execute, List folder contents, Read, Write permissions on the Application Folder

e.g. C:\Program Files\Microsoft Dynamics AX\50\Application

8. Ensure the Service Account has Read, Write permissions on the AOS Log folder

e.g. C:\Program Files\Microsoft Dynamics AX\50\Server\MY_NEW_AOS_02\Log

9. Change the AOS service account user in Services to the newly created service account

10. Restart the AOS Service.

Please as always try this setup in your Test / Dev environment prior to implementing it in your Production environment to ensure you understand the setup required and that it fits your requirements.

Additional Reading:

A great 3 part series on AX Tracing from the ‘Premier Field Engineering’ Team
http://blogs.msdn.com/b/axinthefield/archive/2010/12/28/dynamics-ax-tracing-part-1.aspx
http://blogs.msdn.com/b/axinthefield/archive/2011/03/25/dynamics-ax-tracing-part-2.aspx
http://blogs.msdn.com/b/axinthefield/archive/2011/06/25/dynamics-ax-tracing-part-3.aspx

Run a Dynamics AX SQL statement as an API Cursor in SSMS

1 Reply

We all know that AX2009 uses cursors extensively – simply executing a SQL statement in SSMS doesn’t always result in the same query plan being used that an AX generated SQL statement would use.

Want to run a query in SQL Management Studio exactly how Dynamics AX 2009 runs the query e.g. as a API Cursor so you can ensure you are looking at the same query plan AX has used then run the query below.

Simply replace the red text below with all the query variables and replace the blue text with your parameters and execute the full statement and voila you can view the exact query plan AX uses to execute the statement.

TIP: If your unsure of the variables to declare in the cursor you can find them by doing a SQL Profiler Trace.

———————————————-
— Normal FFO Cursor
———————————————-

— Script taken from AX Dynamics Performance Analyzer scripts by Rod Hansen (http://code.msdn.com/dynamicsperf)

declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
—
— Fast Forward(16)+Parameterized(4096)+AutoFetch(8192)+AutoClose(16384)
—
set @p5=16+4096+8192+16384
declare @p6 int
set @p6=8193
declare @p7 int
—
— Number of Rows for AutoFetch.
— This is calculated by Maximum Buffer Size (24K default) / Row Length
—
set @p7=4
exec sp_cursorprepexec @p1 output,@p2 output,N’@P1 nvarchar(5),@P2 nvarchar(21)‘,N’SELECT A.SALESID,A.RECID FROM SALESLINE A WHERE ((DATAAREAID=@P1) AND (SALESID>@P2))’,@p5 output,@p6 output,@p7 output,N’ceu’,N’SO-100004′
— @p2 contains cursor handle for fetch call
exec sp_cursorfetch @p2,2,1,@p7