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 Microsoft 365 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 Microsoft 365 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 Microsoft 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 Microsoft 365 / App Insights API is the bottleneck and the SQL cleanup below will not help.

3. Compare to previous metrics

3.1 Measure table size and row counts

Run this query against the analytics database to get a per-table breakdown of row count and disk space. The output is sorted largest-first so the tables eating the most space - and most likely to be slowing imports down - are at the top:

SELECT
    t.NAME as TableName,
    p.rows as [Rows],
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.is_ms_shipped = 0
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    TotalSpaceMB DESC, t.Name

Use this to answer two different questions:

• "What needs attention right now?" - The top of the list tells you exactly which table the Clean Old Data Data.sql script (section 4) will benefit most.
• "Am I importing data I don't actually care about?" - If, for example, audit_event_exchange_props or one of the yammer_*_activity_log tables is huge but nobody in the organisation uses those reports, you can turn that import area off in the control panel and free up substantial space and import time. The largest tables map directly to import areas:

If this table is huge ...	... and you don't need that data, turn off
hits, searches, sessions	SharePoint web tracking (AITracker)
audit_events, event_meta_sharepoint	SharePoint audit
event_meta_azure_ad, audit_event_azure_ad_props	Azure AD audit import
event_meta_exchange, audit_event_exchange_props	Exchange audit import
event_meta_general	General audit import
teams_channel_stats_log*, teams_channel_tabs_log, team_membership_log, teams_addons_log	Teams Graph import
onedrive_*_activity_log	OneDrive usage
outlook_user_activity_log	Outlook usage
sharepoint_user_activity_log	SharePoint usage reports
teams_user_activity_log	Teams usage reports
yammer_*_activity_log	Yammer usage reports

3.2 Track the trend over time

Snapshot the result of the query above periodically (e.g. weekly into a CSV, a separate perf_table_sizes_history table, or a Log Analytics custom table) and diff successive runs. Tables with the largest MB / week growth, combined with the App Insights duration trend from section 2.1, tell you whether to clean up (section 4), disable an unused import area, or scale up SQL (see Monitoring section 2.4.4).

When the database is clearly growing and the Finished activity import chart is trending upwards, 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)	Run table-size query (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 Microsoft 365 / App Insights API throttling
App Service CPU spiking to 100% during imports	Azure Portal App Service metrics	Ease up the importers via ImportAggressiveness (set Gentle) and/or scale the plan up
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