Tag Archives: t-sql

Checking AX2009 ‘EntireTable’ Caching Setup

1 Reply

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 (1039 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

Updating an AX users SID when they’ve been accidently deleted from Active Directory

Leave a reply

If you ever find an AX user’s account has been accidently deleted from Active Directory then is recreated with the same user name but they cannot access AX this is because they now have a new Active Directory SID that doesn’t match the SID against their user in AX.

This can be resolved by updating their SID in AX with the newly recreated user’s SID.

 

The Problem:

“User1” was deleted in Active Directory, it was established that “User1” shouldn’t have been deleted, so “User1” was recreated.  Unfortunately “User1” couldn’t access AX though, they were receiving an Access Denied message.

Resolution:

First of all run the following powershell script (replace “User1” with your user) to find their SID

$AdObj = New-Object System.Security.Principal.NTAccount('User1')
$strSID = $AdObj.Translate([System.Security.Principal.SecurityIdentifier])
$strSID.Value

 

As you can see their SID is returned as S-1-5-21-1214440339-1788223648-682003330-48397

 

Using the SID returned by the Powershell script run the following SQL Script (replacing the  Database Name, SID and ID with yours) to update the newly created login’s SID

UPDATE [MY_DAX09_DATABASE].[dbo].[USERINFO]
SET SID = 'S-1-5-21-1214440339-1788223648-682003330-48397'
WHERE ID = 'User1'

Note: ID is not usually the same as the users AD login name, in this example it happens to be.  Doing a simple select statement on the [UserInfo] table will establish the users ID that you need to use in the above SQL Statement.

“User1” will now be able to access AX again.

Get notified when Admin or All user group permissions are granted to users in AX 2009

Leave a reply

Want to know instantly when a user has been granted ‘Admin’ or ‘All’  user rights in your Production or Non-Production environments?

Of course you do!  Implementing the script available to download below gives you control over your AX Environment with regards to knowing when permissions could have been granted incorrectly.

The script will create a trigger on the [USERGROUPLIST] table, if the user group ‘Admin’ or ‘All’ is added to a users profile then an email notification will be sent to your AX Administrators.

Follow the instructions within the SQL Script on what needs to be changed to configure it in your environment.  As always please setup and test in your Dev/Test Environment to ensure you understand the setup and that it works as intended in your environment.

I also always advise sending the email notifications to a distribution email group as opposed to individually named email addresses as it means you only need to change the distribution email group for Administrator leavers/starters as opposed to having to change numerous pieces of code/jobs/notifications.

Download Admin User Group Check Setup (661 downloads)

AX 2009 Batch Job Created Notifications

Leave a reply

Want to know instantly by email when users are creating batch jobs in AX 2009.  This saves you having to constantly go and check the Batch Job form and work out what’s new.

I have created a script which can be downloaded below which will create a SQL DML insert trigger on your database, so when a user submits a process to batch you can receive an email informing you of the submitter and name of job they have submitted.

There’s a couple of pre-requisites that need to be adhered to:

1.  Database Mail must be configured and working in your environment
2.  You need to know the following details:

  • Your Database Server and Instance Name (Instance Name only applicable if you are using named instances)
  • Your Production Database Name
  • Your Database Mail Profile name
  • Email Recipients Address (always best to have a generic distribution group email address)

With these details you can perform a find and replace on the defaults values in the script before running the script against your AX Database, full details are provided in the script itself on how to set it up.

Download  AX Batch Jobs Added Script (577 downloads)

DISCLAIMER: As always please try setting this up first in your test environment to ensure you understand the setup and whether it provides the functionality you require.

Finding which Operators are setup against SQL Agent Job Notifications

Leave a reply

As a minimum every SQL Agent Job should have an operator (or DBA) notification setup ideally for failure or completion.

Agent Job Properties

How do I check which operators are setup against my Agent Jobs I hear you say!

Well run this query below to find out.  If you have any agent jobs return as ‘No email notification configured’ in the [Notify_level_email] field then an email notification is not setup and you should look into these as a priority (unless you have other methods of monitoring Agent Jobs that is!)

SELECT SJ.NAME, SO.[name],SO.[email_address],
[notify_level_email] =
CASE notify_level_email
     WHEN '1' THEN 'When the job succeeds'
     WHEN '2' THEN 'When the job fails'
     WHEN '3' THEN 'When the job completes'
     ELSE 'No email notification configured'
END
FROM
msdb.dbo.sysjobs SJ LEFT JOIN [msdb].[dbo].[sysoperators] SO 
ON SJ.notify_email_operator_id = SO.ID
ORDER BY SO.ID, SJ.NAME

Download  Finding-which-Operators-are-setup-against-SQL-Agent-Job-Notifications (390 downloads)

The example below shows the operators setup against Agent Jobs and the email address the notification will be sent to, the first row shows that no operator is setup on this job for email notifications, if this job fails no-one may be aware.

Agent Job Notifications

 

There’s a few pre-requisites that your need to ensure are setup first for Agent Email Notifications to work:
1.  Database Mail should be configured and working (see this great article on SQL Server Central if you don’t know how to setup Database Mail)
2.  Agent Operators should be setup with valid email addresses (see my post on recommended operators to setup)  
3.  Agent Jobs should have an email notification configured ideally for ‘When jobs the fails’

Modifying Batch Job Statuses in a Non-Production Environment

Leave a reply

There’s a few things you can do when undertaking your data refresh into a non-production environment one of them is modifying your batch jobs to ‘Withhold’ status ensuring they won’t start to run when you after you start up your AOS’s and then configure your batch server settings. After the script has been ran you can set the status of jobs you want to run back to ‘Waiting’ status at a later point during the data refresh process, happy in the knowledge that you know no batch jobs are going to start emailing customers or update/talk to other external systems.

SQL Script:

-- Change to correct DB
 USE <Your Ax Database>
-- Set all Batch Jobs to Withhold status
 UPDATE BatchJob
 SET status = 0

You could also have a scenario where only certain batch jobs should be set to Withhold status, you could do this by adding the batch jobs you want to continue to run into a table and then only update the ones not in that table to Withhold. An example script to do this can be found below:

Pre-Requisites: Create a table with one data field called ‘Caption’, populate table with batch job names that you want to continue to run in your non-production environment (e.g. not be at withhold status)

SQL Script:

-- Change to correct DB
USE <Your Ax Database>

UPDATE BatchJob
SET status = 0
WHERE
Caption NOT IN (SELECT Caption FROM <the table name created in pre-requisites>)

Disclaimer: Ensure any batch jobs you add to this table don’t have other parameters that need to be changed prior to them running again in the non-production environment.

Removing Email Addresses from Non-Production Environments

Leave a reply

A few simple scripts to run when building a 2009 non-production environment from your 2009 production database to remove email addresses from the ‘CustTable’, ‘VendTable’ and ‘Address’ table this will ensure no emails can be sent in error to your customers or vendors from your non-production environment.

-- Change to correct DB
USE <Your Ax Database>

UPDATE Address
SET EMAIL = ''
WHERE
EMAIL != ''

UPDATE VendTable
SET EMAIL = ''
WHERE
EMAIL != ''

UPDATE CustTable
SET EMAIL = ''
WHERE
EMAIL != ''

DISCLAIMER: You may need to add additional tables/fields for any customizations you may be using.

Removing User Login Data from Non-Production Environments

Leave a reply

As part of updating your AX 2009 non-production environments I recommend running a script to remove user login data so you can see who is using your environment and it doesn’t cloud things with it having LIVE login data in there.

This script will delete user log data prior to the database creation date (which should be the restored date of the database if a RESTORE DATABASE WITH REPLACE was used)

The user log data can be viewed in AX2009 via ‘Administration > Inquiries > User Log’

Initially run this query to determine the AX DB creation date:

SELECT create_date
FROM
sys.databases
WHERE
name =  '<Your AX Database Name>'

Once you’ve verified the date is okay run the below script:

/* Delete User Login Data based upon Database Creation Date */
DECLARE @dbcreatedate DATETIME

/* Get Database Creation Date */
SELECT @dbcreatedate = create_date
from
sys.databases
WHERE name = '<Your AX Database Name>'

SELECT @dbcreatedate AS Database_Creation_Date

/* Delete User Login Data */
DELETE FROM SysUserLog
WHERE
CREATEDDATETIME < @dbcreatedate

Download  Delete-User-Login-Data-using-Database-Creation-Date (860 downloads)