DP-300 Monitor, configure, and optimize database resources — All Questions With Answers

Refer to the exhibit.

```json
{
  "properties": {
    "state": "Disabled",
    "emailAccountAdmins": true,
    "emailAddresses": ["dba@contoso.com"],
    "disabledAlerts": ["SqlInjection", "SqlInjectionVulnerability"],
    "retentionDays": 30
  }
}
```

Refer to the exhibit.

```kql
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where Category == "QueryStoreRuntimeStatistics"
| summarize avg(avg_duration) by query_id
| top 10 by avg_duration desc
```

Refer to the exhibit.

```json
{
  "properties": {
    "networkAcls": {
      "defaultAction": "Deny",
      "virtualNetworkRules": [
        {
          "id": "/subscriptions/.../virtualNetworks/vnet1/subnets/subnet1",
          "ignoreMissingVnetServiceEndpoint": false
        }
      ],
      "ipRules": []
    }
  }
}
```

Refer to the exhibit.

```json
{
  "properties": {
    "longTermRetentionPolicy": {
      "weeklyRetention": "P4W",
      "monthlyRetention": "P12M",
      "yearlyRetention": "P3Y",
      "weekOfYear": 1
    }
  }
}
```

Refer to the exhibit.

-- Azure SQL Database automatic tuning policy (JSON)
{
  "properties": {
    "state": "Enabled",
    "options": {
      "forcePlan": {
        "state": "Enabled"
      },
      "createIndex": {
        "state": "Enabled"
      },
      "dropIndex": {
        "state": "Enabled"
      }
    }
  }
}

Refer to the exhibit.

-- Output from sys.dm_exec_query_stats (partial)
-- Query 1: total_worker_time = 50000 ms, execution_count = 100
-- Query 2: total_worker_time = 45000 ms, execution_count = 10
-- Query 3: total_worker_time = 30000 ms, execution_count = 200

Refer to the exhibit. The following Azure CLI output shows the current configuration of an Azure SQL Database:

{
  "name": "mydb",
  "requestedServiceObjectiveName": "S3",
  "currentServiceObjectiveName": "S3",
  "maxSizeBytes": 268435456000,
  "status": "Online",
  "zoneRedundant": false,
  "readScale": "Disabled",
  "autoPauseDelay": 3600
}

Refer to the exhibit. The following KQL query is run in Azure Monitor to analyze performance:

AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL"
| where MetricName == "dtu_consumption_percent"
| where TimeGenerated > ago(1h)
| summarize avg(Maximum) by bin(TimeGenerated, 5m)

Refer to the exhibit. The following PowerShell script is used to configure performance settings:

Set-AzSqlDatabase -ResourceGroupName "rg1" -ServerName "server1" -DatabaseName "db1" -AutoPauseDelay -1

Refer to the exhibit.

{
    "properties": {
        "state": "Enabled",
        "storageEndpoint": "https://mystorage.blob.core.windows.net",
        "retentionDays": 30,
        "auditActionsAndGroups": [
            "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP",
            "FAILED_DATABASE_AUTHENTICATION_GROUP"
        ]
    }
}

Refer to the exhibit.

{
    "kind": "System",
    "properties": {
        "majorVersion": "12.0",
        "preferredEnclaveType": "VBS",
        "serverConfiguration": {
            "isLedgerOn": false,
            "isIntelligentInsightsOn": true
        }
    }
}

Refer to the exhibit.

```json
{
  "properties": {
    "state": "Enabled",
    "location": "West US",
    "properties": {
      "storageContainerPath": "https://mystorageaccount.blob.core.windows.net/sqldb-auditing-logs",
      "retentionDays": 90,
      "auditActionsAndGroups": [
        "DATABASE_LOGOUT_GROUP",
        "DATABASE_ROLE_MEMBER_CHANGE_GROUP",
        "SCHEMA_OBJECT_ACCESS_GROUP"
      ],
      "isAzureMonitorTargetEnabled": true,
      "isStorageSecondaryKeyInUse": false
    }
  }
}
```

Refer to the exhibit.

```kusto
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where Category == "QueryStoreRuntimeStatistics"
| where database_name_s == "SalesDB"
| summarize avg_duration = avg(duration_s) by query_id_d, interval_start_time_t
| where avg_duration > 1000
| order by avg_duration desc
```

Refer to the exhibit.

```json
{
  "type": "Microsoft.Sql/servers/databases/securityAlertPolicies",
  "apiVersion": "2023-08-01-preview",
  "name": "Default",
  "properties": {
    "state": "Enabled",
    "emailAddresses": ["admin@contoso.com"],
    "emailAccountAdmins": true,
    "storageEndpoint": "https://mystorageaccount.blob.core.windows.net",
    "retentionDays": 0
  }
}
```

Refer to the exhibit.
{
  "properties": {
    "state": "Enabled",
    "location": "westus",
    "serverName": "myserver",
    "databaseName": "mydb",
    "storageContainerUri": "https://mystorage.blob.core.windows.net/backups",
    "storageContainerSasToken": "?sv=2018-03-28&..."
  }
}

Refer to the exhibit.
{
  "properties": {
    "databaseName": "mydb",
    "maxSizeBytes": 268435456000,
    "sku": {
      "name": "GP_Gen5_4",
      "tier": "GeneralPurpose",
      "capacity": 4
    }
  }
}

Refer to the exhibit.
SELECT 
  wait_type,
  wait_time_ms,
  waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type = 'PAGEIOLATCH_SH'
ORDER BY wait_time_ms DESC;

Refer to the exhibit.
SELECT 
  plan_handle,
  execution_count,
  total_worker_time,
  total_logical_reads,
  total_elapsed_time
FROM sys.dm_exec_query_stats
ORDER BY total_logical_reads DESC;

Refer to the exhibit.
```json
{
  "properties": {
    "name": "MyWorkloadClassifier",
    "context": {
      "memberName": "AzureSQL\User1",
      "databaseName": "SalesDB"
    },
    "importance": "high",
    "min_percentile_resource": 10,
    "max_percentile_resource": 100,
    "cap_percentile_resource": 100
  }
}

Refer to the exhibit.
```kusto
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where Category == "QueryStoreRuntimeStatistics"
| summarize avg(avg_cpu_time) by DatabaseName, bin(TimeGenerated, 1h)
| render timechart
```

Refer to the exhibit. The following KQL query is used to analyze performance metrics for an Azure SQL Database:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where Category == "QueryStoreRuntimeStatistics"
| summarize avg_duration = avg(duration_s) by query_hash, DatabaseName
| where avg_duration > 1000
| order by avg_duration desc

Refer to the exhibit. The following Azure CLI output shows the configuration of an Azure SQL Database:

{
  "name": "SalesDB",
  "tier": "GeneralPurpose",
  "capacity": 4,
  "family": "Gen5",
  "storage": 1024,
  "auto_pause_delay": 60
}

Refer to the exhibit.

```json
{
  "properties": {
    "databaseSettings": {
      "id": "/subscriptions/.../resourceGroups/rg1/providers/Microsoft.Sql/servers/server1/databases/db1",
      "preferredEnclaveType": "VBS",
      "autoPauseDelay": 60,
      "minCapacity": 0.5,
      "licenseType": "BasePrice"
    }
  }
}
```

Extended Events session trace:

name: system_health
session_id: 5
total_events: 12450
event_name: sql_statement_completed
cpu_time: 2500 ms
logical_reads: 45000
duration: 3000 ms

name: system_health
session_id: 5
total_events: 12451
event_name: sql_statement_completed
cpu_time: 3200 ms
logical_reads: 62000
duration: 4000 ms

Wait statistics from sys.dm_os_wait_stats:

wait_type: PAGEIOLATCH_SH
waiting_tasks_count: 500000
wait_time_ms: 120000

wait_type: LCK_M_S
waiting_tasks_count: 200
wait_time_ms: 500

Azure Monitor metric for Azure SQL Database:

Metric: dtu_consumption_percent
Time range: Past hour
Average: 95%
Max: 100%
Min: 30%

Metric: cpu_percent
Average: 80%
Max: 95%

Metric: physical_data_read_percent
Average: 90%
Max: 100%

Metric: log_write_percent
Average: 20%

Query Store top resource consumers:

Query ID: 1234
Execution count: 5000
Total logical reads: 2.5 million
Total duration: 10,000 ms

Query ID: 5678
Execution count: 100
Total logical reads: 50,000
Total duration: 500 ms

Azure CLI output from 'az sql db list-editions -o table':

Edition       | SLO        | DTU | MaxStorageGB
GeneralPurpose | GP_Gen5_2  | 100 | 512
GeneralPurpose | GP_Gen5_4  | 200 | 512
GeneralPurpose | GP_Gen5_8  | 400 | 1024
BusinessCritical | BC_Gen5_2 | 100 | 512
BusinessCritical | BC_Gen5_4 | 200 | 512

Current database properties:

Name: SalesDB
Edition: GeneralPurpose
ServiceObjective: GP_Gen5_2
Storage used: 480 GB
Max storage: 512 GB

Query Store shows:

Total log write rate: 5 MB/s
Log IO percentage: 80%

sys.dm_db_log_space_usage:

Total log space: 100 GB
Used log space: 95 GB

Azure SQL Database automatic tuning recommendations:

Recommendation ID: 1
Type: CREATE_INDEX
Reason: Improve query performance
Score: 80
State: Active
Impacted queries: 3

Recommendation ID: 2
Type: DROP_INDEX
Reason: Index not used
Score: 90
State: Pending verification
Impacted queries: 0

Recommendation ID: 3
Type: FORCE_LAST_GOOD_PLAN
Reason: Regression detected
Score: 100
State: Active
Impacted queries: 1

Current query performance metrics for impacted query of recommendation 3:

Before plan change: avg CPU 500 ms, avg duration 1000 ms
After plan change: avg CPU 1000 ms, avg duration 2000 ms

Azure SQL Database Intelligent Insights log:

{
  "id": "/subscriptions/.../resourceGroups/.../providers/Microsoft.Sql/servers/.../databases/.../metrics",
  "properties": {
    "startTime": "2024-05-01T10:00:00Z",
    "endTime": "2024-05-01T11:00:00Z",
    "metrics": [
      {
        "name": "cpu_percent",
        "average": 85,
        "max": 99,
        "min": 20
      },
      {
        "name": "physical_data_read_percent",
        "average": 95,
        "max": 100,
        "min": 50
      },
      {
        "name": "log_write_percent",
        "average": 10,
        "max": 15
      }
    ],
    "observations": [
      {
        "type": "Degradation",
        "subType": "ResourceLimit",
        "details": "DTU consumption reached 100% for extended period. Consider scaling up or optimizing queries."
      }
    ]
  }
}

Current database tier: Standard S2 (50 DTU)