Tworzenie bazy BaseLine dla AX2012R2

Plecenie do tworzenia bazy danych BaseLine dla Dynamics AX 2012 :

Initialize-AXModelStore -Server OPTSBR2\SQL2012 -Database AXDB_Baseline -Verbose

Mój stary Job jeszcze z czasów AX3.0.
Umożliwia on wyszukanie w bazie danych w jakich tabelach i polach znajduje się konkretna wartość.
Przykładowo chcemy wyszukać w jakich tabelach jest pole o typie Voucher (niezależnie od jego nazwy, uwzględniając typy podrzędne) o wartości 'VOU0012345'.

  
static void SBR_FindValue_v1_1(Args _args)
{
    str                     func;
    Dictionary              dict;
    DictTable               dictTable;
    DictField               dictField;
    int                     tableCnt;
    int                     fieldCnt;
    Dialog                  dialog = new dialog();
    Dialogfield             dfLookFor;
    Dialogfield             dfLookForType;
    Name                    lookFor;
    int                     lookForTypeId;
    Set                     setTypes;
    ExtendedDataTypeName    lookForType;
    xRefTypeHierarchy       xRefTypeHierarchyBase;
    Query                   query;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    QueryRun                qr;

    Set fillList(Set _set, xRefTypeHierarchy _xRefTypeHierarchy, boolean _forward = true)
    {
        xRefTypeHierarchy   __xRefTypeHierarchy;
        int                 __id;

        if (_forward)
        {
            __id = _xRefTypeHierarchy.Id;
            select __xRefTypeHierarchy
                where __xRefTypeHierarchy.BaseType == _xRefTypeHierarchy.BaseType
                   && __xRefTypeHierarchy.Parent   == __id;
        } else {
            __id = _xRefTypeHierarchy.Parent;
            select __xRefTypeHierarchy
                where __xRefTypeHierarchy.BaseType == _xRefTypeHierarchy.BaseType
                   && __xRefTypeHierarchy.Id       ==  __id;
        }

        while (__xRefTypeHierarchy)
        {
            _set.add(__xRefTypeHierarchy.Id);
            fillList(_set, __xRefTypeHierarchy, _forward);
            next __xRefTypeHierarchy;
        }
        return _set;
    }
    ;

    func         = "int getExtendedTypeNum() {\n";
    func = func  + "  ;\n";
    func = func  + "  return extendedTypeNum(\'%1\');\n";
    func = func  + "}\n";

    dict = new Dictionary();

    dialog.caption('Looking for...');
    dfLookForType   = dialog.addField(typeid(ExtendedDataTypeName), 'Type');
    dfLookFor       = dialog.addField(typeid(Name), 'Value');

    if (!dialog.run())
        return;

    lookForType     = dfLookForType.value();
    lookFor         = dfLookFor.value();
    lookForTypeId   = runBuf(StrFmt(func, lookForType));
    setTypes        = new Set(Types::Integer);

    if (lookFor == '' || lookForType == '')
        throw error('Input parameters cannot be empty!');

    setPrefix(strFmt('Searching value \'%1\' found in:', lookFor));

    select xRefTypeHierarchyBase
        where xRefTypeHierarchyBase.Name == lookForType
           && xRefTypeHierarchyBase.Id   == lookForTypeId;

    if (xRefTypeHierarchyBase)
    {
        setTypes = fillList(setTypes, xRefTypeHierarchyBase);
        setTypes = fillList(setTypes, xRefTypeHierarchyBase, false);
    }

    setTypes.add(lookForTypeId);

    StartLengthyOperation();

    for(tableCnt = 1; tableCnt <= dict.tableCnt(); tableCnt++)
    {
        dictTable = new DictTable( dict.tableCnt2Id(tableCnt) );
        if (dictTable.isTmp() ||  dictTable.isView() || dictTable.isMap())
            continue;

        for(fieldCnt = 1; fieldCnt <= dictTable.fieldCnt(); fieldCnt++)
        {
            dictField = new DictField(dictTable.id(), dictTable.fieldCnt2Id(fieldCnt));

            if (dictField.typeId() > 0 && setTypes.in(dictField.typeId()))
            {
                infolog.messageWin().addLine(strFmt('searching %1 in %2(%3)',  dictField.baseType(), dictTable.name(), dictField.name()));
                query = new Query();
                qbds  = query.addDataSource(dictTable.id());
                qbds.firstOnly(true);
                qbr = qbds.addRange(dictField.id());
                qbr.value(queryValue(lookFor));
                qr = new QueryRun(query);
                if (qr.next())
                {
                    infolog.messageWin().addLine('FOUND!');
                    info(strFmt('Table = %1, Field = %2', dictTable.name(), dictField.name()));
                }
            }
        }
    }
    EndLengthyOperation();
}

Active sessions and locks

---

-- active requests
SELECT 
    r.session_id,
    se.host_name,
    se.login_name,
    Db_name(r.database_id) AS dbname,
    r.status,
    r.command,
    r.cpu_time, r.total_elapsed_time,
    r.reads, r.logical_reads, r.writes,
    s.text sql_text, p.query_plan query_plan,
    SQL_CURSORSQL.text, SQL_CURSORPLAN.query_plan
FROM sys.dm_exec_requests r
    INNER JOIN sys.dm_exec_sessions se
        ON r.session_id = se.session_id
    OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s
    OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
    OUTER APPLY sys.dm_exec_cursors(r.session_id) AS SQL_CURSORS
    OUTER APPLY sys.dm_exec_sql_text(SQL_CURSORS.sql_handle) AS SQL_CURSORSQL
    LEFT JOIN sys.dm_exec_query_stats AS SQL_CURSORSTATS
        ON SQL_CURSORSTATS.sql_handle = SQL_CURSORS.sql_handle
    OUTER APPLY sys.dm_exec_query_plan(SQL_CURSORSTATS.plan_handle) AS SQL_CURSORPLAN
WHERE  r.session_id <> @@SPID AND se.is_user_process = 1 

---

-- active locks
SELECT  
    L.request_session_id AS SPID, 
    DB_NAME(L.resource_database_id) AS database_name,
    O.Name AS locked_object_name, 
    P.object_id AS locked_objectId, 
    L.resource_type AS locked_resource, 
    L.request_mode AS lock_type,
    ST.text AS sql_statement_text,        
    ES.login_name AS login_name,
    ES.host_name AS [host_name],
    TST.is_user_transaction as is_user_transaction,
    AT.name as transaction_name,
    CN.auth_scheme as authentication_method
FROM sys.dm_tran_locks L
    JOIN sys.partitions P 
        ON P.hobt_id = L.resource_associated_entity_id
    JOIN sys.objects O 
        ON O.object_id = P.object_id
    JOIN sys.dm_exec_sessions ES 
        ON ES.session_id = L.request_session_id
    JOIN sys.dm_tran_session_transactions TST 
        ON ES.session_id = TST.session_id
    JOIN sys.dm_tran_active_transactions AT 
        ON TST.transaction_id = AT.transaction_id
    JOIN sys.dm_exec_connections CN 
        ON CN.session_id = ES.session_id
    CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id

---

Unused/Missing indexes

---

-- unused indexes
SELECT  OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
        OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
        I.NAME AS IndexName    
FROM    sys.indexes I   
WHERE   -- only get indexes for user created tables
        OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1 
        -- find all indexes that exists but are NOT used
        AND NOT EXISTS ( 
                    SELECT  index_id 
                    FROM    sys.dm_db_index_usage_stats
                    WHERE   OBJECT_ID = I.OBJECT_ID 
                            AND I.index_id = index_id 
                            -- limit our query only for the current db
                            AND database_id = DB_ID()) 
  AND I.index_id != 0
  AND I.is_primary_key = 0
  AND I.is_unique_constraint = 0  
  -- AND I.is_unique = 0                            
ORDER BY SchemaName, ObjectName, IndexName 

---

-- rarely used indexes
SELECT
    O.name AS object_name, 
    I.name AS index_name, 
    I.index_id AS index_id, 
    DM_IUS.user_seeks AS user_seeks, 
    DM_IUS.user_scans AS user_scans, 
    DM_IUS.user_lookups AS user_lookups, 
    DM_IUS.user_updates AS user_updates, 
    P.table_rows as table_rows,
    DM_IUS.last_user_seek,
    DM_IUS.last_user_scan,
    DM_IUS.last_user_lookup,
    DM_IUS.last_user_update 
FROM sys.dm_db_index_usage_stats DM_IUS
INNER JOIN sys.indexes I
    ON I.index_id = DM_IUS.index_id AND 
       DM_IUS.OBJECT_ID = I.OBJECT_ID
INNER JOIN sys.objects O 
    ON DM_IUS.OBJECT_ID = O.OBJECT_ID
INNER JOIN sys.schemas S
    ON O.schema_id = S.schema_id
INNER JOIN (SELECT SUM(P.rows) table_rows, P.index_id, P.OBJECT_ID FROM sys.partitions P 
            GROUP BY P.index_id, P.OBJECT_ID) P
    ON P.index_id = DM_IUS.index_id 
       AND DM_IUS.OBJECT_ID = P.OBJECT_ID
WHERE OBJECTPROPERTY(DM_IUS.OBJECT_ID,'IsUserTable') = 1
    AND DM_IUS.database_id = DB_ID()
    AND I.type_desc = 'nonclustered'
    AND I.is_primary_key = 0
    AND I.is_unique_constraint = 0
ORDER BY (DM_IUS.user_seeks + DM_IUS.user_scans + DM_IUS.user_lookups) ASC

---

-- used indexes stats
SELECT    
    DB_NAME(C.database_id) AS database_name,
    OBJECT_NAME(C.object_id, C.database_id) AS table_name,
    A.avg_user_impact * (A.user_seeks + A.user_scans) avg_estimated_impact,
    A.avg_total_user_cost * (A.user_seeks + A.user_scans) avg_estimated_total_user_cost,
    A.unique_compiles, 
    A.user_seeks,
    A.last_user_seek,
    DATEPART(d, GETDATE() - A.last_user_seek) as last_user_seek_days_ago,
    A.user_scans,
    A.last_user_scan,
    DATEPART(d, GETDATE() - A.last_user_scan) as last_user_scan_days_ago,
    A.avg_total_user_cost,
    A.avg_user_impact,
    C.equality_columns,
    C.inequality_columns,
    C.included_columns  
FROM sys.dm_db_missing_index_group_stats AS A
INNER JOIN sys.dm_db_missing_index_groups AS B
    ON A.group_handle = B.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS C
    ON B.index_handle = C.index_handle
--WHERE  
    --DATEPART(hh, last_user_seek) >= 6 AND DATEPART(hh, last_user_seek) <= 20 AND    -- run in work hours
    --DATEPART(d, GETDATE() - A.last_user_seek) = 1          -- run today
ORDER BY avg_estimated_total_user_cost DESC

---

Create ledger journal from code (using LedgerJournalEngine)

  
static void SBR_Create_Ledger_Journal(Args _args)
{
    #define.JournalName('KOST12')
    
    LedgerJournalName       ledgerJournalName;
    LedgerJournalTable      ledgerJournalTable;
    LedgerJournalTrans      ledgerJournalTrans;
    LedgerJournalId         journalId;
    Amount                  amount;
    LedgerJournalEngine     engine;
    ;   
    ttsBegin;

    ledgerJournalName = LedgerJournalName::find(#JournalName);
    
    ledgerJournalTable.clear();
    ledgerJournalTable.JournalName  = ledgerJournalName.JournalName;
    ledgerJournalTable.initFromLedgerJournalName();
    ledgerJournalTable.insert();

    journalId = ledgerJournalTable.JournalNum;
    
    // Create Trans
    engine = LedgerJournalEngine::construct(ledgerJournalName.JournalType);
    engine.newJournalActive(ledgerJournalTable);

    ledgerJournalTrans.clear();
    ledgerJournalTrans.initValue();
    ledgerJournalTrans.JournalNum = journalId;
    engine.initValue(ledgerJournalTrans);

    // account
    ledgerJournalTrans.AccountType          = LedgerJournalACType::Cust;
    engine.accountNumModified(ledgerJournalTrans);

    ledgerJournalTrans.AccountNum           = '10058';
    engine.accountModified(ledgerJournalTrans);

    // offset account
    ledgerJournalTrans.OffsetAccountType    = LedgerJournalACType::Ledger;
    engine.offsetAccountTypeModified(ledgerJournalTrans);

    ledgerJournalTrans.OffsetAccount        = '22500';
    engine.offsetAccountModified(ledgerJournalTrans);

    // dimensions
    ledgerJournalTrans.Dimension[1] = '';
    ledgerJournalTrans.Dimension[2] = '';
    ledgerJournalTrans.Dimension[3] = '';
    engine.initDimension(ledgerJournalTrans);

    // amount
    amount = 100;
    
    ledgerJournalTrans.AmountCurCredit  = (amount > 0 ? abs(amount) : 0);

    if (ledgerJournalTrans.AmountCurCredit)
        engine.amountCurCreditModified(ledgerJournalTrans);

    ledgerJournalTrans.AmountCurDebit   = (amount < 0 ? abs(amount) : 0);
    
    if (ledgerJournalTrans.AmountCurDebit)
        engine.amountCurDebitModified(ledgerJournalTrans);

    ledgerJournalTrans.insert();

    pause;

    ttsCommit;
    
    info(strFmt('created journal %1', journalId));
}