Monitoring System Performance

This page covers how to monitor the performance of the analytics engine itself: how quickly imports complete, how to compare today's run against historical baselines, and how to keep the SQL database from slowing down as it grows.

If you are looking for general health alerting (DTU%, CPU, "no recent data" rules, etc.), see the Monitoring page instead.

1. Why monitor performance?

Each web-job runs on a continuous loop and re-imports data from the M365 audit/usage APIs into SQL. Two things tend to slow this loop down over time:

• Volume of incoming data - more users, more sites, more audit events per cycle.
• Size of the SQL database - merges, lookups and de-duplication queries get slower as tables grow.

If a single import cycle starts taking longer than 24 hours, the backlog from M365 will grow faster than the importer can drain it and data will start being missed. The goal of this page is to catch that trend early.

2. Monitor how quickly imports are completing

2.1 The "Finished activity import" log line

At the end of every Office 365 activity import cycle, ProgramTasks.DownloadActivityData writes a single Application Insights trace that includes the wall-clock duration:

Finished activity import. Time taken in = 12.43 minutes. Stats: ...

This is the easiest signal to track. Run this in your App Insights / Log Analytics workspace to see a time series of cycle durations:

traces
| where operation_Name == "Office365ActivityImporter"
| where message startswith "Finished activity import"
| extend MinutesTaken = toreal(extract(@"Time taken in = ([0-9\.]+) minutes", 1, message))
| project timestamp, MinutesTaken, message
| order by timestamp desc

Render it as a chart to spot the trend:

traces
| where operation_Name == "Office365ActivityImporter"
| where message startswith "Finished activity import"
| extend MinutesTaken = toreal(extract(@"Time taken in = ([0-9\.]+) minutes", 1, message))
| summarize avg(MinutesTaken), max(MinutesTaken) by bin(timestamp, 1d)
| render timechart

If avg(MinutesTaken) is creeping up week-on-week without a corresponding rise in tenant activity, that is the signal to investigate database performance (see section 4).

2.2 Cycle-to-cycle frequency

A healthy importer logs Waiting 2 mins... between cycles. If the gap between successive "Finished activity import" entries grows, the cycle itself is taking longer:

traces
| where operation_Name == "Office365ActivityImporter"
| where message startswith "Finished activity import"
| order by timestamp asc
| extend GapMinutes = datetime_diff('minute', timestamp, prev(timestamp))
| project timestamp, GapMinutes

2.3 Per-stage timings (App Insights importer & Graph saves)

Several inner stages also log their own elapsed time. These are useful when a cycle is slow and you want to know which step is the bottleneck:

Log message (contains)	What it measures
Day {yyyy-MM-dd} completed in ...s	A single day of App Insights data (page-views + events)
API fetch completed in ...s	Pulling raw data from the App Insights API
Page-views SQL save completed in ...s	Staging + merge of page-views (hits)
Events SQL save completed in ...s	Staging + merge of custom events
Hits batch imported and merged in ...s	Single hits batch end-to-end
Search merge completed in ...s	Searches merge step
Startup: duplicate-hit cleanup completed in ...s	Once-per-startup hit de-duplication

Example query - average SQL save time per day:

traces
| where message startswith "Page-views SQL save completed"
| extend Seconds = toreal(extract(@"in ([0-9\.]+)s", 1, message))
| summarize avg(Seconds), max(Seconds) by bin(timestamp, 1d)
| render timechart

If Page-views SQL save or Hits batch imported and merged is the stage that is growing, the database is the bottleneck. If API fetch is the slow stage, the M365 / App Insights API is the bottleneck and the SQL cleanup below will not help.

3. Compare to previous metrics

3.1 sys_telemetry_reports table

Every 24 hours the UsageStatsManager snapshots the size of every table in the database and writes the JSON report to sys_telemetry_reports (the same payload that is optionally uploaded to Microsoft if telemetry is enabled, but it is always saved locally too).

The JSON includes a TableStats array - one row per user table - with TableName, Rows and TotalSpaceMB. This is the historical record you compare against.

Latest report:

SELECT TOP 1 submitted, report
FROM sys_telemetry_reports
ORDER BY submitted DESC;

Diff the two most recent reports (row counts per table):

;WITH latest AS (
    SELECT TOP 2 submitted, report
    FROM sys_telemetry_reports
    ORDER BY submitted DESC
),
parsed AS (
    SELECT
        submitted,
        ROW_NUMBER() OVER (ORDER BY submitted DESC) AS rn,
        j.TableName,
        j.[Rows],
        j.TotalSpaceMB
    FROM latest
    CROSS APPLY OPENJSON(report, '$.TableStats')
        WITH (
            TableName     nvarchar(200) '$.TableName',
            [Rows]        bigint        '$.Rows',
            TotalSpaceMB  decimal(18,2) '$.TotalSpaceMB'
        ) j
)
SELECT
    n.TableName,
    o.[Rows]          AS PreviousRows,
    n.[Rows]          AS CurrentRows,
    n.[Rows] - o.[Rows]                 AS RowDelta,
    o.TotalSpaceMB    AS PreviousMB,
    n.TotalSpaceMB    AS CurrentMB,
    n.TotalSpaceMB - o.TotalSpaceMB     AS MBDelta
FROM parsed n
LEFT JOIN parsed o ON o.TableName = n.TableName AND o.rn = 2
WHERE n.rn = 1
ORDER BY MBDelta DESC;

Tables to watch closely - these are normally the largest and the first to cause slowness:

• hits, searches, sessions - web-traffic
• audit_events, event_meta_sharepoint, event_meta_azure_ad, event_meta_exchange, event_meta_general
• audit_event_azure_ad_props, audit_event_exchange_props
• teams_channel_stats_log, teams_channel_stats_log_keywords, teams_channel_stats_log_langs, teams_channel_tabs_log, team_membership_log, teams_addons_log
• onedrive_usage_activity_log, onedrive_user_activity_log, outlook_user_activity_log, sharepoint_user_activity_log, teams_user_activity_log
• yammer_user_activity_log, yammer_group_activity_log, yammer_device_activity_log

3.2 Pair the size diff with the duration trend

The two queries above are complementary:

• sys_telemetry_reports diff answers "is the database getting bigger?"
• App Insights Finished activity import chart answers "is the importer getting slower?"

When both lines are climbing together, you are at the point where the cleanup script in section 4 should be run (or your SQL tier increased - see Monitoring section 2.4.4).

4. Clean-up script for growing data

The repository ships with a maintenance script that archives data older than one month from the hits/audit/usage tables. As the dataset grows this is what you run to keep the importer fast.

Script: src/Clean Old Data Data.sql

4.1 What it does

It computes a single cut-off date - one month before "now" - and deletes everything older than that from:

Area	Tables affected
Web tracking	hits, then orphaned rows in searches and sessions
Azure AD audit	audit_event_azure_ad_props, event_meta_azure_ad
Exchange audit	audit_event_exchange_props, event_meta_exchange
SharePoint audit	event_meta_sharepoint
General audit	event_meta_general
Teams	teams_addons_log, teams_channel_stats_log, teams_channel_stats_log_keywords, teams_channel_stats_log_langs, teams_channel_tabs_log, team_membership_log
Usage activity	onedrive_usage_activity_log, onedrive_user_activity_log, outlook_user_activity_log, sharepoint_user_activity_log, teams_user_activity_log, yammer_device_activity_log, yammer_group_activity_log, yammer_user_activity_log

The cut-off is set at the top of the script:

declare @archiveDateMax datetime
set @archiveDateMax = dateadd(month, -1, GETDATE())

Edit that line if you want to keep more (or less) history. For example, to keep 6 months use dateadd(month, -6, GETDATE()).

4.2 Recommended way to run it

1. Stop the web-jobs first (or at least pause the importer) so that nothing is writing to the same rows while you delete.

2. Open the script in SSMS / Azure Data Studio against the analytics database.

3. Test inside a transaction first - the script ships with the transaction lines commented out for exactly this purpose:

   begin transaction archive
   -- ... run the script body ...
   rollback transaction archive   -- verify counts, then re-run with commit

4. Inspect the session count before / after session clean: PRINT output and run row counts against the affected tables.

5. When happy, re-run with commit transaction archive.

6. Restart the web-jobs.

4.3 After the clean-up: reclaim space and refresh statistics

DELETE does not shrink data files and leaves index fragmentation behind. For the importer to actually go faster again, follow the delete with:

-- Rebuild indexes on the largest cleaned tables
ALTER INDEX ALL ON hits                          REBUILD;
ALTER INDEX ALL ON sessions                      REBUILD;
ALTER INDEX ALL ON searches                      REBUILD;
ALTER INDEX ALL ON audit_events                  REBUILD;
ALTER INDEX ALL ON event_meta_sharepoint         REBUILD;
ALTER INDEX ALL ON event_meta_azure_ad           REBUILD;
ALTER INDEX ALL ON audit_event_azure_ad_props    REBUILD;
ALTER INDEX ALL ON event_meta_exchange           REBUILD;
ALTER INDEX ALL ON audit_event_exchange_props    REBUILD;
ALTER INDEX ALL ON event_meta_general            REBUILD;

-- Refresh statistics so the query planner picks the right plans
EXEC sp_updatestats;

On Azure SQL Database DBCC SHRINKDATABASE is generally not recommended - it increases fragmentation. Trust the index rebuild + autogrowth headroom instead.

4.4 Schedule it

Once you have run the script manually a couple of times and are confident in the retention window you want, schedule it. Options:

• An Azure SQL Elastic Job or SQL Agent job (if your tier supports it).
• A small Azure Automation runbook invoking Invoke-Sqlcmd against the database.
• A scheduled Azure Function with a timer trigger.

Running it weekly is usually enough; running it more often than the importer cycle gains nothing.

4.5 Other clean-up scripts in the repo

For the rarer one-off cases, the repo also ships:

• src/Clean Data By User StoredProc.sql - delete all activity associated with a specific user (e.g. for GDPR / right-to-be-forgotten requests). Not a performance tool.
• src/AnalyticsEngine/Common/Entities/Migrations/Clean Duplicate Urls.sql and Clean Duplicate Sessions.sql - one-off de-duplication scripts that were used when fixing past bugs. Only run these if you are following specific guidance for a known issue.

5. Quick checklist

Symptom	First check	Action
Import cycle taking longer over time	KQL Finished activity import trend (2.1)	Compare sys_telemetry_reports (3.1); run Clean Old Data Data.sql (4)
Page-views SQL save / Hits batch ... merged slow	Per-stage timings (2.3)	DB-bound - cleanup + index rebuild (4.3)
API fetch slow	Per-stage timings (2.3)	Not a DB issue - check M365 / App Insights API throttling
DTU% pinned at 100%	Azure Portal SQL metrics	Upscale DB tier (see Monitoring 2.4.4)
Cycle interval > 24h	KQL gap query (2.2)	Cleanup + upscale - data loss risk