01: Compare hot vs. cold query performance

Task 01: Compare hot vs. cold query performance

Introduction

Microsoft Sentinel’s analytics (hot) tier uses a Log Analytics workspace for fast, recent data queries. The Data Lake (cold) tier extends storage for historical analysis and compliance.

You’ll compare query latency, result counts, and performance between the two tiers.

Description

Run equivalent KQL queries in both tiers to see how data freshness and query depth impact responsiveness.

Success criteria

Queries execute successfully in both tiers.
You observe performance differences between hot and cold storage.
You understand which scenarios are best suited for each tier.

Key steps:

Run a query in the analytics (hot) tier.
Expand here for detailed steps
1. In the Azure portal, go to Microsoft Sentinel > Logs.
2. Run the following query:
```
 SecurityAlert 
 | where TimeGenerated > ago(30d) 
 | summarize Count = count() by AlertSeverity 
 | order by Count desc 
```
3. Observe fast execution (typically under two seconds).
  
  This is hot-tier data only.
Run the same query in the Data Lake (cold) tier.
Expand here for detailed steps
1. In the Defender portal, go to Microsoft Sentinel > Data Lake Exploration > KQL Queries.
2. Run the same query with a longer time window:
```
 SecurityAlert 
 | where TimeGenerated > ago(180d) 
 | summarize Count = count() by bin(TimeGenerated, 7d), AlertSeverity 
 | order by TimeGenerated desc 
```
3. Notice higher latency (10–20 seconds) as the query scans cold storage.
4. Compare result counts and time ranges between both tiers.
  
  Even with a longer lookback period, the Data Lake may return the same amount of data or even fewer results if the workspace or Data Lake integration was recently onboarded. Historical data will only appear once enough time has passed for ingestion and mirroring to accumulate.