DP-203 Study Guide - Monitor data storage and data processing
Published: 2023-12-05 18:20:01

[Study guide](https://dev.to/aidutcher/dp-203-data-engineering-on-microsoft-azure-study-guide-5h63)

**Implement logging used by Azure Monitor**

- **Azure Monitor key features**
     - **Metrics** - resource utilization, response time, etc
     - **Logs** - leverage Azure Log Analytics to store and query logs
     - **Alerts** - set up alerts based on specific metrics or log data
     - Service maps, analytics insights, workbooks, and more
- **Log Analytics Workspace**
     - Central repository and analytics engine
     - **Collects, stores, and analyzes log data** and other telemetry
- **Diagnostic settings**
     - Feature of Azure Monitor
     - **Control and route diagnostic data** from Azure resources to various destinations
     - Source (metrics, resource logs, activity logs)
     - Destination (Event Hubs, Log Analytics, Azure Storage)

**Configure monitoring services**

- **What can be monitored in Azure Monitor?**
     - **Applications**
     - **Infrastructure** (containers, OS)
     - **Azure Platform** (resources, subscription, tenant)
     - **Custom sources**
- Configure Monitor for Azure Resources
     - Monitoring section of any Azure resource
     - Select "Metrics"
     - Choose scope, metric, visual type, etc
     - Save as Azure Monitor workbook

**Monitor stream processing**

- Monitor Stream Analytics jobs via Azure Monitor in the portal, Powershell, or .NET SDK
- In the portal, select "Metrics" under the Monitoring section
- Can save metrics to a dashboard or send to Azure Monitor workbook

**Measure performance of data movement**

- In the details of a pipeline, there are records of the tasks performed
- Click the eyeglasses symbol on a record to view details about the performance (duration, throughput, start/end time, etc)

**Monitor and update statistics about data across a system**

- Statistics provide **info about how data is distributed** in a table and help the database figure out most efficient way to run a query
- Important for
     - Query performance and optimization
     - Optimal execution plans
     - Index utilization
- **Update statistics daily or after loading/transforming data**
- Enabled at **database level** with ALTER DATABASE database_name SET AUTO_CREATE_STATISTICS ON
- Querying stats data:

```
- Display Query Statistics information
sp_helpstats N'StatisticsTest', 'all'

- Display extra information
SELECT FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = object_id('StatisticsTest');

- Display query details
SELECT * FROM dbo.StatisticsTest
```

**Monitor data pipeline performance**

- Monitor section in ADF or Synapse Studio
- Displays pipeline runs
- Within a pipeline run you can view
     - Consumption
     - Pipeline orchestration (activities performed)
     - Data flow (activity inputs, outputs, etc)
- View in list view or Gantt view

**Measure query performance**

- Tools to measure query performance
     - [Query store](https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver16)
          - Identifies performance differences when query plan changes
     - [Intelligent Insights](https://learn.microsoft.com/en-us/azure/azure-sql/database/intelligent-insights-overview?view=azuresql)
          - Uses AI to continuously monitor database usage to detect disruptive events
          - Detection metrics
               - Query duration
               - Timeout requests
               - Excessive wait time
               - Errored out requests
     - [Dynamic Management Views (DMV)](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views?view=sql-server-ver16)
          - Monitor server health
          - Diagnose problems
          - Tune performance
          - Available via SQL queries
               - sys.dm_pdw_exec_requests
               - sys.dm_exec_requests
               - sys.dm_pdw_request_steps
               - sys.dm_exec_query_plan
               - sys.dm_pdw_waits waits

**Schedule and monitor pipeline tests**

- Using a scheduled trigger
     - Add a new trigger
     - Leave as Schedule type
     - Choose start time and frequency
     - Go to Manage section and view triggered runs

**Interpret Azure Monitor metrics and logs**

- Azure Monitor Metrics
     - Collects numeric data from monitored resources and stores it in a time-series database
     - Allows point-in-time descriptions of resources
- Resources that AMM pulls data from
     - Azure Resources
          - First party services
          - Access to metrics is available by default
     - Azure Monitor Agent - Collects data from OS
     - Application Insights - collects telemetry about specific application workloads
     - REST API - get data in and out of AMM
- Azure Monitor Logs
     - Collect and organize logs and performance data from monitored resources
     - [Log Analytics workspaces](https://learn.microsoft.com/en-us/azure/azure-monitor/logs/log-analytics-workspace-overview)
          - Edit and run log queries
          - Create alerts and workbooks
          - Analyze logs with Kusto Query Language

**Implement a pipeline alert strategy**

- [Set up a pipeline alert in ADF](https://learn.microsoft.com/en-us/azure/data-factory/monitor-metrics-alerts)
     - Provides ability to combine data and business process
     - Configured in the Monitor section under Alerts and Metrics
     - Alert
          - Set target criteria
          - Send out notification to an email or group
          - Send notifications via text, push notification, etc