Missing Index – Improvement Measure Calculation Logic

Missing Index – Improvement Measure Calculation Logic

To help answer some of these questions, I calculate an arbitrary number to determine the “usefulness” of a new index, called improvement measure. The higher this number, the more useful it is to create it. The improvement measure is calculated based on 4 factors

1. Average cost of the user queries that could be reduced by the new index. This is the current cost of the query.
2. Average impact on the query if this index were created. i.e. The query cost would on average drop by this percentage value.
3. Number of Seek operations that this index could have been used by user queries.
4. Number of Scan operations that this index could have been used by user queries.

Now that you understand what missing indexes are, factors to consider and the logic behind the recommendations, go ahead and run the below query on your databases. The output provides you the “CREATE INDEX” T-SQL statement that you can use to create the index, if you see fit after considering all factors mentioned so far. This saves you time having to write the T-SQL yourself. The CREATE INDEX statement will include the index key columns in order, suggest included columns where applicable.

USE [msdb]  /*Replace with your Database Name */
GO

SELECT
    GETDATE() AS [RunTime],
    DB_NAME(mid.database_id) AS [DBNAME],
    OBJECT_NAME(mid.[object_id]) AS [ObjectName],
mid.[object_id] AS [ObjectID],
    CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS [Improvement_Measure],
    'CREATE NONCLUSTERED INDEX IX_SON_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
    + ' ON ' + mid.statement
    + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS [CREATE_INDEX_Statement],
'DROP INDEX ' + OBJECT_NAME(mid.[object_id])+'.'+'IX_SON_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) AS 'DELETE_INDEX_STATEMENT',
    migs.user_seeks, migs.user_scans, migs.last_user_seek, migs.last_user_scan, migs.avg_total_user_cost, migs.avg_user_impact, migs.avg_system_impact,
    mig.index_group_handle, mid.index_handle
FROM sys.dm_db_missing_index_groups mig
    INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
    AND mid.database_id = DB_ID()
ORDER BY [Improvement_Measure] DESC
GO

Reset tts level - An unbalanced x++ ttsbegin/ttscommit pair has been detected

Sometimes a user may receive the message of "An unbalanced X++ ttsbegin/ttscommit pair has been been detected"

The following job will clear the issue and reset the tts level. I did not create this job originally and it is wildly used if you do a simple search on the internet. I am just posting this on here because I find myself looking for the script every now again 

static void resetTTS(Args _args)

{

    if (appl.ttsLevel() > 0)

    {

        info(strFmt("Level %1 aborted", appl.ttsLevel()));

        ttsAbort;

    }

}

Get all users missing a worker relationship assignment or retail channel

I found that sometimes when setting up a large amount of users in AX for retail it can lead to sometimes missing a worker relation configuration or missing a retail channel assignment. When this happens it wont break anything but some odd ball things can start to happen. I use the following job to make sure all of the users have at least a worker assignment and are assigned to a retail channel (when valid)

static void CheckUserSetup(Args _args)
{
    UserInfo userNames;
    DirPersonUser userRelations;
    MCRChannelUser retailChannelUsers;


    //find any users that are missing either a user relation or retail channel setup
    while select * from userNames
        outer join userRelations
        where userRelations.User == userNames.id
        outer join retailChannelUsers
        where retailChannelUsers.User == userNames.id
    {
        //check to see if there is a user relation
        if(!userRelations)
        {
            info(strFmt("No User Relation: %1 (%2)", userNames.id, userNames.name));
        }

        //check to see if the user is in a retail channel
        if(!retailChannelUsers)
        {
            info(strFmt("No Retail Channel: %1 (%2)", userNames.id, userNames.name));
        }
    }
}

Database index analysis

I find myself fine tuning the ax database more frequently to get the best possible performance. I have noticed that sometimes the default dba scripts to rebuild, reorganize indexes and update statistics for tables don't always run properly.

I use the following to break down the current avg fragmentation for a specific table

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N',<database name>'),
      OBJECT_ID(N'dbo.<table name>'), NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b
      ON a.object_id = b.object_id AND a.index_id = b.index_id; 

I also use the following script to look at overall indexes which  may need attention. The below will generate the commands to rebuild or reorganize a fragmented index along with updating a specific tables index instead of relaying on the dba scripts.

DECLARE @fragmentPercent int = 30;
SELECT OBJECT_NAME(ind.OBJECT_ID) AS Tablename,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent,
'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REBUILD;' as RebuildIndex,
'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REORGANIZE;' as ReorganizeIndex,
'UPDATE STATISTICS ' + OBJECT_NAME(ind.OBJECT_ID) + ' WITH FULLSCAN;' as UpdateStatics
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent >= @fragmentPercent  and indexstats.index_type_desc <> 'HEAP'
ORDER BY indexstats.avg_fragmentation_in_percent DESC

R3 Retail Async Client + Server Error: Unable to communicate with server for upload. Please check username/password, server and database connections.

Recently I came across a strange error when dealing with the new Async Server + Client feature within AX 2012 R3 to support a channel database for a retail store/pos setup.

This environment had been working just fine with no errors for the past couple of months but I just noticed that none of the download/upload files were being processed by the server and client for the past couple of days. Nothing had changed in the AX, SQL or account access that pertained to this environment.

When looking in the event login I saw the following


CDX Client: Unable to communicate with server for upload. Please check username/password, server and database connections. Error Details: System.ServiceModel.Security.MessageSecurityException: An unsecured or incorrectly secured fault was received from the other party. See the inner FaultException for the fault code and detail. ---> System.ServiceModel.FaultException: Invalid username or password
   --- End of inner exception stack trace ---

CDX Server: Invalid username or password

However I knew that my username/password combination was valid and my certs for the aos + cdx server were still valid.

After doing some basic troubleshooting I found that the following fixed the error on both the server + client.

Retail > Setup > Parameters > Retail scheduler parameters > Sync metadata

I then restarted the async client service + did an issreset on the async server. I am not sure the last part was necessary but restarting them forces it to try to reconnect to AX.

AX 2012 R3 - Retail POS - Clear all existing transactions from the POS DB/CDX Server/CDX Client

When reloading environments sometimes it is necessary to reset the retail pos, cdx server, cdx client and back office databases to show none of the previous transactions that once existed within the system.

In order to clear it out we need to do the following (with CDX server + client services offline)

POSDB - AXRetailPOS

--current transactions within the pos system

truncate table ax.RetailPosBatchAccountTrans

truncate table ax.RetailPosBatchTable

truncate table ax.RetailPosBatchTenderTrans

truncate table ax.RetailTransactionBankedTenderTrans

truncate table ax.RetailTransactionTenderDeclarationTrans

truncate table ax.RetailTransactionDiscountTrans

truncate table ax.RetailTransactionIncomeExpenseTrans

truncate table ax.RetailTransactionInfocodeTrans

truncate table ax.RetailTransactionLoyaltyRewardPointTrans

truncate table ax.RetailTransactionOrderInvoiceTrans

truncate table ax.RetailTransactionPaymentTrans

truncate table ax.RetailTransactionSafeTenderTrans

truncate table ax.RetailTransactionSalesTrans

truncate table ax.RetailTransactionTable

truncate table ax.RetailTransactionTaxTrans

CDX_AsyncClient DB

--clear previous syncs

truncate table dbo.datastore

truncate table dbo.downloadsession

truncate table dbo.downloadsessionLog

truncate table dbo.offlinedataexportlog

CDX_AsyncServer DB

--clear previous syncs

truncate table dbo.UPLOADSESSIONLOG

truncate table dbo.UPLOADSESSION

truncate table dbo.DOWNLOADSESSIONDATASTORELOG

truncate table dbo.DOWNLOADSESSIONDATASTORE

truncate table dbo.DOWNLOADSESSION

--clear base info (optional)

truncate table dbo.FILESTORAGEPROVIDER

truncate table dbo.JOB

truncate table dbo.SCHEDULERINTERVAL

truncate table dbo.SCHEDULEPARAMETER

truncate table dbo.SCHEDULEJOB

truncate table dbo.SCHEDULEDATAGROUP

truncate table dbo.DATASTOREHEARTBEATLOG

truncate table dbo.DATASTORE

truncate table dbo.DATAGROUP

truncate table dbo.AUTHENTICATIONLOG

If there are transaction with the back office AX then you may need to do the following

Company/Retail/Journals/Open statements > remove all statements that pertain to the pos system

Then run the following job

static void ClearRetailTransactions(Args _args)

{

    RETAILTRANSACTIONBANKEDTENDERTRANS retailTable1;

    RETAILTRANSACTIONTENDERDECLARATIONTRANS retailTable2;

    RETAILTRANSACTIONDISCOUNTTRANS retailTable3;

    RETAILTRANSACTIONINCOMEEXPENSETRANS retailTable4;

    RETAILTRANSACTIONINFOCODETRANS retailTable5;

    RETAILTRANSACTIONLOYALTYREWARDPOINTTRANS retailTable6;

    RETAILTRANSACTIONORDERINVOICETRANS retailTable7;

    RETAILTRANSACTIONPAYMENTTRANS retailTable8;

    RETAILTRANSACTIONSALESTRANS retailTable9;

    RETAILTRANSACTIONTABLE retailTable10;

    RETAILTRANSACTIONTAXTRANS retailTable11;

    

    ttsbegin;

    delete_from retailTable1;

    delete_from retailTable2;

    delete_from retailTable3;

    delete_from retailTable4;

    delete_from retailTable5;

    delete_from retailTable6;

    delete_from retailTable7;

    delete_from retailTable8;

    delete_from retailTable9;

    delete_from retailTable10;

    delete_from retailTable11;

    ttscommit;

    info('done');

}

Finally you will want to remove all of the previous cdx sync files located in 

Company/Retail/Setup/Retail scheduler/Channel integration/working folders

Once this is completed you should follow the normal store setup if it is not already done.

Note: if you run the sql under --clear base info (optional) you will need to run the sync metadata function (button) that is located @

Company/Retail/Setup/Parameters/Retail scheduler parameters

Update: depending on if you are transferring data from one environment to the next  you may run into an issue where the data will download but wont upload you may need to clear out/update the following tables depending on what you are trying to do.

Clearing these tables will force a complete refresh of all pos transactions into the back office.

POSDB - AXRetailPOS:
truncate table [crt].[TABLEREPLICATIONLOG]
truncate table [crt].[UPLOADSESSION]

AX and MPOS Table Synchronization Fundamental

Retail CDX (commerce data exchange) is the interface that Dynamics AX uses to send and receive information to Retail stores running AX Retail POS.

Retail POS is a .net application that runs at the stores and can interface to opos hardware such as receipt printers, cash drawers, eftpos machines and dual displays.  It makes use of a SQL database that runs on a server located at the store.  Parts of the Retail POS application can be modified using the Retail SDK which contains the source code for most modules.  Being a .net application it does not have any support for x++ code, data dictionary, table relations, labels, AX database synchronization or the AX query engine so the code can be much more cumbersome and will use a lot of native SQL and hardcoded enums, labels, table relations and business logic.  Also each time the Retail POS application code is changed the application will need to be reinstalled for all the retail stores, unlike the AX client which would automatically get the latest code from the AOS.  It feels a bit like Microsoft may have gone down the wrong path with the Retail POS development.

There are two channels of communication between RetailPOS at the store and Dynamics AX at the Head Office.

• Async communication will synchronize specific tables between the store database and head office database. Related tables are grouped together into scheduler jobs. Scheduler jobs are run at different intervals depending on how time critical the information is.  SQL change tracking is used to identify any new or modified records in the databases and only the changes will be synchronized.  The tables are only synchronized in one direction, either from store to head office, or from head office to the store.
• Real-time communication allows retail pos to fetch or update information at head office, this can also make use of the dynamics ax business logic at head office. To use the functions that require realtime at RetailPOS the store must have a reliable internet connection.

Here is a few examples of how the data is synchronized:

• Async push: Head office updates future dated prices and discounts for products.  A batch job for syncing product information is scheduled to run nightly in AX. This uses SQL change tracking to identify the new pricing records and packages up the data to be sent to the store. 
• Async pull: Head office has a batch job running every hour that requests retail sales transactions from the store.  The async client running at the store will see that this information was requested by AX and use Replication Counters to identify the new transactions, these are packaged up and sent to AX to be imported into the database.
• Realtime:  Worker selects a customer to view their balance.  Realtime service call is made to AX in Head Office to get customer balance.  Balance is displayed on screen. 
• Realtime & async:  Worker at pos updates customer address, realtime service call is made to AX to update address in head office. If the call is successful, pos will also update the address in its own database.  A batch job for syncing customer information will detect the changed address in head office and it will be packaged up and sent to RetailPos. The async client at the store will overwrite the customer address information with what is in head office, since the address in head office is the same the record will be overwritten with the exact same data.

Real-time:

Real-time web service can be installed using the Dynamics AX installer or via powershell scripts.  There is a bug with windows 10 where the powershell script must be modified to get it to complete.  The realtime service setup creates and configures a website in IIS.  When installing you will be prompted for a SSL certificate thumbprint, this need to be a valid certificate for the address that the realtime service is running on.  Also you will need to provide the credentials for the AppPool user for the website. IIS will run the .net business connector as this user to connect to AX so the user must be in AX.  The .net business connector will call methods in the RetailTransactionService- classes in AX.

(Refer to https://technet.microsoft.com/en-us/library/hh575229.aspx)

POS will need to know the web address of the realtime service to use it.  This is configured in AX through Retail->Setup->POS->Retail Scheduler->Channel integration->Realtime service profiles.

There are already a few sites describing the settings in this screen so I won’t do that.  Just make sure the passphrase is filled in, it can be anything but if left blank then the realtime will not work. https://technet.microsoft.com/en-us/library/hh580631.aspx

The information in this screen is synced down to pos through the async service.  Run the 1070 (channel configuration) or 1090 jobs to push these settings to the pos database.

Realtime not working? Make sure to check in the windows event viewer for error details.

Async:

There is a few different components here…

• Head office message database
• Client message database
• File share
• RetailPos database
• Async server IIS webservice
• Async client service
• Utilities (AsyncClientConfigurationUtility, RetailDatabaseUtility, DDPackView)

Push Jobs (AX -> POS)

For the push jobs (head office to store) AX will have a batch job which periodically executes a specified distribution schedule (eg. 1010 – customers).  This will identify a list of all the tables that need to be synced for the job (from RetailConnScheduler* tables).  For each table it will grab some pre-generated SQL from the RetailCDXChangeTrackingSQL table, this SQL is used along with the last SQL change tracking version number to identify the records that have changed since the last sync. 

The identified records are further filtered down to store specific data through the Table Distrubution XML in the retail channel schema. The XML describes how all the tables link back to the RetailChannelTable. This is useful in cases such as when we only want to sync customers that are linked to the store through the stores address book relation, rather than sending all customers data to all stores.  Editing the XML is very manual and a mistake could wipe out all the data from a table at the POS databases, it would have been nice if they created a form with a tree view of the tables and just used the AOT table relations. 

Custom fields and tables that also need to be synced to POS can be defined in the RetailCDXSeedData_AX63 class (or a class that extends this).  The class contains a list of all the Tables, Fields and which scheduler job they belong to.

Once the table distribution XML or channel schema class has been modified the RetailCDXSeedData_AX63 needs to be run to generate additional classes that are used during retail sync, this only needs to happen when the schema changes.  The RetailCDXChannelSpecificData_AX63 class will be generated which contains RecordSet based operations for filtering the data down to the store level based on the table distribution XML. 

The RetailCDXXmlDataOutput_AX63 class is also generated which contains while select statements to read the data and generate the XML.

The generated XML is grouped with other files, compressed to .rpf and saved to a fileshare defined in the ‘Working folders’ setup in AX. The filename and status is then written to the AsyncServerHeadOffice database directly from AX.  The xml from the .rpf files can be extracted by using the DDPackView tool which is included when installing the retail components.

Every two minutes the Async clients at the stores will connect to the Async server website which also has a connection to the AsyncServerHeadOffice database.  The async server reads the AsyncServerHeadOfficedatabase and identifies that there is a new file to be sent and reads the file from the fileshare sending it to the async client. 

The async client will write this into the AsyncClientMessage database and then update the data in the RetailPOS database.  This can either be a delta update where records are inserted, updated and deleted; or a full update where the entire table is deleted and then repopulated from the XML.  The async client connects to the async server again to write back the status of the update.  This is stored in the AsyncServerHeadOffice database and can be viewed in AX in the download sessions form. 

Pull Jobs (POS -> AX)

For the pull jobs there is also a batch job scheduled in AX.  This writes a record to the AsyncServerHeadOffice database requesting the data from POS.  When the async client connects to the Async server website it identifies that the data has been requested, also packages the data into RPF files and sends it to the AsyncServer. The file is written into the fileshare where it is read and applied to the database by AX.

All pull tables in the POS database have a field called ReplicationCounterFromOrigin which is an auto-incremented number, every new record gets a new number.  There is a table [RetailPOS].[crt].[TableReplicationLog] that stores a list of all tables that need to upload data. This table has a field FILTERMAX, which is a reference to the above [ReplicationCounterFromOrigin] field.  FILTERMAX is always updated to indicate the last transaction uploaded.

That means any new records created in store DB will get a value in REPLICATIONCOUNTERFROMORIGIN greater than the one currently set in FILTERMAX. Every time the pull job runs, it checks REPLICATIONCOUNTERFROMORIGIN in all tables against the FILTERMAX value for those tables and sees if there are any new transactions that need to be uploaded, using the Async client service and sets the FILTERMAX values for the tables to the last uploaded value.