Microsoft DP-300 Administering Azure SQL Solutions Exam Dumps and Practice Test Questions Set 13 Q 181-195

Visit here for our full Microsoft DP-300 exam dumps and practice test questions.

Question 181: 

You manage an Azure SQL Database that experiences high CPU utilization during peak business hours. Query Store data shows that a specific stored procedure is causing the issue. The procedure has multiple execution plans, and the most recent plan is inefficient. What should you do to resolve the performance issue?

A) Clear the Query Store data and restart the database

B) Use Query Store to force the previous efficient execution plan

C) Rebuild all indexes on the tables used by the stored procedure

D) Increase the database service tier to add more CPU resources

Answer: B

Explanation:

Query Store in Azure SQL Database provides a powerful capability to identify and resolve query performance regressions by tracking query execution history and plans over time. When a query suddenly experiences performance degradation due to a suboptimal execution plan, Query Store allows you to force a previous, more efficient execution plan without modifying application code or recompiling stored procedures. This feature is specifically designed to address plan regression scenarios where the query optimizer has chosen a poor execution plan compared to historical plans.

To implement this solution, you access Query Store through SQL Server Management Studio or Azure Portal by navigating to the Query Store views. The «Regressed Queries» view identifies queries whose performance has degraded over time by comparing recent execution metrics against historical baselines. Once you identify the problematic stored procedure, you can examine its execution plan history to see all plans the query optimizer has generated. The Query Store interface displays metrics like duration, CPU time, logical reads, and execution count for each plan, making it easy to identify which previous plan performed better. You can then force the efficient plan by right-clicking it and selecting «Force Plan,» which instructs SQL Server to use that specific plan regardless of what the optimizer might otherwise choose.

Forcing plans through Query Store provides immediate performance improvement without requiring schema changes, index modifications, or code rewrites. The forced plan remains in effect even after database restarts or failovers, ensuring consistent performance. Query Store continues monitoring execution metrics, so you can verify that the forced plan resolves the performance issue and consider whether additional optimization steps are needed. This approach is particularly valuable in scenarios where you cannot immediately modify the underlying query or stored procedure, or when optimizer statistics have become skewed causing poor plan selection. Microsoft recommends Query Store as the first tool for diagnosing and resolving query performance issues in Azure SQL Database because it provides both diagnostic data and remediation capabilities in a single feature.

A) is incorrect because clearing Query Store data would eliminate the valuable historical execution plan information needed to identify and force a better plan, and restarting the database doesn’t address the root cause of inefficient plan selection. B) is the correct answer because Query Store allows you to force a previous efficient execution plan, immediately resolving plan regression issues without code or schema changes. C) is incorrect because while rebuilding indexes might help in some scenarios, it doesn’t directly address the immediate problem of an inefficient execution plan that’s already been generated, and it’s a more time-consuming approach than forcing a known good plan. D) is incorrect because increasing service tier adds cost and doesn’t solve the underlying inefficiency; the query would still execute poorly, just with more resources available.

Question 182: 

Your company has an Azure SQL Database that stores financial transaction data. You need to implement a solution that captures all data changes to specific tables for audit purposes. The solution must track INSERT, UPDATE, and DELETE operations with before and after values, and the audit data must be retained for 7 years. What should you implement?

A) Temporal tables with history retention configured for 7 years

B) Change Data Capture (CDC) with custom retention script

C) SQL Server Audit with database audit specification

D) Azure SQL Database Ledger tables

Answer: A

Explanation:

Temporal tables in Azure SQL Database provide a system-versioned approach to tracking complete data history automatically. When you enable temporal tables on specific tables, the system automatically captures the full row state before and after every INSERT, UPDATE, and DELETE operation, storing historical versions in a separate history table. This built-in feature requires minimal configuration, imposes low performance overhead, and integrates seamlessly with standard SQL queries. For audit requirements spanning multiple years, temporal tables provide a robust, maintainable solution that captures the complete data lineage needed for financial compliance and forensic analysis.

To implement temporal tables for audit purposes, you alter existing tables to add the SYSTEM_VERSIONING feature along with two datetime2 columns to track the period of validity for each row version. The syntax involves commands like ALTER TABLE TransactionTable ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo), followed by ALTER TABLE TransactionTable SET (SYSTEM_VERSIONING = ON). Azure SQL Database automatically creates and manages the history table, capturing every version of each row when modifications occur. The history table stores the previous state of rows before updates or deletes, preserving complete before-and-after values without requiring custom triggers or application logic.

For the 7-year retention requirement, you configure the history table with appropriate retention policies. Azure SQL Database supports retention policies on temporal history tables through the HISTORY_RETENTION_PERIOD setting, which can be configured during table creation or modification. You specify the retention period using syntax like HISTORY_RETENTION_PERIOD = 7 YEARS in the system versioning clause. The database engine automatically manages cleanup of historical data older than the retention period, eliminating the need for custom purge scripts. Temporal tables also provide powerful querying capabilities through the FOR SYSTEM_TIME clause, allowing auditors to reconstruct data state at any historical point, query changes between time periods, or retrieve the complete history of specific records. The feature is optimized for performance with columnstore indexing on history tables for large datasets and supports partitioning for managing very large history tables efficiently.

A) is the correct answer because temporal tables provide comprehensive change tracking with before and after values, support 7-year retention policies, and are purpose-built for audit scenarios. B) is incorrect because Change Data Capture tracks changes at the transaction log level but requires significant custom development for retention management, reporting, and doesn’t natively provide the before-and-after query capabilities that temporal tables offer. C) is incorrect because SQL Server Audit tracks security and compliance events but doesn’t capture before-and-after data values for individual row changes; it logs that changes occurred but not the detailed data values. D) is incorrect because Ledger tables provide tamper-evident storage proving data hasn’t been altered, but they’re designed for data integrity verification rather than comprehensive historical tracking with easy before-and-after queries.

Question 183: 

You manage multiple Azure SQL Databases across different Azure regions. You need to implement a backup strategy that meets the following requirements: Point-in-time restore capability for 35 days, geo-redundant backup storage, and the ability to restore databases to a different region in case of regional failure. What should you configure?

A) Configure long-term retention with geo-redundant storage and 35-day retention

B) Use default automated backups with geo-redundant storage

C) Configure automated backups with 35-day retention and geo-redundant storage

D) Implement Azure Site Recovery for database replication

Answer: C

Explanation:

Azure SQL Database provides flexible automated backup configurations that support various retention periods and storage redundancy options. For scenarios requiring point-in-time restore beyond the default retention period and geo-redundant protection, you must explicitly configure the retention period and storage redundancy settings. By configuring automated backups with 35-day retention and geo-redundant storage, you ensure that backups are automatically taken, replicated to a paired Azure region, and retained long enough to meet your recovery requirements while enabling restore operations to alternate regions during disasters.

Azure SQL Database automated backups include full backups weekly, differential backups every 12-24 hours, and transaction log backups every 5-10 minutes. By default, these backups are retained for 7 days for Basic tier databases and 7-35 days for Standard and Premium tiers, though you can configure retention from 1 to 35 days for short-term retention policies. The storage redundancy option determines where backup copies are stored: locally-redundant storage keeps three copies within a single region, zone-redundant storage distributes copies across availability zones, and geo-redundant storage replicates backups to a paired Azure region hundreds of miles away. For disaster recovery scenarios where entire regions might become unavailable, geo-redundant storage is essential.

To configure this solution, you access the database settings in the Azure Portal, navigate to the Backup section, and configure the backup retention period to 35 days. In the backup storage redundancy settings, you select geo-redundant storage to ensure backups are replicated to the paired region. These settings can be configured during database creation or modified for existing databases, though changing storage redundancy requires careful planning as it affects new backups going forward. With this configuration, you can perform point-in-time restore operations to any moment within the 35-day window, either to the same region or to the geo-paired region using geo-restore capabilities. The geo-restore feature uses the geo-replicated backups to create a new database in any Azure region, providing regional disaster recovery without requiring active geo-replication or manual backup management. This approach balances cost, compliance requirements, and disaster recovery capabilities for most business scenarios.

A) is incorrect because long-term retention is designed for backups extending beyond 35 days (up to 10 years) for compliance purposes, and it’s a separate feature from the automated backup retention that provides point-in-time restore capabilities. B) is incorrect because default automated backups only provide 7-day retention, which doesn’t meet the 35-day requirement. C) is the correct answer because configuring automated backups with 35-day retention and geo-redundant storage provides point-in-time restore for 35 days and enables geo-restore to different regions. D) is incorrect because Azure Site Recovery is designed for virtual machine replication, not for Azure SQL Database backup and restore operations.

Question 184: 

You are designing a high-availability solution for an Azure SQL Database that supports a mission-critical application. The solution must provide automatic failover with minimal data loss and read-scale capabilities. The application can tolerate up to 5 seconds of data loss during failover. What should you implement?

A) Auto-failover groups with read-write listener endpoint

B) Active geo-replication with manual failover

C) Zone-redundant database configuration

D) Database copy in a secondary region

Answer: A

Explanation:

Auto-failover groups in Azure SQL Database provide a comprehensive high-availability and disaster recovery solution that combines automatic failover capabilities, connection endpoint management, and read-scale functionality. This feature creates a bidirectional replication relationship between a primary database in one region and secondary databases in other regions, with automatic failover triggered when the primary region experiences an outage. Auto-failover groups meet the stated requirements by providing automated failover with minimal data loss (typically achieving recovery point objectives of less than 5 seconds), and they include read-only endpoint access to secondary replicas for read-scale scenarios where applications can offload reporting or analytical queries.

Auto-failover groups work by establishing active geo-replication between primary and secondary databases, then adding policy-based automatic failover on top of the replication relationship. You configure the failover group through the Azure Portal, PowerShell, or Azure CLI by specifying the primary server, secondary server(s), databases to include, and failover policy settings including grace period before automatic failover. The grace period determines how long the system waits before initiating automatic failover when the primary becomes unavailable, typically configured based on your recovery time objective. The feature provides two connection endpoints: a read-write listener endpoint that always points to the current primary database (automatically updating during failover), and a read-only listener endpoint that directs traffic to secondary replicas for read workloads.

The read-write listener endpoint significantly simplifies application connection logic because applications connect to a consistent endpoint regardless of which region currently hosts the primary database. During failover events, the endpoint transparently redirects connections to the new primary without requiring application changes or DNS updates. The asynchronous replication mechanism underlying auto-failover groups provides minimal performance impact on the primary database while maintaining high data durability. In typical scenarios, the recovery point objective (amount of potential data loss) is less than 5 seconds because transaction log records are replicated to secondaries with minimal lag. The recovery time objective (time to restore service) is typically under 30 seconds for automatic failover, though applications must implement retry logic to handle transient connection failures during the failover process. Auto-failover groups support multiple secondary databases, enabling complex disaster recovery topologies or distributing read workloads across multiple geographic regions.

A) is the correct answer because auto-failover groups provide automatic failover with minimal data loss (meeting the 5-second tolerance), include read-write listener endpoints for simplified application connectivity, and support read-scale through read-only endpoints. B) is incorrect because while active geo-replication provides the underlying replication technology with similar data loss characteristics, it requires manual failover initiation and doesn’t provide the automatic failover policy or managed listener endpoints that simplify disaster recovery operations. C) is incorrect because zone-redundant configuration provides high availability within a single region by distributing replicas across availability zones, but it doesn’t provide geo-redundancy or read-scale capabilities across regions. D) is incorrect because database copy creates a point-in-time copy in another region but doesn’t maintain continuous replication or provide automatic failover capabilities.

Question 185: 

Your Azure SQL Database contains a table with 500 million rows. Queries filtering on the CreateDate column are experiencing poor performance. The column has a non-clustered index, but query execution plans show index scans instead of seeks. Statistics are current, and the column has high cardinality. What should you do to improve query performance?

A) Rebuild the existing non-clustered index with a higher fill factor

B) Create a filtered non-clustered index for the most frequently queried date ranges

C) Partition the table by CreateDate column using monthly partitions

D) Create a clustered columnstore index on the table

Answer: C

Explanation:

Table partitioning is a database design technique that divides large tables into smaller, more manageable pieces called partitions based on a partition key, while maintaining the logical appearance of a single table. For very large tables where queries frequently filter on a specific column like CreateDate, partitioning by that column enables partition elimination, a query optimization where the database engine automatically excludes irrelevant partitions from query execution. With 500 million rows and date-based queries, partitioning by CreateDate using monthly partitions would dramatically reduce the amount of data scanned for typical queries, transforming full table scans or large index scans into much more efficient operations against a single or small number of partitions.

Implementing table partitioning in Azure SQL Database requires several steps. First, you create a partition function that defines how rows are distributed across partitions based on the partition key values. For monthly partitioning on CreateDate, you would create a partition function specifying boundary values for each month: CREATE PARTITION FUNCTION pfCreateDate (datetime2) AS RANGE RIGHT FOR VALUES (‘2023-02-01’, ‘2023-03-01’, ‘2023-04-01’, …). Then you create a partition scheme that maps partitions to filegroups: CREATE PARTITION SCHEME psCreateDate AS PARTITION pfCreateDate ALL TO ([PRIMARY]). Finally, you create a new partitioned table or rebuild the existing table specifying the partition scheme, and recreate indexes aligned with the partition scheme for optimal performance.

Partitioning provides multiple performance benefits for large tables with appropriate access patterns. When queries include predicates on the partition key (WHERE CreateDate >= ‘2024-01-01’ AND CreateDate < ‘2024-02-01’), the query optimizer eliminates partitions outside the specified range, scanning only relevant data. This partition elimination can reduce I/O by orders of magnitude for queries targeting specific time periods. Additionally, partitioning improves maintenance operations like index rebuilds and statistics updates, which can be performed on individual partitions rather than the entire table, reducing lock duration and resource consumption. Partitioning also facilitates data lifecycle management through partition switching, where you can efficiently archive old partitions or load new data by switching entire partitions in metadata-only operations. For the scenario described where date-based filtering is common and the table is very large, partitioning addresses the root cause of poor performance more effectively than index tuning alone.

A) is incorrect because rebuilding with a higher fill factor affects how much free space is left in index pages and may reduce page splits during inserts, but it doesn’t address the fundamental issue of scanning too many rows when filtering on date ranges in a 500-million-row table. B) is incorrect because while filtered indexes can improve performance for specific query patterns, creating multiple filtered indexes for different date ranges is not maintainable, and queries outside the filtered ranges would still perform poorly. C) is the correct answer because partitioning by CreateDate enables partition elimination, dramatically reducing the amount of data scanned for date-based queries in very large tables. D) is incorrect because clustered columnstore indexes are optimized for analytical queries with aggregations and scans, not for transactional queries with selective date range filters that would benefit more from partition elimination.

Question 186: 

You need to monitor and troubleshoot blocking issues in an Azure SQL Database. The database experiences intermittent blocking that affects application performance. You need to identify the queries causing blocking, the duration of blocks, and the resources being blocked. What should you use?

A) Query Store to identify long-running queries

B) Extended Events session capturing blocked_process_report

C) Azure SQL Analytics monitoring workspace

D) Dynamic management views sys.dm_exec_requests and sys.dm_exec_sessions

Answer: B

Explanation:

Extended Events in Azure SQL Database provide a lightweight, flexible performance monitoring framework specifically designed for capturing detailed diagnostic information about database engine behavior, including blocking scenarios. The blocked_process_report event is specifically designed to capture information about blocking situations, including details about the blocking and blocked sessions, the queries involved, resources being blocked, and the duration of blocking. By creating an Extended Events session with the blocked_process_report event, you can collect comprehensive data about blocking incidents for analysis and troubleshooting, even capturing blocking situations that occur intermittently or during off-hours when administrators aren’t actively monitoring.

To implement blocking monitoring with Extended Events, you create a session that captures blocked_process_report events along with related context information. The session definition includes the event, any predicates to filter which blocking scenarios to capture (such as minimum duration thresholds), and actions to collect additional data like SQL text, session details, and wait statistics. You can configure the blocked process threshold using sp_configure to determine how long a process must be blocked before the event fires. The Extended Events session stores captured data in a target such as an event file stored in Azure Blob Storage or a ring buffer in memory for real-time analysis. The captured data includes the complete context needed for troubleshooting: the SQL text of both blocking and blocked queries, session IDs, wait types, resource identifiers, and timestamps.

Extended Events provide several advantages for troubleshooting intermittent blocking issues. Unlike point-in-time queries against dynamic management views which only show currently blocked sessions, Extended Events capture historical blocking incidents for later analysis. The lightweight architecture minimizes performance impact compared to older tracing technologies like SQL Trace or Profiler. You can configure sessions to run continuously, capturing data across extended periods to identify patterns in blocking behavior. The captured data can be analyzed using SQL queries against the event file or visualized in SQL Server Management Studio’s Extended Events viewer, which provides graphical representations of event timelines and details. For Azure SQL Database specifically, Extended Events sessions can be created using T-SQL commands, and the event files can be stored in Azure Blob Storage for long-term retention and analysis. This comprehensive blocking data enables database administrators to identify problematic queries, understand resource contention patterns, and implement targeted solutions like query optimization, index improvements, or isolation level adjustments.

A) is incorrect because while Query Store identifies long-running queries and can show resource consumption, it doesn’t specifically capture blocking relationships, blocked resources, or the session interactions that cause blocking scenarios. B) is the correct answer because Extended Events with blocked_process_report specifically captures comprehensive blocking information including blocking and blocked queries, duration, and resources, enabling thorough troubleshooting of intermittent blocking issues. C) is incorrect because while Azure SQL Analytics provides monitoring dashboards and metrics, it shows aggregate performance data rather than the detailed session-level blocking information needed for troubleshooting specific blocking incidents. D) is incorrect because while dynamic management views show currently blocked sessions in real-time, they don’t capture historical blocking incidents that have already resolved, making them unsuitable for troubleshooting intermittent issues that occur unpredictably.

Question 187: 

Your company is migrating an on-premises SQL Server database to Azure SQL Database. The database uses SQL Server Agent jobs for nightly maintenance tasks including index rebuilds, statistics updates, and data purging. What should you implement to replace SQL Server Agent functionality in Azure SQL Database?

A) Create Azure Automation runbooks with PowerShell scripts

B) Use Elastic Jobs to execute T-SQL scripts on a schedule

C) Implement Azure Functions with timer triggers

D) Use Azure Logic Apps with recurrence triggers

Answer: B

Explanation:

Elastic Jobs is the Azure-native replacement for SQL Server Agent for Azure SQL Database, providing scheduled and on-demand execution of Transact-SQL scripts across single or multiple databases. Elastic Jobs are specifically designed for database administration scenarios, supporting the same types of maintenance operations that SQL Server Agent handled on-premises, including index maintenance, statistics updates, and data cleanup operations. The service provides job scheduling, execution tracking, retry logic, and credential management optimized for database workloads, making it the natural migration path for SQL Server Agent jobs when moving to Azure SQL Database.

To implement Elastic Jobs, you first provision an Elastic Jobs agent, which is a special Azure SQL Database that stores job definitions, schedules, execution history, and credentials. You then define target groups specifying which databases the jobs should execute against, which can include single databases, all databases in an elastic pool, or databases across multiple servers matching specific criteria. Job steps contain the T-SQL scripts to execute, similar to SQL Server Agent job steps. For maintenance tasks like index rebuilds, you would create a job with steps executing ALTER INDEX REBUILD commands or calling system stored procedures. You configure schedules using cron expressions for flexible timing options, enabling complex schedules like «first Sunday of each month» or «every weekday at 2 AM.»

Elastic Jobs provide several advantages for database maintenance scenarios. They execute T-SQL directly against databases with full database engine capabilities, supporting complex maintenance scripts with temporary tables, cursors, and error handling. The jobs service automatically manages transient fault retry logic, critical for cloud environments where temporary connectivity issues may occur. Execution history is stored in the jobs database, providing audit trails and troubleshooting information. Elastic Jobs support parallel execution across multiple databases, which is valuable for managing database fleets at scale. The service also supports output parameters and result set capture, enabling jobs to collect information from target databases for monitoring or reporting purposes. For organizations migrating from on-premises SQL Server, Elastic Jobs provide the closest functional equivalent to SQL Server Agent while leveraging cloud-native scaling, reliability, and management capabilities. The primary trade-off is that Elastic Jobs require additional Azure resources (the jobs agent database) and have slightly different scheduling syntax than SQL Server Agent, but the migration path is straightforward for most maintenance workloads.

A) is incorrect because while Azure Automation can execute PowerShell scripts and connect to Azure SQL Database, it adds unnecessary complexity for database maintenance tasks that can be accomplished with native T-SQL, and it operates outside the database engine rather than leveraging optimized database maintenance commands. B) is the correct answer because Elastic Jobs is the purpose-built Azure service for scheduled T-SQL execution in Azure SQL Database, directly replacing SQL Server Agent functionality for maintenance tasks. C) is incorrect because Azure Functions with timer triggers is designed for general-purpose application code execution and would require custom code to execute database commands, adding unnecessary development and maintenance compared to Elastic Jobs’ native T-SQL support. D) is incorrect because Logic Apps is designed for workflow orchestration and integration scenarios rather than database maintenance, and while it can execute SQL queries, it lacks the database-specific features like transaction handling, parallel execution across databases, and maintenance command optimization that Elastic Jobs provides.

Question 188: 

You manage an Azure SQL Database that uses the vCore purchasing model with the General Purpose service tier. Application performance has degraded, and monitoring shows that log write latency has increased significantly during peak hours. The database is using 60% of allocated compute resources. What should you do to reduce log write latency?

A) Scale up to the Business Critical service tier

B) Increase the number of vCores in the General Purpose tier

C) Enable Read Scale-Out to distribute the workload

D) Reduce the database backup frequency

Answer: A

Explanation:

The service tier selection in Azure SQL Database fundamentally determines the underlying storage architecture and I/O capabilities, which directly impact transaction log write latency. The General Purpose service tier uses remote storage (Azure Premium Storage) for both data and log files, where log writes must traverse the network to reach storage, introducing inherent latency typically in the 5-10 millisecond range. In contrast, the Business Critical service tier uses local SSD storage attached directly to the compute nodes, providing log write latencies typically under 2 milliseconds. For workloads where transaction log write latency is a performance bottleneck, upgrading to Business Critical is the most effective solution as it addresses the root cause through architectural improvements rather than attempting to mitigate symptoms.

The Business Critical service tier provides several architectural advantages beyond log write performance. It implements an Always On availability group architecture with multiple synchronous replicas, where transaction commits wait for acknowledgment from only one secondary replica (not all replicas), maintaining low latency while ensuring high availability. The local SSD storage eliminates network hops for I/O operations, benefiting both log writes and data page access patterns. Business Critical also includes built-in read scale-out capability with up to three readable secondary replicas at no additional cost, allowing read workloads to be offloaded from the primary replica. While this tier is more expensive than General Purpose, it’s the appropriate choice for latency-sensitive OLTP workloads where transaction performance directly impacts user experience or business operations.

In the described scenario, the database is only using 60% of compute resources, indicating that CPU is not the bottleneck. Increasing vCores in the General Purpose tier would add more CPU and memory capacity but would not address the fundamental storage architecture limitation causing log write latency. Log write operations are inherently sequential and cannot be parallelized effectively, so they don’t benefit from additional compute capacity if the storage subsystem is the bottleneck. The Business Critical tier’s local storage architecture provides a direct path to resolve log write latency issues, though organizations should evaluate the cost implications and verify that log write latency is indeed the primary performance bottleneck through monitoring metrics like log write wait statistics. You can monitor log-related waits using the sys.dm_db_wait_stats DMV, looking for wait types like WRITELOG, LOGBUFFER, or LOG_RATE_GOVERNOR to confirm that log writes are the primary bottleneck before making the service tier change.

A) is the correct answer because Business Critical service tier uses local SSD storage that provides significantly lower log write latency compared to General Purpose tier’s remote storage architecture. B) is incorrect because increasing vCores adds compute capacity but doesn’t address the storage architecture limitation causing log write latency in the General Purpose tier. C) is incorrect because Read Scale-Out distributes read workload to secondary replicas but doesn’t affect log write performance on the primary replica where transactions are committed. D) is incorrect because database backup operations use separate I/O channels and don’t directly affect transaction log write latency for user transactions.

Question 189: 

You are implementing security for an Azure SQL Database that contains sensitive customer data. The security requirements mandate that database administrators should not be able to view or access the sensitive data in specific columns containing social security numbers and credit card information. What should you implement?

A) Always Encrypted with deterministic encryption

B) Dynamic Data Masking on the sensitive columns

C) Row-Level Security to filter data based on user context

D) Transparent Data Encryption with customer-managed keys

Answer: A

Explanation:

Always Encrypted is a client-side encryption technology in Azure SQL Database that protects sensitive data by encrypting it on the client side before sending it to the database, and decrypting it on the client after retrieval. The encryption keys never pass through the database engine, which means that database administrators, high-privileged users, and even the cloud provider cannot access the plaintext values of encrypted columns. This technology is specifically designed for scenarios where data must be protected from privileged database users, meeting the requirement that database administrators should not be able to view sensitive customer data like social security numbers or credit card information.

Always Encrypted works by establishing a two-tier key hierarchy. The Column Encryption Key (CEK) encrypts the actual data and is stored encrypted in the database. The Column Master Key (CMK) encrypts the CEK and is stored in a trusted key store outside the database, such as Azure Key Vault, Windows Certificate Store, or hardware security modules. Applications require access to both keys to decrypt data: they retrieve the encrypted CEK from the database, use the CMK to decrypt it, and then use the decrypted CEK to encrypt data before inserting or to decrypt data after retrieving. Database administrators cannot decrypt the data because they don’t have access to the Column Master Key stored in the external key store. The encryption process is transparent to applications using client libraries that support Always Encrypted, though applications must use parameterized queries for encrypted columns.

Always Encrypted supports two encryption types: deterministic and randomized. Deterministic encryption produces the same ciphertext for a given plaintext value, enabling equality searches, grouping, and joins on encrypted columns, though it may reveal patterns in data distribution. Randomized encryption produces different ciphertexts for the same plaintext, providing stronger security but preventing any computations or searches on encrypted columns. For social security numbers and credit card information, you would typically use randomized encryption for maximum security since these fields rarely need to be queried with equality comparisons. The main implementation consideration is that Always Encrypted requires application changes to use compatible client libraries and properly handle encryption operations, and it has limitations on which SQL operations can be performed on encrypted columns. However, for scenarios requiring protection from privileged database users, Always Encrypted is the only technology that provides this guarantee through client-side encryption.

A) is the correct answer because Always Encrypted protects data through client-side encryption, preventing database administrators from accessing plaintext sensitive data while applications can still access it with appropriate keys. B) is incorrect because Dynamic Data Masking only obscures data in query results but doesn’t prevent privileged users from viewing the actual data; database administrators can bypass masking by querying the underlying data directly or modifying the masking rules. C) is incorrect because Row-Level Security filters which rows users can access based on predicates, but it doesn’t prevent authorized users from viewing data in the columns they can access. D) is incorrect because Transparent Data Encryption encrypts data at rest on disk to protect against unauthorized file access, but the data is automatically decrypted when accessed through the database engine, so database administrators can view it normally.

Question 190: 

Your Azure SQL Database supports a global application with users in multiple geographic regions. Users report slow query performance when accessing the application from regions distant from the primary database location. The application performs more read operations than write operations. What should you implement to improve performance for distributed users?

A) Implement Azure Front Door with caching

B) Configure active geo-replication with read-only secondaries in user regions

C) Use Azure Traffic Manager to route users to the nearest database

D) Scale up the primary database to a higher service tier

Answer: B

Explanation:

Active geo-replication in Azure SQL Database creates readable secondary replicas in different Azure regions, enabling applications to route read operations to the geographically nearest replica while write operations continue to execute against the primary database. This approach significantly reduces latency for read-heavy workloads distributed across global regions by minimizing the network distance between users and the database replica serving their queries. Since the scenario describes an application with more read operations than writes and users experiencing slow performance due to geographic distance, active geo-replication with strategically placed read-only secondaries directly addresses the root cause of the performance issue.

Active geo-replication works through continuous asynchronous replication of transaction log records from the primary database to secondary replicas in different Azure regions. Each secondary replica maintains a complete copy of the database that lags behind the primary by typically only seconds, providing near real-time read access to data. Applications connect to secondary replicas using read-only intent in the connection string (ApplicationIntent=ReadOnly) or by explicitly connecting to the secondary replica’s endpoint. The application architecture must implement read-write splitting logic, directing read queries to the nearest secondary and write operations to the primary. Many application frameworks and connection libraries provide built-in support for this pattern, or you can implement custom routing logic based on user location and operation type.

The benefits of active geo-replication for global applications are substantial. Users in each region experience low-latency read access by connecting to local secondaries rather than traversing long network paths to a distant primary database. The solution scales read capacity horizontally by distributing read workload across multiple replicas rather than vertically by adding resources to a single database. Active geo-replication also provides disaster recovery capabilities since secondaries can be promoted to primary during regional outages. You can configure up to four readable secondaries in different regions, enabling a truly global architecture. The primary trade-off is that secondaries have slight data lag due to asynchronous replication, so applications must tolerate eventually consistent reads for non-critical queries while directing queries requiring strong consistency to the primary. For many global applications, this trade-off is acceptable because most read operations can tolerate slight staleness in exchange for dramatically improved performance. Azure SQL Database also provides connection-level read routing through auto-failover groups, which simplify application connection logic by providing a single read-only endpoint that automatically routes to healthy secondaries.

A) is incorrect because Azure Front Door is designed for caching static web content and routing HTTP requests, not for database query optimization; it cannot cache database query results effectively since data changes frequently and query patterns are highly dynamic. B) is the correct answer because active geo-replication creates readable secondaries in multiple regions, allowing read operations to be served locally to users worldwide, dramatically reducing latency for read-heavy workloads. C) is incorrect because Traffic Manager routes network traffic between endpoints but doesn’t create database replicas; routing all users to a single primary database in a distant region still results in high latency for most users. D) is incorrect because scaling up adds more resources to the database but doesn’t address the fundamental issue of network latency between distant users and the database location; the physical distance would still cause slow performance regardless of database capacity.

Question 191: 

You are troubleshooting poor query performance in an Azure SQL Database. A specific query that joins four tables and returns aggregated results takes 45 seconds to execute. The Query Store shows that the same query executed in under 2 seconds last week. Statistics are up to date and no schema changes have occurred. What is the most likely cause?

A) Index fragmentation has increased

B) Parameter sniffing has caused a suboptimal execution plan

C) Database log file has grown too large

D) Tempdb contention is affecting query performance

Answer: B

Explanation:

Parameter sniffing is a phenomenon in SQL Server and Azure SQL Database where the query optimizer creates an execution plan based on the parameter values from the first execution of a parameterized query or stored procedure, then caches and reuses that plan for subsequent executions. When parameter values change significantly, the cached plan may become suboptimal for different parameter value distributions, leading to dramatic performance degradation. The scenario described shows a query that previously performed well but suddenly executes slowly without schema changes or statistics issues, which is a classic indicator of parameter sniffing problems where the cached plan was optimized for different parameter values than those currently being used.

Parameter sniffing occurs because SQL Server’s cost-based optimizer examines parameter values during initial compilation to estimate row counts and choose join strategies, index usage, and other plan elements. For example, if a query first executes with a parameter value that matches only 10 rows, the optimizer might choose a nested loop join and index seeks. If the query later executes with a parameter value matching 1 million rows, the same plan performs terribly because nested loops and seeks are inefficient for large result sets, but a hash join and table scan would be optimal. The problem is particularly acute for queries with multiple parameters or predicates where different value combinations have vastly different cardinalities and optimal access methods.

Several solutions can address parameter sniffing issues. The immediate remediation shown in the scenario is using Query Store to force a better historical execution plan, as discussed in Question 181. For longer-term solutions, you can add the RECOMPILE hint to the query or stored procedure to force plan recompilation on each execution using current parameter values, though this adds compilation overhead. The OPTIMIZE FOR hint specifies which parameter values the optimizer should use for plan compilation, useful when you know typical values. The OPTIMIZE FOR UNKNOWN hint directs the optimizer to use average cardinality estimates rather than specific parameter values, generating more generic plans. You can also refactor stored procedures into smaller procedures with more homogeneous parameter distributions, or implement dynamic SQL that constructs queries based on parameter characteristics. In Azure SQL Database, the Query Store’s automatic plan correction feature can automatically detect plan regressions and force previous good plans, providing a safety net against parameter sniffing issues without manual intervention.

A) is incorrect because while index fragmentation can affect performance, it typically causes gradual degradation rather than sudden dramatic slowdowns, and it wouldn’t explain why performance was good just one week ago without index maintenance occurring. B) is the correct answer because parameter sniffing causes sudden performance changes when execution plans optimized for one set of parameter values are reused with significantly different parameter values, matching the described symptoms. C) is incorrect because log file size doesn’t directly affect read query performance; it impacts write operations and log-related waits but wouldn’t cause a read-heavy aggregation query to slow down dramatically. D) is incorrect because while tempdb contention can affect performance, it typically manifests across many queries system-wide rather than affecting a single specific query, and the scenario indicates this particular query had good performance recently suggesting a query-specific issue rather than a system-wide resource contention problem.

Question 192: 

Your company needs to implement a data classification and protection strategy for Azure SQL Database that complies with GDPR requirements. You need to discover, classify, and label columns containing personal data, and track access to sensitive data. What should you implement?

A) Azure Purview data catalog

B) SQL Data Discovery and Classification with auditing

C) Always Encrypted for all personal data columns

D) Dynamic Data Masking for personal data columns

Answer: B

Explanation:

SQL Data Discovery and Classification is a built-in capability in Azure SQL Database specifically designed for discovering, classifying, labeling, and protecting sensitive data to support compliance with regulations like GDPR, HIPAA, and industry standards. This feature automatically scans databases to identify columns likely containing sensitive data based on column names, data types, and content patterns, then allows you to apply classification labels indicating data sensitivity and information types. When combined with Azure SQL Database auditing, the solution tracks access to classified sensitive data, providing the visibility and governance capabilities required for regulatory compliance while requiring minimal configuration or application changes.

Data Discovery and Classification works through multiple components. The discovery engine scans database schemas and samples data to identify potentially sensitive columns based on built-in rules recognizing patterns like social security numbers, credit card numbers, email addresses, and other personal data types. Administrators review the recommendations and apply classification labels from a hierarchical taxonomy including sensitivity labels (such as Highly Confidential, Confidential, Public) and information type labels (such as Financial, Personal Identifiable Information, Health). These classifications are stored as extended properties on database columns and can be queried programmatically or viewed through Azure Portal, SQL Server Management Studio, or Azure Security Center. The classifications integrate with other Azure SQL security features, automatically applying appropriate protection policies based on sensitivity labels.

When combined with Azure SQL Database auditing, the Data Discovery and Classification feature provides comprehensive sensitive data access tracking. Audit logs capture not only who accessed the database and what queries executed, but also specifically which sensitive classified columns were accessed in query results. This detailed tracking enables security teams to monitor unusual access patterns to personal data, investigate potential data breaches, and demonstrate compliance with regulations requiring access logs for personal information. The audit logs can be sent to Azure Storage, Log Analytics workspace, or Event Hubs for long-term retention and analysis. Advanced threat protection also uses classification metadata to identify anomalous access patterns, such as unusual users accessing large amounts of sensitive data or sensitive data being exported to unusual locations. This integrated approach provides a complete solution for GDPR data protection requirements including data discovery, classification, access control recommendations, and audit trails, all using native Azure SQL Database capabilities without requiring significant custom development or third-party tools.

A) is incorrect because while Azure Purview provides enterprise data governance and cataloging across multiple data sources, SQL Data Discovery and Classification is purpose-built for database-level sensitive data discovery and integrates more tightly with Azure SQL security features and auditing. B) is the correct answer because SQL Data Discovery and Classification combined with auditing provides automated sensitive data discovery, classification labeling, and access tracking specifically designed for compliance requirements like GDPR. C) is incorrect because while Always Encrypted protects data from privileged users, it doesn’t provide the data discovery, classification labeling, and access auditing capabilities required for comprehensive GDPR compliance, and encrypting all personal data may not be necessary or practical. D) is incorrect because Dynamic Data Masking only obscures data in query results and doesn’t provide data discovery, classification, or the detailed access auditing required for compliance tracking.

Question 193: 

You manage an Azure SQL Database that experiences performance degradation during nightly ETL operations. The ETL process loads data using BULK INSERT statements. Query Store data shows that statistics on the target tables become outdated during the load, causing poor execution plans for queries running concurrently with the ETL. What should you do to minimize the performance impact?

A) Update statistics manually after each BULK INSERT completes

B) Enable automatic tuning to optimize execution plans

C) Use TF 2371 trace flag to adjust auto-update statistics threshold

D) Disable auto-update statistics during ETL and update them at the end

Answer: D

Explanation:

Statistics in SQL Server and Azure SQL Database track data distribution in columns and indexes, enabling the query optimizer to estimate row counts and choose efficient execution plans. By default, statistics automatically update when approximately 20% of table rows change (with modifications for larger tables via TF 2371). During large ETL operations using BULK INSERT, tables can undergo massive data changes that trigger multiple automatic statistics updates. These updates occur synchronously in the query path, meaning queries that need statistics wait while updates complete. This causes unpredictable query delays during ETL operations. The optimal approach for ETL scenarios is to temporarily disable automatic statistics updates before the load, perform the data loading without interruption, then manually update statistics once at the end when the data is stable and complete.

To implement this solution, you execute sp_autostats to disable automatic statistics updates on the target tables before beginning the ETL process: EXEC sp_autostats ‘TableName’, ‘OFF’. This prevents the automatic update mechanism from triggering during BULK INSERT operations, eliminating the synchronous wait times that degrade concurrent query performance. You execute the BULK INSERT statements which now complete faster without statistics update overhead. After all data loading completes, you manually update statistics on the affected tables using UPDATE STATISTICS TableName WITH FULLSCAN to ensure the optimizer has accurate cardinality information reflecting the final data state. Finally, you re-enable automatic statistics updates with EXEC sp_autostats ‘TableName’, ‘ON’ to restore normal behavior for ongoing operations. This approach minimizes performance impact by consolidating statistics updates into a single operation after ETL completes rather than allowing multiple updates during the load.

This strategy provides several advantages for ETL scenarios. It eliminates unpredictable query delays caused by automatic statistics updates triggering at arbitrary points during data loading. It reduces overall statistics update overhead by performing one comprehensive update instead of multiple incremental updates. It ensures that post-ETL statistics accurately reflect the final data distribution rather than intermediate states during loading. The approach does introduce a risk window where statistics may be outdated if queries execute against the tables after data loading but before manual statistics updates complete, so you should schedule the manual UPDATE STATISTICS immediately after data loading finishes. For Azure SQL Database specifically, you can also leverage database scoped configurations to control auto-update statistics behavior and use asynchronous statistics updates (AUTO_UPDATE_STATISTICS_ASYNC = ON) as an alternative approach that updates statistics in the background rather than synchronously in query paths, though this introduces slight delay before queries benefit from updated statistics.

A) is incorrect because updating statistics manually after each BULK INSERT statement would be operationally complex and wouldn’t prevent automatic statistics updates from still triggering during the inserts based on row count thresholds. B) is incorrect because automatic tuning primarily addresses plan regression issues by forcing good historical plans, but it doesn’t prevent the synchronous statistics update delays during ETL that are causing the performance degradation. C) is incorrect because TF 2371 adjusts the threshold for when automatic updates trigger on large tables, but it doesn’t prevent the synchronous updates during ETL; it only makes them less frequent, which doesn’t fully address the performance impact during intensive data loading. D) is the correct answer because temporarily disabling automatic statistics updates during ETL and performing a single manual update afterward eliminates synchronous update delays and provides optimal performance for both the ETL process and concurrent queries.

Question 194: 

You are designing a disaster recovery solution for a mission-critical Azure SQL Database. The solution must meet the following requirements: Recovery Point Objective (RPO) of 1 hour, Recovery Time Objective (RTO) of 4 hours, ability to restore the database to a point in time within the last 35 days, and cost optimization. What should you implement?

A) Active geo-replication with manual failover

B) Auto-failover groups with automatic failover

C) Automated backups with geo-redundant storage and point-in-time restore

D) Zone-redundant configuration with automated backups

Answer: C

Explanation:

For disaster recovery scenarios where the Recovery Point Objective allows up to 1 hour of potential data loss and Recovery Time Objective allows several hours for service restoration, Azure SQL Database’s automated backup features with geo-redundant storage provide a cost-effective solution that meets the requirements without the additional expense of maintaining continuously replicating secondary databases. Automated backups capture full backups weekly, differential backups every 12-24 hours, and transaction log backups every 5-10 minutes, enabling point-in-time restore to any moment within the retention period. With geo-redundant backup storage, backups are asynchronously replicated to a paired Azure region, providing protection against regional disasters while the frequent transaction log backups ensure the actual RPO is typically much better than the 1-hour requirement.

Automated backups with point-in-time restore capabilities work through Azure’s backup infrastructure which automatically manages backup scheduling, retention, and storage. The transaction log backups occurring every 5-10 minutes ensure that in a disaster scenario, you can restore to within approximately 10 minutes of data loss under normal circumstances, well within the 1-hour RPO requirement. The geo-redundant storage option replicates backups to the geo-paired region asynchronously, typically with less than 15 minutes of lag, ensuring backup availability even if the primary region experiences a catastrophic failure. When disaster recovery is needed, you initiate a geo-restore operation through Azure Portal, PowerShell, or Azure CLI, specifying the target region and point in time to restore. The geo-restore process creates a new database in any Azure region from the geo-replicated backups, typically completing within 1-4 hours depending on database size, meeting the 4-hour RTO requirement.

The cost advantage of this approach compared to active geo-replication or auto-failover groups is significant. Geo-redundant backup storage incurs only storage costs for the backup data, which is typically much less than the cost of maintaining a full secondary database replica running continuously. You don’t pay for compute resources on the secondary until disaster recovery is actually needed and you perform the geo-restore operation. This makes the solution ideal for scenarios where the longer RTO is acceptable and cost optimization is important. The approach also satisfies the 35-day point-in-time restore requirement by configuring backup retention to 35 days. The main trade-off compared to active geo-replication is the longer RTO since geo-restore requires creating a new database from backups rather than failing over to an existing running secondary. However, for many business scenarios where the application can tolerate several hours of downtime during regional disasters and where 1-hour potential data loss is acceptable, the cost savings make automated backups with geo-redundant storage the optimal choice.

A) is incorrect because active geo-replication provides much lower RPO and RTO than required (typically seconds to minutes) at higher cost due to continuously running secondary replicas, making it overengineered for these requirements. B) is incorrect because auto-failover groups with automatic failover provide even more stringent RPO/RTO (automatic failover within seconds to minutes) and are unnecessary when 1-hour RPO and 4-hour RTO are acceptable, representing higher costs for capabilities beyond requirements. C) is the correct answer because automated backups with geo-redundant storage and point-in-time restore meet the RPO, RTO, and retention requirements while optimizing costs by avoiding the expense of continuously running secondary database replicas. D) is incorrect because zone-redundant configuration provides high availability within a single region but doesn’t protect against regional disasters, so it wouldn’t meet the disaster recovery requirement even though it includes automated backups.

Question 195: 

Your Azure SQL Database contains a stored procedure that executes multiple UPDATE statements affecting thousands of rows. Users report that the procedure occasionally fails with deadlock errors. You need to minimize deadlock occurrences without changing the application logic. What should you do?

A) Add ROWLOCK hint to all UPDATE statements

B) Change the transaction isolation level to READ UNCOMMITTED

C) Ensure all UPDATE statements access tables in consistent order and add appropriate indexes

D) Increase the database service tier to provide more resources

Answer: C

Explanation:

Deadlocks in database systems occur when two or more transactions hold locks on resources that other transactions need, creating a circular waiting condition where no transaction can proceed. The database engine detects these situations and terminates one transaction as a deadlock victim to allow others to complete. The most effective way to prevent deadlocks is through defensive design that ensures transactions acquire locks in consistent order across the system and hold locks for minimal duration. When UPDATE statements access tables in a predictable, consistent sequence and use appropriate indexes to minimize the number of locks and lock duration, deadlock probability decreases significantly. This approach addresses the root cause of deadlocks rather than attempting to mitigate symptoms or accepting dirty reads.

Implementing consistent lock ordering requires analyzing your stored procedure and related procedures to identify table access patterns. All procedures that update multiple tables should access them in the same sequence—for example, always updating TableA before TableB before TableC. If a procedure updates ParentTable and ChildTable, it should always acquire locks on ParentTable first. This ordering prevents circular wait conditions where one transaction locks TableA then tries to lock TableB while another transaction locks TableB then tries to lock TableA. You document these ordering conventions and enforce them through code reviews and development standards. Additionally, adding appropriate indexes on columns used in UPDATE statement WHERE clauses ensures that SQL Server can quickly locate the specific rows to update using index seeks rather than scanning large ranges of rows and holding broader locks. Covering indexes that include UPDATE columns can further reduce lock duration by eliminating key lookups.

This approach also involves minimizing transaction scope and duration. Long-running transactions hold locks longer, increasing deadlock probability. Breaking large operations into smaller batches, committing intermediate results, and releasing locks frequently reduces contention. For example, instead of updating 100,000 rows in a single transaction, update 1,000 rows at a time in separate transactions. You can also consider using SNAPSHOT isolation level for read operations involved in update decisions, as snapshot isolation uses row versioning rather than shared locks, eliminating read-write blocking. While READ UNCOMMITTED similarly avoids read locks, it allows dirty reads which may violate data integrity requirements. Proper indexing deserves particular emphasis because missing indexes force SQL Server to acquire more locks to locate rows, extending lock duration and increasing deadlock probability. Using sys.dm_db_missing_index_details and analyzing deadlock graphs in Extended Events or SQL Server Profiler helps identify specific index improvements that would reduce lock contention for your workload.

A) is incorrect because ROWLOCK hint requests row-level locks but doesn’t guarantee them, and explicitly requesting finer-grained locks can sometimes increase deadlocks by creating more lock escalation scenarios or holding more individual locks that participate in deadlock chains. B) is incorrect because READ UNCOMMITTED eliminates locks on read operations but doesn’t address locks from UPDATE statements which are the likely deadlock participants, and it introduces dirty read risks that may violate business logic requirements. C) is the correct answer because accessing tables in consistent order prevents circular wait conditions and appropriate indexes minimize lock duration and granularity, addressing the root causes of deadlocks. D) is incorrect because deadlocks are caused by lock ordering and contention patterns rather than resource constraints; adding more CPU or memory doesn’t prevent transactions from acquiring locks in conflicting orders that create deadlock conditions.