Reporting_PerformanceAnalysis

Converted from: 20.101.Reporting_PerformanceAnalysis.sql

/*
using BatchProcessing logging to get performance stats
*/

--review BatchProcess logs
SELECT *
FROM [dbo].[MFProcessBatch] AS [mpb]
ORDER BY [mpb].[ProcessBatch_ID] DESC;

SELECT *
FROM [dbo].[MFProcessBatchDetail] AS [mpbd]
--WHERE [mpbd].[ProcessBatch_ID] = 1180
ORDER BY [mpbd].[ProcessBatch_ID] DESC
        ,[mpbd].[ProcessBatchDetail_ID] DESC;

-- summary of ProcessBatch
SELECT [mpb].[ProcessType]
      ,[mpb].[LogText]
      ,COUNT(*) [Instances]
      ,AVG([mpb].[DurationSeconds])
FROM [dbo].[MFProcessBatch] AS [mpb]
WHERE [mpb].[Status] = 'Completed'
      AND [mpb].[ProcessType] IS NOT NULL
GROUP BY [mpb].[ProcessType]
        ,[mpb].[LogText];

-- ProcessBatch summary by table
SELECT [mpb].[ProcessType]
      ,[mpbd].[MFTableName]
      ,COUNT(*)                     [Instances]
      ,SUM([mpb].[DurationSeconds]) [totalDuration]
FROM [dbo].[MFProcessBatchDetail]     AS [mpbd]
    INNER JOIN [dbo].[MFProcessBatch] AS [mpb]
        ON [mpb].[ProcessBatch_ID] = [mpbd].[ProcessBatch_ID]
WHERE [mpb].[Status] = 'Completed'
      AND [mpb].[ProcessType] IS NOT NULL
      AND [mpbd].[ColumnName] = 'NewOrUpdatedObjectDetails'
GROUP BY [mpb].[ProcessType]
        ,[mpbd].[MFTableName];

--  records per second
SELECT [mpb].[ProcessType]
      ,[mpbd].[MFTableName]
      ,[mpb].[LogText]
      ,MIN([mpb].[CreatedOn])                 AS [earliestDate]
      ,MAX([mpb].[CreatedOn])                 AS [lastUpdate]
      ,COUNT(*)                               [Instances]
      ,SUM([mpb].[DurationSeconds])           AS [Total_processingTime]
      ,AVG([mpb].[DurationSeconds])           AS [Ave_time]
      ,SUM(CAST([mpbd].[ColumnValue] AS INT)) AS [recordCount]
      ,CASE
           WHEN (SUM(CAST([mpbd].[ColumnValue] AS INT))) > 0 THEN
               CAST(ROUND(SUM(CAST([mpbd].[ColumnValue] AS INT)) / SUM([mpb].[DurationSeconds]), 2, 1) AS DECIMAL(18, 2))
           ELSE
               NULL
       END                                    AS [Records_Per_Second]
FROM [dbo].[MFProcessBatchDetail]     AS [mpbd]
    INNER JOIN [dbo].[MFProcessBatch] AS [mpb]
        ON [mpb].[ProcessBatch_ID] = [mpbd].[ProcessBatch_ID]
WHERE [mpb].[Status] = 'Completed'
      AND [mpb].[ProcessType] IS NOT NULL
      AND [mpbd].[ColumnName] = 'NewOrUpdatedObjectDetails'
      AND [mpbd].[ColumnValue] <> '0'
GROUP BY [mpb].[ProcessType]
        ,[mpbd].[MFTableName]
        ,[mpb].[LogText]
ORDER BY [mpb].[ProcessType];

--graph by table by session
SELECT [mpb].[ProcessBatch_ID]
      ,[mpb].[LogText]
      ,SUM([mpb].[DurationSeconds])           AS [Total_processingTime]
      ,AVG([mpb].[DurationSeconds])           AS [Ave_time]
      ,SUM(CAST([mpbd].[ColumnValue] AS INT)) AS [recordCount]
      ,CASE
           WHEN (SUM(CAST([mpbd].[ColumnValue] AS INT))) > 0 THEN
               CAST(ROUND(SUM(CAST([mpbd].[ColumnValue] AS INT)) / SUM([mpb].[DurationSeconds]), 2, 1) AS DECIMAL(18, 2))
           ELSE
               NULL
       END                                    AS [Records_Per_Second]
      ,[mpb].[ProcessType]
      ,[mpbd].[MFTableName]
      ,MIN([mpb].[CreatedOn])                 AS [earliestDate]
      ,MAX([mpb].[CreatedOn])                 AS [lastUpdate]
      ,COUNT(*)                               [Instances]
FROM [dbo].[MFProcessBatchDetail]     AS [mpbd]
    INNER JOIN [dbo].[MFProcessBatch] AS [mpb]
        ON [mpb].[ProcessBatch_ID] = [mpbd].[ProcessBatch_ID]
WHERE [mpb].[Status] = 'Completed'
      AND [mpb].[ProcessType] IS NOT NULL
      AND [mpbd].[ColumnName] = 'NewOrUpdatedObjectDetails'
      AND [mpbd].[ColumnValue] <> '0'
GROUP BY [mpb].[ProcessType]
        ,[mpb].[ProcessBatch_ID]
        ,[mpbd].[MFTableName]
        ,[mpb].[LogText]
ORDER BY [mpb].[ProcessType];

--base data
SELECT [mpb].[ProcessType]
      ,[mpbd].[MFTableName]
      ,[mpb].[LogText]
      ,[mpb].[CreatedOn]
      ,[mpb].[DurationSeconds]
      ,[mpbd].[ColumnValue]
      ,*
FROM [dbo].[MFProcessBatchDetail]     AS [mpbd]
    INNER JOIN [dbo].[MFProcessBatch] AS [mpb]
        ON [mpb].[ProcessBatch_ID] = [mpbd].[ProcessBatch_ID]
WHERE [mpb].[Status] = 'Completed'
      AND [mpb].[ProcessType] IS NOT NULL
      AND [mpbd].[ColumnName] = 'NewOrUpdatedObjectDetails'
      AND [mpbd].[ColumnValue] <> '0';