AX 2009, AX 2012, Development, Dynamics AX, Uncategorized

AX Database Maintenance

AX Databases perform better if regular Index Maintenance is done on SQL server. It is only suggested to run this during non-peak hours. A recurring job to be created to run index maintenance periodically.

I am herewith sharing statistics update strategies I read from a blog.

Statistics Update Strategies

SQL will keep statistics on indexes on how data is structured within each of those indexes in order to determine the best execution plan. These statistics look at the composition of data to determine such things as whether to seek (utilize the B-tree in the index) or scan (walk through the whole table) among many other execution options.

Within Dynamics AX, these statistics are kept up to date by the 2 options on the database

             auto_create_statistics – When column is specified in a query, statistics on that column are kept.

auto_update_statistics – When enough changes have been made to the column or index, SQL will update its statistics.

Best practice for the Dynamics AX database is to leave these ‘ON’, which is the default when installing Dynamics AX. However, you may have taken note of the text for auto_update_statistics which states “when enough changes have been made to the column”. What this means is that when a certain percentage of records have been changed, SQL will perform an update on the information that it has about the data. If a table contains a high number of rows, it can take a long time however for these statistics to be updated, which will then cause SQL to pick an execution plan that may not be the best option for the current data composition.

If you have large tables, it is recommended to run a nightly update only for those tables. We have found that transactional tables such as LEDGERTRANS and INVENTTRANS are prone to needing to be updated on a nightly basis. You have the option of turning auto_update_statistics off for those tables on a per-table basis. When doing an update statistics, it is recommended that you test how your data will be sampled. The more data that is sampled, the more accurate the statistics will be. For example, the following command will scan every record for the most accuracy

      UPDATE STATISTICS <table_name> WITH FULLSCAN

Depending on the size of the table, this can take multiple hours to complete, so you may choose to sample a certain percentage of records, at the expense of accuracy.

UPDATE STATISTICS <table_name> WITH SAMPLE 50 PERCENT

We can make no recommendations on what is the better option, each one should be tested for the time it takes, and the overall impact on execution plan performance.

SQL ships with a stored procedure called sp_updatestats. You can take advantage of this stored procedure which will automatically update statistics for those indexes and columns which have changed data.

If you are running SQL 2008 R2 SP1 or greater, you can enable this feature through Trace Flag 2371. The linked article has more explanation on this change.

Fragmentation

If you would like to know the fragmentation in indexes, run following query on the dynamics perf (if installed) database to find fragmentation details on the indexes.

 

SELECT DATABASE_NAME, 
       TABLE_NAME, 
       INDEX_NAME, 
       AVG_FRAGMENTATION_IN_PERCENT, 
       FRAGMENT_COUNT 
FROM   INDEX_STATS_CURR_VW 
ORDER  BY AVG_FRAGMENTATION_IN_PERCENT DESC

 

Alternatively,  you can run following query on the database to find the fragmentation on the indexes.

SELECT OBJECT_NAME(ps.OBJECT_ID)             AS TableName, 
       si.NAME, 
       ps.AVG_FRAGMENTATION_IN_PERCENT, 
       STATS_DATE(ss.OBJECT_ID, ss.STATS_ID) AS LastUpdatedStatistics 
FROM   sys.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(DB_NAME()), NULL, NULL, NULL, NULL) 
       ps 
       JOIN SYSINDEXES si 
         ON ps.OBJECT_ID = si.ID 
            AND ps.INDEX_ID = si.INDID 
       LEFT OUTER JOIN sys.STATS ss 
                    ON ss.OBJECT_ID = ps.OBJECT_ID 
                       AND ss.NAME = si.NAME 
WHERE  ps.AVG_FRAGMENTATION_IN_PERCENT > 10 
ORDER  BY ps.AVG_FRAGMENTATION_IN_PERCENT DESC 

I came across a very useful blog and a tool for Database maintenance. Please go through this blog for detailed helpful tool and maintenance. Thanks! to Ola Hallengren

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Download the SQL file by clicking the link below on Index Optimize, added here  as well for quick reference. Latest copy should be taken from the link above. Below link file extension is Key change that to SQL and use it. Please change the database name to your AX database name before run. And I would suggest to run this when you have proper downtime .

IndexOptimize

Examples

A. Rebuild or reorganize all indexes with fragmentation on all user databases

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

B. Rebuild or reorganize all indexes with fragmentation and update modified statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = ‘ALL’,
@OnlyModifiedStatistics = ‘Y’

C. Update statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = ‘ALL’

D. Update modified statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = ‘ALL’,
@OnlyModifiedStatistics = ‘Y’

E. Rebuild or reorganize all indexes with fragmentation on all user databases, performing sort operations in tempdb and using all available CPUs

EXECUTE dbo.IndexOptimize @Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = ‘Y’,
@MaxDOP = 0

F. Rebuild or reorganize all indexes with fragmentation on all user databases, using the option to maintain partitioned indexes on the partition level

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PartitionLevel = ‘Y’

G. Rebuild or reorganize all indexes with fragmentation on all user databases, with a time limit so that no commands are executed after 3600 seconds

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@TimeLimit = 3600

H. Rebuild or reorganize all indexes with fragmentation on the table Production.Product in the database AdventureWorks

EXECUTE dbo.IndexOptimize
@Databases = ‘AdventureWorks’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = ‘AdventureWorks.Production.Product’

I. Rebuild or reorganize all indexes with fragmentation except indexes on the table Production.Product in the database AdventureWorks

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = ‘ALL_INDEXES, -AdventureWorks.Production.Product’

J. Rebuild or reorganize all indexes with fragmentation on all user databases and log the results to a table

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LogToTable = ‘Y’

Advertisements
Standard
AX 2012, Calling Webservice, Development, Rest APIs, Uncategorized

Calling Rest API from AX 2012

AX 2012 supports system.Net namespace interface. This interface provides simple programming interface for many of the protocols used on the networks today. Herewith i am sharing a simple example to call a rest API asynchronously.  it is very lightweight and has hardly an impact if you are calling asynchronously.

 

static void CallRestAPISample(Args _args)
{
 System.Net.WebHeaderCollection headers ;
 System.Net.WebClient webclient;
 System.Uri uri;
 
 // Create XML msg or string message as parameter to call the Rest API. 
 str msgstring = '<?xml version="1.0"?> ';
 
 msgstring += '<LogRequest>' ;
 
 msgstring += '<ApplicationName>AX Error Tracking</ApplicationName>';
 msgstring += ' <Date>06-22-2017 12:58:22 PM</Date>';
 msgstring += '<ThreadID>0</ThreadID>';
 msgstring += '<LogLevel> <INFO>false</INFO> <DEBUG>false</DEBUG> <ERROR>true</ERROR> <FATAL>false</FATAL> <WARN>false</WARN> </LogLevel>';
 msgstring += '<LogDetails> <ExceptionMessage>Test Exception</ExceptionMessage> <Message>This is test message from AX Logging Call.</Message> </LogDetails> ';
 msgstring += '<Metadata> <Data> <Name>ENV</Name> <Value>DEV</Value> </Data> <Data> <Name>Server</Name> <Value>AX AOS 1</Value> </Data> <Data> <Name>Source</Name> <Value>DAX</Value> </Data> </Metadata>';
 msgstring += '<Email> <From>FromAddr@Fromserver.com</From> <To>toaddrr@toserver.com</To> <Subject>AX Logging Test</Subject> <Message>This is test message</Message> <HasAttachments>false</HasAttachments> <IsBodyHtml>true</IsBodyHtml> <IncludeLogDetails>true</IncludeLogDetails> </Email>';
 msgstring += '</LogRequest>';
 
 
 info(strFmt("%1",time2str(timeNow(),1,1)));
 // initilizing host URI
 uri = new System.Uri("http://LogginServiceHost/api/Log/LogRequest");// FAKE SERVICE HOST ADDRESS
 // initializing webClient
 webclient = new System.Net.WebClient();
 headers = webclient.get_Headers();
 // Set header content type
 Headers.Add(System.Net.HttpRequestHeader::ContentType, "text/xml;charset=utf-8");
 // call API asynchronously
 webclient.UploadStringAsync(uri,msgstring);
 
 info(strFmt("%1",time2str(timeNow(),1,1)));

}

Standard
AX 2009, AX 2012, Development, dialog, Dynamics AX, User parameters, without dialogable class, without runbasebatch

Quick Dialog to take user entered parameters without dilaogable class creation

Following job uses dialog class to take parameters from user and run the job

static void UserParamSample(Args _args)
{

wmslocationID     wmslocationID;

str                           palletstoRAF;

dialog          dlg = new dialog();

 dialogField     dlgPalletIDs,dlgwmslocation;

 dlgPalletIDs = dlg.addField(typeid(ItemFreeTxt),”Container number”,”Enter pallet Ids seperated by commas”);
dlgwmslocation = dlg.addField(typeid(wmslocationid),”Location”);

if(!dlg.run())

return;

else

{

palletstoRAF = dlgPalletIDs.value();

wmslocationid = dlgwmslocation.value();

//Run the Job

  }

}

Standard
AX 2012, Development

Dynamics AX 2012 connect to Visual studio online , Team Foundation Server

With Visual Studio Online, Now you can have your project data on cloud and accessible anywhere. if you are a MSDN subcriber then you can start on VS online today by clicking here. Else you start a trial here .

How to connect to visual Studio online :

With a simplistic scenario when no Branching is setup below are the steps to follow.

1. Create a Team project in your VS online

At the home page of your VS online create new team project by choosing a suitable process template.

Create a Team Project

Create a Team Project

2. Add members to the project using manage members

Add members

3.  Go to AX client and configure TFS as version control for AX

Enable Version control in AX.

Enable TFS in AX.

Enable TFS in AX.

Setup VS online site , make sure that you follow the same pattern with port number etc. else it doesn’t get connected.

Setup TFS online

Setup TFS online

Standard
AX 2009, AX 2012, Development, Dynamics AX, Uncategorized

How to send a file to the printer in dynamics AX

How to send a file to the printer in dynamics AX

Following Job demonstrates on how to send a file from the file system to the locally connected printer( printer physically can be network or local but it should be added to the client and its name should be visible in the control panel–> devices and printers)

static void sendToPrinter(Args _args)
{
PrintJobSettings printJobSettings = new PrintJobSettings();
Dialog dialog = new Dialog();
DialogField dialogFileName;
str CoreLabelExe;
str CoreLabelParm;
;
// initiallize printer with the printer name. This initiallization automatically
// gets dll file details and port details.
printJobSettings = new PrintJobSettings();
printJobSettings.SetTarget(PrintMedium::Printer);
printJobSettings.deviceName(‘Microsoft XPS Document Writer’);
dialogFilename = dialog.addField(typeid(FilenameOpen));
if (dialog.run())
{
// finding executable DLL
CoreLabelExe = WinAPI::findExecutable(dialogFileName.value());
// Create Shell command parameter
CoreLabelParm = strFmt(‘ /PT “%1” “%2” “%3” “%4″‘,
dialogFileName.value(),
printJobSettings.printerPrinterName(),
printJobSettings.printerDriverName(),
printJobSettings.printerPortName());
// run dll on the shel with parameter
winAPI::shellExecute(CoreLabelExe, CoreLabelParm);
}
}

Standard
AX 2012, Development, Dynamics AX

Creating File Using Text I/O class

you can use Text I/O class to create a flat file in file system. TextIO supports Unicode characters.

code Class_LXKFormattedTextFile (1).

Download above class xpo and then change the file extension from “.KEY” to “.XPO”

Class Declaration

Class Declaration

Init File

Init File

Run

Run

init Device

init Device

Format Text

Format Text

Create Data

Create Data

Can create file

Can create file

Creating File Using Text I/O class

Aside
AX 2012, Development, Dynamics AX, Uncategorized

Reserve quantity by X++ code

Quick code snippet to reserve the quantity by X++

SalesLine locSalesline;

InventMovement inventmovement;

InventUpd_Reservation reservation;

inventMovement = InventMovement::constructNoThrow(locSalesline,InventMovSubType::None);

reservation = InventUpd_Reservation::newinventdim(inventMovement, InventDim::find(InventDimReserveFrom),-1*inventqty, false);

reservation.updateNow();

Standard