Monday 2 September 2019

Use DynamicPerf- Beginner Guide-Find Expensive Query by time period


Use DynamicsPerf

SELECT TOP 100 SERVER_NAME,
               DATABASE_NAME,
               QUERY_HASH,
               QUERY_PLAN_HASH,
               SUM(CAST(TIME_THIS_PERIOD / 1000.000 AS DECIMAL(20, 3)))                               AS TOTAL_TIME_MS,
               SUM(EXECUTIONS_THIS_PERIOD)                                                            AS TOTAL_EXECUTIONS,
               CASE SUM(EXECUTIONS_THIS_PERIOD)
                 WHEN 0 THEN 0
                 ELSE ( SUM(CAST(TIME_THIS_PERIOD / 1000.000 AS DECIMAL(20, 3))) / SUM(EXECUTIONS_THIS_PERIOD) )
               END                                                                                    AS AVG_TIME_MS,
               (SELECT TOP 1 QUERY_PLAN_PARSED
                FROM   QUERY_PLANS_PARSED_VW V2
                WHERE  V2.SERVER_NAME = CTE.SERVER_NAME
                       AND V2.DATABASE_NAME = CTE.DATABASE_NAME
                       AND V2.QUERY_HASH = CTE.QUERY_HASH
                       AND V2.QUERY_PLAN_HASH = CTE.QUERY_PLAN_HASH)                                  AS QUERY_PLAN_PARSED,
               (SELECT SQL_TEXT
                FROM   QUERY_TEXT V2
                WHERE  V2.SERVER_NAME = CTE.SERVER_NAME
                       AND V2.DATABASE_NAME = CTE.DATABASE_NAME
                       AND V2.QUERY_HASH = CTE.QUERY_HASH)                                            AS SQL_TEXT,
               (SELECT QUERY_PLAN
                FROM   QUERY_PLANS V2
                WHERE  V2.SERVER_NAME = CTE.SERVER_NAME
                       AND V2.DATABASE_NAME = CTE.DATABASE_NAME
                       AND V2.QUERY_PLAN_HASH = CTE.QUERY_PLAN_HASH)                                  AS QUERY_PLAN,
               SUM(CAST(WORKER_TIME_THIS_PERIOD / 1000.000 AS DECIMAL(20, 3)))                        AS WORK_TIME,
               SUM(CAST(( TIME_THIS_PERIOD - WORKER_TIME_THIS_PERIOD ) / 1000.000 AS DECIMAL(14, 3))) AS WAIT_TIME
FROM   QUERY_STATS_CTE_VW CTE
WHERE  1 = 1
--AND STATS_TIME BETWEEN '2018-10-29 07:15:00.000' AND '2018-10-29 07:26:00.000' --REH 1 minute past last collection
--AND STATS_TIME IN (SELECT MAX(STATS_TIME) FROM QUERY_STATS)  --Look at last data collection
--  Find all queries using a specific table/index,
--AND QUERY_PLAN_HASH IN
--             (SELECT DISTINCT  QUERY_PLAN_HASH FROM QUERY_PLANS_PARSED QPP WHERE CTE.QUERY_PLAN_HASH = QPP.QUERY_PLAN_HASH
--                             AND TABLE_NAME = 'INVENTDIM' AND INDEX_NAME = 'I_698DIMIDIDX')
--AND DATABASE_NAME = 'XXXXXXXXXXXXXX'
--AND QUERY_HASH = 0x020C200A1715926C
--AND SQL_TEXT like '%TABLE_NAME%'
GROUP  BY SERVER_NAME,
          DATABASE_NAME,
          QUERY_HASH,
          QUERY_PLAN_HASH
ORDER  BY TOTAL_TIME_MS DESC

Ebooks Site for future reference

Thursday 16 May 2019

Find running query in sql


SELECT s.session_id
    ,r.STATUS
    ,r.blocking_session_id 'blocked by'
    ,r.wait_type
    ,wait_resource
    ,r.wait_time / (1000.0) 'Wait Time (in Sec)'
    ,r.cpu_time
    ,r.logical_reads
    ,r.reads
    ,r.writes
    ,r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)'
    ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
            (
                CASE r.statement_end_offset
                    WHEN - 1
                        THEN Datalength(st.TEXT)
                    ELSE r.statement_end_offset
                    END - r.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
     Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
    ,r.command
    ,s.login_name
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.last_request_end_time
    ,s.login_time
    ,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
    ,r.STATUS
    ,r.blocking_session_id
    ,s.session_id

Friday 30 November 2018

AX 2012 R3 Query to Find the Jobs which are in stuck state and need to cancel them to clear up the download session.

AX 2012 R3 Query to Find the Jobs which are in stuck state and need to cancel them to clear up the download session.

--Query to Find out how many Jobs are not applied as per current date and got into stuck status
Select * from RetailCDXDownloadSessionDataStore where status!=4 and CREATEDDATETIME not between '2018-11-30 00:00:00.000' and '2018-11-30 23:59:59.000'

--- Query to Cancel the Old not applied Job----
update  RetailCDXDownloadSessionDataStore set status=5  where status!=4 and CREATEDDATETIME not between '2018-11-30 00:00:00.000' and '2018-11-30 23:59:59.000'

Saturday 13 October 2018

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

Friday 4 May 2018

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));
        }
    }
}