Study guide
9.4 Query Construct Modifications and Intelligent Insights
DP-300: Administering Microsoft Azure SQL Solutions Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Overview
1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources
2 Planning and Deploying Azure SQL Solutions
2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching
3 Configuring Resources for Scale and Performance
3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression
4 Planning and Implementing a Migration Strategy
4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting
5 Configuring Database Authentication and Authorization
5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management
6 Implementing Security for Data at Rest and in Transit
6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints
7 Implementing Compliance Controls for Sensitive Data
7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL
8 Monitoring Resource Activity and Performance
8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events
9 Monitoring and Optimizing Query Performance
9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights
10 Configuring Database Solutions for Optimal Performance
10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing
11 Creating and Managing SQL Server Agent Jobs
11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates
12 Automating Deployments and Database Tasks in Azure
12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks
13 Planning an HA/DR Strategy and Backup and Restore
13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations
14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups
14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups
15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting
15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions
9.4 Query Construct Modifications and Intelligent Insights
Key Takeaways Explain This Topic AI
SARGable predicates let the optimizer use an index; wrapping an indexed column in a function or leading with a wildcard makes the predicate non-SARGable and forces a scan
Implicit type conversions (e.g., comparing a varchar column to an nvarchar parameter) disable index seeks and force scans; align parameter and column types
Table variables have no statistics and trigger one-row estimates; temp tables have statistics and cardinality estimates - choose temp tables for large intermediate sets
Intelligent Insights is Azure SQL DB and MI built-in AI performance diagnostics that detects regressions, increased waits, lock waits, and DTU/vCore pressure and surfaces them via Azure SQL Analytics in Log Analytics
Intelligent Insights complements Query Store and automatic tuning: it detects and explains patterns Query Store exposes; it does not auto-fix them - automatic tuning does
Last updated: August 2026
SARGable Predicates
A predicate is SARGable (Search Argument-able) when the optimizer can use an index to satisfy it. Non-SARGable predicates force a scan even when a perfect index exists. The patterns that break SARGability:
Non-SARGable pattern Why it scans SARGable rewrite
WHERE UPPER(LastName) = 'SMITH' Function on indexed column prevents seek WHERE LastName = 'SMITH' COLLATE SQL_Latin1_General_CP1_CI_AS (use a case-insensitive collation)
WHERE DATEDIFF(day, Created, GETDATE()) = 0 Function on indexed column WHERE Created >= CAST(GETDATE() AS date) AND Created < DATEADD(day, 1, CAST(GETDATE() AS date))
WHERE LastName LIKE '%son' Leading wildcard cannot navigate the b-tree WHERE LastName LIKE 'son%' (or full-text search)
- WHERE ISNULL(Status, '') = '' Function on indexed column WHERE Status IS NULL OR Status = ''
- WHERE OrderID + 1 = 1001 Arithmetic on indexed column WHERE OrderID = 1000
The rule: keep the indexed column bare on one side of the comparison. Any function, arithmetic, or expression on the indexed column makes the optimizer treat the result as an opaque value and abandon the index.
Implicit Conversions
Implicit type conversion is a subtle, pervasive SARGability killer. When a query compares a column of one type to a parameter or literal of a different type, SQL Server converts one side - and the rules rank nvarchar higher than varchar , so a varchar column compared to an nvarchar parameter gets converted on the column side, disabling index seeks and forcing a scan. -- Bad: @p is nvarchar; ColumnA is varchar; ColumnA is converted, index seek becomes scan
WHERE ColumnA = @p
- - Good: @p is varchar, matching ColumnA; index seek preserved
- WHERE ColumnA = @p
Symptoms: a plan shows an index scan with a CONVERT_IMPLICIT operator feeding the seek predicate; sys.dm_exec_query_stats shows high logical reads on a query that should touch few rows. The fix is to align parameter and column types in the application or stored procedure, or use OPTION (RECOMPILE) so the parameter is sniffed as the right type. The exam often shows a query that runs fast on one database and slowly on another with the same schema - the difference is the parameter type.
Set-Based vs Row-by-Row, and Temporary Structures
Set-based queries let the optimizer batch work across many rows; row-by-row patterns (cursors, scalar UDFs, while loops) prevent set-based optimization and are the most common cause of slow procedural code. Where possible, replace cursors with set-based UPDATE/INSERT/DELETE joins.
For intermediate results, three structures trade off differently:
- Structure Statistics? Cardinality estimate Best for
- #temp table (tempdb) Yes Accurate, recompiled as needed Large intermediate sets with later joins
- @table variable No Always estimated as 1 row Small, recompiled-avoidance sets
- CTE No (inlined) Inlined into outer query Readability and recursion; not a materialization
A common trap: @table_variables have no statistics, so the optimizer assumes 1 row. Inserting 100,000 rows into a table variable and then joining on it produces a nested-loop plan optimized for 1 row - catastrophic. Use #temp_tables for large intermediate sets; use @table_variables only for tiny known-cardinality sets or to avoid recompile overhead. CTEs are syntactic sugar: a non-recursive CTE is inlined and does not materialize, so a CTE referenced twice in one statement is computed twice unless the optimizer spills it to a spool.
Batch Mode on Rowstore and Parameterization
Batch mode on rowstore (introduced in SQL Server 2019, on by default under compatibility level 150+) lets columnstore-style batch processing apply to rowstore tables, speeding up analytic scans of b-tree tables. It is enabled automatically when compatibility level is 150 or higher and is one of the reasons upgrading to a recent database compatibility level is a low-risk performance win.
Parameterization controls how literals become parameters. SIMPLE parameterization (default) parameterizes only trivial predicates; FORCED parameterization treats most literals as parameters, increasing plan reuse but risking plan instability for skewed data. The middle ground is parameterization by template (plan guides) or using stored procedures, which parameterize explicitly.
Query Hints
Hint Effect When to use
OPTION (RECOMPILE) Discards the cached plan, compiles a fresh plan for the current parameter set Parameter-sniffed queries with skewed distributions where compile cost is small relative to execution
OPTION (MAXDOP n) Caps the degree of parallelism for this query A single query that should not consume all CPUs
OPTION (OPTIMIZE FOR UNKNOWN) Uses averaged density estimates, not a specific parameter Stable but not perfect plans across distributions
OPTION (OPTIMIZE FOR (@p = 'value')) Optimizes for a specific representative value When one parameter distribution is most common
OPTION (LOOP JOIN, HASH JOIN) Forces a join strategy Diagnosis only; avoid in production unless verified
USE HINT ('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150') Applies a compatibility level's optimizer behavior to one query Testing an upgrade before changing the database level
Hints are sharp tools: they override the optimizer, so they remain correct even after statistics or data changes that would otherwise produce a better plan. Prefer an index or query-construct change that lets the optimizer do the right thing; reserve hints for cases where the optimizer is consistently wrong.
Intelligent Insights
Intelligent Insights is an Azure SQL Database and Managed Instance feature that uses built-in machine learning to detect performance anomalies and emit human-readable diagnostic descriptions. It complements Query Store (which exposes per-query data) and automatic tuning (which can force plans) by detecting patterns across many queries and across time, then explaining them in plain language.
What Intelligent Insights detects:
Query regressions : a previously fast query or workload that became slow, including new regression types discovered since the feature was last updated.
Increased wait times : a rise in aggregate wait time for specific wait types (lock waits, latch waits, I/O waits, log waits).
Lock and deadlock waits : blocking episodes and deadlocks with the resource types and durations.
DTU or vCore pressure : the database hitting compute, I/O, or log limits and throttling.
Memory pressure and tempdb contention patterns.
Temporal anomalies : a slow period that recurs at a regular interval.
Each detected issue is emitted as a row with a sqlInsights text description, the time window, the affected query hashes (where applicable), and diagnostic metrics. To consume it, you enable Diagnostic Settings on the database (or MI) to send the SQLInsights log to a Log Analytics workspace , then view results in an Azure Monitor workbook or query the AzureDiagnostics table directly with Kusto: AzureDiagnostics
| where Category == "SQLInsights"
| project TimeGenerated, logical_server_name_s, database_name_s, issueId_d, issueType_s, impact_s, sqlInsight_s
- | order by TimeGenerated desc
- Limitations and How It Complements Other Tools
Intelligent Insights has known limits the exam tests:
It detects and explains ; it does not fix . Automatic plan correction and automatic tuning perform the fixes; Intelligent Insights points you at the problem.
It is available only on Azure SQL Database and Azure SQL Managed Instance - not on SQL Server on Azure VMs or on-premises.
It needs Diagnostic Settings configured to a Log Analytics workspace; without that, detection runs but you cannot read the output.
The SQLInsights text description is human-readable but not a SQL fix; you still act on it via Query Store, index changes, or query hints.
Do not confuse the SQLInsights log category (Intelligent Insights, current) with the SQL Insights monitoring solution, which was retired on 31 December 2024. Azure SQL Analytics is likewise a legacy solution no longer in active development; database watcher is the current fleet-monitoring recommendation.
Complementary Role Summary
Tool Detects Fixes
Query Store Per-query regressions, top consumers, plans Manual plan forcing; source data for automatic plan correction
Automatic plan correction Plans where the last good plan beats the current one Automatically forces the good plan; unforces on regression
Intelligent Insights Cross-query patterns: waits, lock waits, DTU pressure, regressions None - surfaces diagnostics only
Log Analytics / database watcher Dashboards over collected monitoring data None - consumption surface
A scenario asking "which tool automatically fixes a regression" answers automatic plan correction; "which tool detects a DTU pressure pattern across the database" answers Intelligent Insights; "which tool exposes per-query plans for manual forcing" answers Query Store. Keeping these roles straight is the section's most testable distinction.