Monitor, configure, and optimize database resources

A production Azure SQL Database is experiencing high CPU usage during peak hours. The database uses the S3 service tier. You need to reduce CPU usage without changing the service tier. Which action should you take?

You manage an Azure SQL Managed Instance with a large database. You notice that the automatic tuning recommendations are not being applied. You need to ensure that automatic tuning is enabled for the instance. Which PowerShell cmdlet should you run?

You have an Azure SQL Database that uses the General Purpose service tier. You notice that the log write throughput is consistently near the limit. What should you do to improve log write performance?

You are monitoring an Azure SQL Database using Query Performance Insight. You see a query with high duration and high CPU usage. The query plan shows a clustered index scan. What is the most likely cause and recommendation?

You need to configure Azure SQL Database to automatically adjust indexing based on workload patterns. Which feature should you enable?

You deploy a new Azure SQL Database and need to ensure that all queries are logged for performance analysis. Which configuration should you enable?

You have an Azure SQL Database with a heavy workload. You notice that the `PAGEIOLATCH_SH` wait is the top wait. Which performance issue does this indicate?

You need to monitor Azure SQL Database performance over time and receive alerts when CPU usage exceeds 80%. Which Azure service should you use?

You have an Azure SQL Database in the Hyperscale service tier. You need to ensure that read-only workloads are offloaded to a readable secondary. Which configuration should you set?

Which THREE metrics should you monitor to detect a memory pressure issue in Azure SQL Database?

Which TWO configurations can help improve the performance of an Azure SQL Database experiencing high `WRITELOG` waits?

Which THREE factors should you consider when choosing between vCore and DTU purchase models for Azure SQL Database performance optimization?

You are the database administrator for a large e-commerce company. The production Azure SQL Database (Business Critical, 16 vCores) hosts the order processing system. Recently, users report that order submissions are slow during peak hours. You examine the wait statistics and find that `LCK_M_IX` waits are the top wait type. You also notice that the stored procedure `usp_PlaceOrder` performs an `UPDATE` on the `Orders` table, and there is a high volume of concurrent transactions. The table has a clustered index on `OrderID` and a nonclustered index on `CustomerID`. The procedure uses serializable isolation level. Which action will most effectively reduce the blocking?

You manage an Azure SQL Database (General Purpose, S2) used by a reporting application. The database has a table `FactSales` with 500 million rows. Queries that aggregate sales by date are slow. The execution plan shows a clustered index scan on `FactSales`. The table has a clustered index on `SaleID` and a nonclustered index on `DateKey`. The queries filter by `DateKey` and `ProductKey`. You need to improve query performance without changing the service tier. Which action should you take?

You are the database administrator for an Azure SQL Managed Instance hosting a data warehouse workload. You notice that the storage space consumed by the database is significantly larger than expected. The database has multiple large tables with clustered columnstore indexes. You suspect that the columnstore indexes have become fragmented and that deleted rows are consuming space. You need to reclaim storage space with minimal impact on query performance during business hours. What should you do?

A company has an Azure SQL Database that experiences periodic performance degradation. The database uses the General Purpose service tier. You need to identify the most common performance bottlenecks. You enable the Query Store and collect data for a week. Which Query Store view should you query to find queries that have the highest total resource consumption over time?

You are monitoring an Azure SQL Database using the Automatic Tuning feature. The database has a workload that is read-intensive. You enable the CREATE INDEX and DROP INDEX options. After a week, you observe that the database has created several new indexes automatically. However, you notice that one of the new indexes is causing increased write latency for an application that performs frequent updates. What should you do to resolve the issue without losing the benefits of automatic tuning for other indexes?

You are responsible for performance tuning of an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has several tables with millions of rows. Users report that a report query that joins four tables is slow. You examine the query execution plan and notice that the database engine is using an Index Spool (Lazy Spool) operator. Which TWO actions should you take to improve query performance? (Choose two.)

You are the database administrator for an Azure SQL Database used by a financial trading application. The database is in the Business Critical service tier with 16 vCores. The application executes thousands of small, high-frequency transactions per second. Recently, the application's response time has increased, and you observe high PAGELATCH_EX waits in sys.dm_os_waiting_tasks. The database is 500 GB with a single data file (tempdb.mdf) and a single log file (tempdb_log.ldf). TempDB is configured with the default settings. You need to reduce PAGELATCH_EX contention in TempDB. What should you do?

A company runs a critical SQL Server database on Azure Virtual Machines. The database experiences intermittent performance degradation during peak hours. The database administrator notices high I/O latency on the data drive. The current disk configuration uses Standard HDDs. What is the most cost-effective action to reduce I/O latency without changing the virtual machine size?

You are monitoring an Azure SQL Database using Query Performance Insight and notice that a specific query has a high average duration and high CPU usage. The query plan shows a clustered index scan on a large table. Which two actions should you take to optimize performance? (Choose two.)

Drag and drop the steps to configure an Azure SQL Managed Instance link for disaster recovery in the correct order.

Drag and drop the steps to configure transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key in Azure Key Vault in the correct order.

Match each Azure SQL Database backup type to its description.

Match each Azure SQL Database monitoring metric to its meaning.

You have a SQL Managed Instance that hosts a critical OLTP database. You notice that the average query wait time has increased significantly over the past hour. You need to identify the top resource waits. What should you use?

You manage an Azure SQL Database with the Premium service tier. You need to reduce storage costs by automatically deleting old audit logs that are older than 90 days. What should you configure?

You are reviewing an Azure SQL Database server's vulnerability assessment settings. The exhibit shows the current configuration. A recent security audit requires that vulnerability assessment scans be enabled and that results be retained for at least 90 days. What should you do?

You have an Azure SQL Database that is experiencing performance degradation. You suspect that parameter sniffing is causing suboptimal execution plans. What should you do to mitigate this issue without changing application code?

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that resource usage has exceeded 90% for the past hour. What is the recommended first step to troubleshoot?

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that during peak hours, the log rate is throttled frequently. You need to reduce log write throttling. What should you do?

You run the KQL query shown in the exhibit. The query returns a list of query IDs. What is the purpose of this query?

You are configuring a private endpoint for an Azure SQL Database. The exhibit shows the current network ACLs. You need to ensure that only traffic from a specific subnet in VNet1 is allowed, and all other traffic is denied. What should you do?

You are monitoring an Azure SQL Database that is experiencing high DTU consumption. You need to identify the queries that are causing high resource usage. Which two data sources can you use? (Choose two.)

You have an Azure SQL Database that is configured with geo-replication. You need to optimize read performance for reporting queries that run on the secondary replica. Which three actions should you take? (Choose three.)

You are troubleshooting a performance issue in an Azure SQL Database. You need to identify the queries that are consuming the most CPU over the last hour. Which two methods can you use? (Choose two.)

You are reviewing the long-term retention (LTR) policy for an Azure SQL Database. The exhibit shows the current policy. You need to ensure that backups are retained for at least 10 years for compliance. What should you do?

You manage an Azure SQL Database that uses the General Purpose service tier. You need to reduce storage costs by archiving old data that is not frequently accessed. The archived data must still be queryable occasionally. What should you do?

You have an Azure SQL Database that is configured with automatic failover groups. During a planned failover, you notice that the failover takes longer than expected. You need to minimize downtime during future planned failovers. What should you do?

You are managing an Azure SQL Database that experiences intermittent performance degradation. Query Store shows a significant increase in wait time for PAGEIOLATCH_SH. You need to identify the most likely cause. What should you investigate first?

You have a SQL Managed Instance with a large database. You notice that the automatic tuning recommendations are not being applied. You need to ensure that automatic tuning is enabled and that recommendations are automatically executed. What should you do?

You are monitoring an Azure SQL Database that is running a mission-critical workload. You notice that the DTU consumption is consistently above 90% during peak hours. You need to recommend a solution to reduce the DTU consumption. What should you recommend?

You are managing an Azure SQL Database that uses Intelligent Insights. You receive an alert that there is a performance issue with a specific query. You need to analyze the root cause. What should you use?

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is consistently high. You need to reduce the log write latency. What should you do?

You are monitoring an Azure SQL Database and notice that the average CPU usage is 80% and the average data IO percentage is 70%. You need to identify the most likely cause of the high resource usage. What should you check first?

You manage an Azure SQL Database that uses a Serverless compute tier. You notice that during idle periods, the database auto-pauses and then auto-resumes when a connection is made. However, users report that the first query after a pause is slow. You need to improve the performance of the first query. What should you do?

You are optimizing an Azure SQL Database that has a large table with a clustered columnstore index. You notice that the index has been fragmented over time. You need to improve query performance. What should you do?

You have an Azure SQL Database that is experiencing high wait times on RESOURCE_SEMAPHORE waits. You need to identify the root cause. What should you check?

You are configuring performance monitoring for an Azure SQL Database. You need to identify which two tools can be used to analyze query performance over time. Which TWO should you select?

You are optimizing an Azure SQL Database that uses the Hyperscale service tier. You need to identify which three actions can improve write performance. Which THREE should you select?

You are monitoring an Azure SQL Database. You need to identify which two metrics are most important for detecting a memory pressure issue. Which TWO should you select?

You are monitoring a critical production Azure SQL Database that is experiencing intermittent query timeouts. The database is configured with the General Purpose service tier. You need to identify the root cause of the timeouts with minimal overhead. What should you review first?

Your Azure SQL Managed Instance is configured with a long-term backup retention policy of 10 years. You need to reduce storage costs while still meeting a compliance requirement to retain monthly backups for 7 years. What should you do?

You are the database administrator for a hybrid environment with on-premises SQL Server 2022 and Azure SQL Database. All databases use the AdventureWorks schema. You notice that a critical stored procedure runs slower on Azure SQL Database than on-premises. Both have identical indexes and statistics. What is the most likely cause?

You are monitoring an Azure SQL Database that hosts a financial application. You notice that the average DTU consumption is 20%, but occasionally spikes to 95% for 5-minute intervals. Users report slow response times during these spikes. You need to ensure consistent performance without over-provisioning resources. What should you do?

Your Azure SQL Database is configured with Active Geo-Replication to a secondary region for disaster recovery. During a routine failover drill, you notice that after failover, the application cannot connect to the new primary because the login credentials fail. The logins are contained in the master database. What is the most likely cause?

You are managing an Azure SQL Database that has Automatic Tuning enabled. You receive an alert that a query plan regression was detected and a plan correction was automatically applied. You want to verify the performance improvement. What should you use?

Your Azure SQL Database is experiencing high CPU usage. You suspect a specific query is causing the issue. You have enabled Query Store. How can you identify the query that has consumed the most cumulative CPU time over the last hour?

You are optimizing a data warehouse workload on Azure SQL Database. The workload involves large batch inserts and nightly aggregations. You notice that the transaction log is growing excessively during the batch inserts, causing performance degradation. You need to reduce log growth without affecting data consistency. What should you do?

Your Azure SQL Database has a recurring job that rebuilds indexes weekly. After a recent change, the job is taking much longer to complete. You suspect that the index fragmentation is higher than usual. What is the most efficient way to check index fragmentation across the database?

You are troubleshooting a performance issue on an Azure SQL Database. Which TWO actions should you prioritize to identify the root cause of high resource consumption?

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the Business Critical service tier. Which THREE configuration changes can improve query performance for reporting queries without significantly impacting OLTP operations?

You are monitoring an Azure SQL Database that is experiencing high DTU usage. Which TWO metrics should you examine to determine whether the bottleneck is CPU or I/O?

Refer to the exhibit. You are analyzing query performance using sys.dm_exec_query_stats. Based on the output, which query is the best candidate for optimization to reduce overall CPU usage?

Refer to the exhibit. You executed the Azure CLI command to list databases. You need to resume db3 to make it available for connections. Which command should you use?

Your organization runs a critical e-commerce application on Azure SQL Database. You notice that during peak hours, query performance degrades significantly. You have enabled Intelligent Insights and Query Store. Which diagnostic metric would you analyze first to identify the root cause of the performance degradation?

You are a database administrator for a large financial services company. You need to ensure that all queries that read sensitive customer data use an optimized execution plan. What feature should you enable to automatically identify and fix regressed query plans?

Your company uses Azure SQL Database with the Hyperscale service tier. You notice that index maintenance operations are taking longer than expected. What is the most likely reason for this performance issue?

You are configuring performance monitoring for Azure SQL Managed Instance. You need to collect and analyze query performance data with minimal overhead. Which solution should you use?

Your Azure SQL Database is experiencing a sudden increase in wait time due to PAGEIOLATCH_SH waits. What should you do to reduce these waits?

You need to configure a long-term retention policy for backups of an Azure SQL Database that must retain weekly full backups for 5 years and monthly full backups for 10 years. Which backup retention feature should you use?

Your company plans to migrate a large on-premises SQL Server database to Azure SQL Managed Instance. The database uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. How should you manage the TDE protector after migration to ensure minimal administrative overhead and high availability?

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert indicating 'Degradation in performance due to increased log write wait time'. What is the most likely cause of this issue?

You need to recommend a performance monitoring solution for a new Azure SQL Managed Instance deployment. The solution must provide historical query performance data and the ability to compare performance before and after index changes. What should you include in the recommendation?

Your Azure SQL Database is experiencing high CPU utilization due to frequent recompilations of a stored procedure. Which TWO actions should you take to reduce recompilations?

You are configuring automatic tuning for an Azure SQL Database. Which THREE recommendations can be applied automatically without manual approval?

You are troubleshooting a performance issue on an Azure SQL Database. Which THREE Dynamic Management Views (DMVs) should you query to identify the most resource-intensive queries?

You are a database administrator for a large e-commerce platform using Azure SQL Database. You notice that a specific query frequently causes high CPU usage during peak hours. The query is a SELECT with multiple JOINs and a WHERE clause on a non-clustered index. You have already updated statistics and rebuilt indexes. What should you do next to optimize performance?

Your Azure SQL Managed Instance is experiencing high latency for write transactions. You have identified that log write latency is the bottleneck. The instance uses Premium SSD with 5000 IOPS and 200 MB/s throughput. You observe that the log file is 500 GB and has grown significantly. What is the most likely cause and solution?

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You want to identify the top queries by total CPU time over the last hour. Which DMV should you query?

Your Azure SQL Database is experiencing deadlocks. You have enabled deadlock graphs in the extended events session. After capturing a deadlock, you need to analyze it to determine which queries are involved. What should you use?

You are configuring automatic tuning for an Azure SQL Database. The database has a heavy OLTP workload. You want to automatically correct query plan choice regressions without manual intervention. Which automatic tuning option should you enable?

You need to monitor the long-running queries in an Azure SQL Database. Which dynamic management view should you query to see queries that have been running for more than 30 seconds?

Your Azure SQL Database is hitting the DTU limit frequently during business hours. You need to identify which queries are consuming the most DTU. What should you do?

You are migrating an on-premises SQL Server database to Azure SQL Database. The database uses Service Broker for asynchronous messaging. After migration, you notice that performance is degraded. What should you do to optimize?

You are troubleshooting a performance issue in Azure SQL Database. You suspect that parameter sniffing is causing suboptimal query plans. What feature can you use to mitigate this without code changes?

Which TWO actions can help you identify and resolve performance bottlenecks related to I/O in an Azure SQL Database?

Which THREE metrics should you monitor to proactively detect potential performance issues in an Azure SQL Database?

Which TWO options are valid methods to optimize query performance in Azure SQL Managed Instance?

The database 'mydb' is experiencing performance issues during peak hours. Based on the exhibit, what is the most likely cause?

You observe that the average of Maximum DTU consumption over the last hour is consistently above 90%. What should you do next?

You manage an Azure SQL Database that experiences periodic performance degradation. You need to identify the top queries by CPU consumption over the last hour. Which dynamic management view should you query?

Your Azure SQL Managed Instance is experiencing high PAGELATCH_SH waits. You need to reduce this contention. What should you implement?

You are troubleshooting a performance issue on an Azure SQL Database. Query Store shows a significant increase in query duration for a specific query. The execution plan has changed from a hash join to a nested loops join. What is the most likely cause?

Refer to the exhibit. You apply this JSON configuration to an Azure SQL Database server. Which actions will be audited?

Refer to the exhibit. You retrieve the configuration of an Azure SQL Database. Based on the JSON output, which feature is enabled?

Refer to the exhibit. You review the configuration of an Azure SQL Database. Which statement about this database is true?

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is consistently high, causing performance issues. Which metric should you monitor to identify the log generation rate?

You administer an Azure SQL Database that uses the General Purpose tier. Users report that queries are slow during peak hours. You need to identify if the slow performance is due to log write latency. Which metric should you examine in Azure Monitor?

You have an Azure SQL Database with Intelligent Insights enabled. You receive an alert that 'SQLInsights: Resource utilization is consistently high'. You need to determine whether the issue is caused by an increase in user workload or a degradation in query performance. Which Intelligent Insights dimension should you review?

You are tuning an Azure SQL Database workload. Which TWO actions can help reduce PAGELATCH_EX contention?

You manage an Azure SQL Managed Instance. You need to monitor storage space usage. Which TWO dynamic management views can you use?

You are troubleshooting a transaction log growth issue on an Azure SQL Database. Which THREE conditions can cause the transaction log to grow unexpectedly?

You have an Azure SQL Database that uses automatic tuning. Which TWO benefits does automatic tuning provide?

You are optimizing an Azure SQL Database that uses the Business Critical tier. Which TWO factors affect the maximum log rate?

You are the database administrator for a large e-commerce application hosted on Azure SQL Database in the Business Critical tier (8 vCores). The database has a table named Orders with 500 million rows. The table is partitioned by OrderDate using a monthly partition function. The application frequently runs queries that aggregate sales by month for the current year. Users report that these queries are slow. You examine the query execution plans and notice that the queries are scanning all partitions, not just the relevant ones. You need to improve query performance without changing the application code. Which action should you take?

A company has an Azure SQL Database that is experiencing performance degradation during peak hours. The database is configured with the Standard tier (S2). Which action should you recommend to improve performance without changing the application code?

You manage an Azure SQL Managed Instance that hosts a critical OLTP database. You notice that the average CPU usage is consistently above 90% during business hours. You have enabled Intelligent Insights, which recommends creating a missing index. What should you do first to validate the recommendation before implementing it?

Your Azure SQL Database is configured with the Hyperscale service tier. You observe that log write latency is consistently high, affecting transaction throughput. What is the most likely cause and the recommended mitigation?

You are monitoring an Azure SQL Database using the sys.dm_db_resource_stats DMV. The avg_log_write_percent column shows 95% for the last hour. What does this indicate, and what should you do?

You have an Azure SQL Database that uses the SQL Server Agent to run a daily maintenance job. The job fails intermittently with the error 'Login failed for user'. The job uses a SQL Server authentication login. What is the most likely cause and solution?

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the General Purpose tier. You notice that many queries are scanning large tables. What is the best first action to improve performance?

Your SQL Server is experiencing deadlocks. You enable trace flag 1222 to capture deadlock graphs in the error log. Where can you retrieve the deadlock information?

You manage an Azure SQL Database that uses automatic tuning. The database has the FORCE_LAST_GOOD_PLAN option enabled. A critical query suddenly starts performing poorly after a plan change. What is the expected behavior?

You need to monitor the storage space usage of an Azure SQL Database over time. Which tool should you use?

Which TWO metrics from sys.dm_db_resource_stats should you monitor to identify a disk IO bottleneck in an Azure SQL Database?

Which THREE actions can help reduce the frequency of parameter-sensitive plan (PSP) problems in Azure SQL Database? (Choose three.)

Which TWO database properties can be configured to optimize performance for a read-heavy workload in Azure SQL Database?

You are managing an Azure SQL Database that is experiencing intermittent performance degradation. Query Store shows that a specific query's execution plan changed, causing increased CPU usage. You need to ensure consistent performance without rewriting the application. What should you do?

Your company uses Azure SQL Database with active geo-replication. You notice that the secondary database in a different region has a high log write latency. Users report that the primary database performance is normal. What is the most likely cause?

You have an Azure SQL Managed Instance with a database that is used for reporting. The reporting queries are read-only and can tolerate some latency. You want to offload the reporting workload from the primary instance to a secondary read-only replica. Which feature should you use?

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that 'Query performance degradation' was detected. After reviewing the details, you find that a specific query now has a higher duration and is using a different execution plan. What is the recommended first step to troubleshoot?

You need to monitor the performance of an Azure SQL Database and set up alerts when the DTU consumption exceeds 80% for more than 5 minutes. Which Azure service should you use?

You have an Azure SQL Database with automatic tuning enabled. You notice that a query that previously ran quickly is now running slower. Automatic tuning has implemented a plan correction. However, the new plan is performing worse. What should you do to revert to the previous behavior?

You are optimizing an Azure SQL Database that has a heavy workload of both reads and writes. The database has a clustered columnstore index on a large fact table. You notice that the index has high fragmentation and the performance of queries against this table is degrading. What should you do to improve performance?

You have an Azure SQL Database that is part of an elastic pool. You notice that the pool's eDTU consumption is consistently high, and some databases are experiencing resource contention. You need to ensure that a critical database always gets a minimum amount of resources. What should you configure?

You are monitoring an Azure SQL Managed Instance using dynamic management views. You want to identify which queries are consuming the most IOPS. Which DMV should you query?

You are reviewing the audit configuration of an Azure SQL Database using the above JSON from Azure Resource Manager. Based on the exhibit, which of the following is true?

You run the above KQL query in Azure Monitor Log Analytics to investigate performance issues in SalesDB. What is the primary purpose of this query?

You are reviewing a deployment template for an Azure SQL Database. The above snippet configures a security alert policy. What is a potential issue with this configuration?

You are tuning an Azure SQL Database that has a heavy write workload. You need to reduce the number of log writes. Which TWO actions should you take? (Choose two.)

You are monitoring an Azure SQL Database and notice that the 'tempdb' database is experiencing contention. Which THREE actions can reduce tempdb contention? (Choose three.)

You need to configure monitoring for an Azure SQL Database to meet the following requirements: - Alert when average DTU consumption exceeds 90% for 10 minutes. - Track failed logins. - Analyze query performance over the last 30 days. Which THREE Azure services or features should you use? (Choose three.)

You manage an Azure SQL Database that is experiencing higher than expected DTU consumption. You need to identify which queries are consuming the most resources. Which dynamic management view should you query?

You have an Azure SQL Managed Instance with a large number of databases. You need to monitor the storage space used by each database to proactively manage capacity. Which tool should you use?

You need to configure automatic tuning for an Azure SQL Database to automatically identify and fix performance issues. Which two tuning options can be enabled?

You are troubleshooting a performance degradation on an Azure SQL Database. You notice that the database is hitting the maximum DTU limit frequently. Which action should you take first to reduce DTU consumption?

You are reviewing an Azure Resource Manager template snippet for configuring long-term backup retention for an Azure SQL Database. The deployment fails with an error indicating the storage account is not accessible. What is the most likely cause?

You are monitoring an Azure SQL Database and notice that the average CPU usage is consistently above 90%. The database is using the S3 service tier. What should you do first to resolve this performance issue?

You need to configure alerts for an Azure SQL Database to notify the operations team when the database exceeds 80% DTU consumption for more than 10 minutes. What should you use?

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is frequently throttled. Which configuration change can help reduce log rate throttling?

You need to monitor the performance of a set of Azure SQL Databases from a single dashboard. Which Azure service should you use?

You are tuning a query in Azure SQL Database. Which TWO actions can reduce logical reads?

You are troubleshooting a performance issue on an Azure SQL Database. The database is experiencing high PAGELATCH_EX waits. Which TWO measures can help reduce these waits?

Which TWO metrics in Azure SQL Database indicate that the database might need to be scaled up?

You are reviewing the configuration of an Azure SQL Database. The database is currently 250 GB in size. You are concerned about storage capacity. What is the maximum size limit for this database?

You run the query in the exhibit on an Azure SQL Database. The result shows high wait_time_ms for PAGEIOLATCH_SH waits. What does this indicate?

You are analyzing query performance in an Azure SQL Database. The query in the exhibit returns a list of queries ordered by total_logical_reads. What does high total_logical_reads typically indicate?

You are a database administrator for a large e-commerce platform using Azure SQL Database. The application experiences intermittent performance degradation during peak hours. Query Store data shows that a specific stored procedure, `usp_getProducts`, has multiple query variants with different execution plans. Some plans are suboptimal. You need to ensure consistent and optimal performance for this stored procedure without rewriting the application code. What should you do?

Your organization runs a critical application on Azure SQL Managed Instance. You notice that the `tempdb` database is experiencing contention, leading to PAGELATCH_EX waits. The managed instance has 16 vCores and is using the General Purpose service tier. You need to reduce `tempdb` contention without altering the application code or changing the service tier. What should you do?

You are monitoring an Azure SQL Database using Intelligent Insights. The built-in intelligence detects a performance issue and suggests a specific index to create. The database is running the Business Critical service tier. You want to automatically implement this recommendation without manual intervention. What should you configure?

Your company uses Azure SQL Database with Active Geo-Replication for disaster recovery. During a routine failover drill, you observe that after failover to the secondary region, the application experiences significantly higher latency for write operations. The secondary database is in a different Azure region and has the same service objective. What is the most likely cause of the increased write latency?

You are configuring workload management for an Azure SQL Database using the JSON exhibit above for a classifier named 'MyWorkloadClassifier'. The classifier is intended to assign high importance to queries from user 'User1' in the 'SalesDB' database. However, after deployment, you notice that queries from 'User1' are not getting the expected resource guarantees. What is the most likely reason?

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You run a query against `sys.dm_exec_query_stats` to find the top 10 queries by total worker time. Several queries show high worker time but low logical reads. The database is not experiencing any blocking or deadlocks. What is the most likely cause of the high worker time?

Your organization uses Azure SQL Database with elastic pools. One of the databases in the pool, 'OrdersDB', consistently uses more eDTUs than other databases, causing performance degradation for other databases. You need to isolate the resource usage of 'OrdersDB' to prevent it from affecting others. The solution should minimize cost and administrative overhead. What should you do?

You are deploying a new application on Azure SQL Database. The application requires that all connections use a specific login, 'AppUser', with the least privileges necessary. The login should only be able to execute stored procedures in the 'Sales' schema and should not have direct access to underlying tables. What should you do?

You manage an Azure SQL Database that is part of an Always On availability group in Azure SQL Managed Instance. You notice that the secondary replica is experiencing high log send queue size. The primary replica has ample CPU and I/O resources. The network latency between replicas is low. What is the most likely cause of the high log send queue?

Which TWO actions can you take to optimize query performance in Azure SQL Database without changing the application code? (Choose two.)

Which THREE factors should you consider when configuring automatic tuning for an Azure SQL Database? (Choose three.)

Which TWO metrics in Azure SQL Database's Intelligent Insights can indicate a performance degradation due to increased resource consumption? (Choose two.)

Which THREE actions can you take to monitor and optimize database resources in Azure SQL Database? (Choose three.)

You are analyzing the exhibit KQL query that queries Azure Diagnostics logs for Query Store runtime statistics. The query is intended to show average CPU time per hour for each database. However, the result shows no data for the last 24 hours, although Query Store is enabled on all databases. What is the most likely reason?

You are a database administrator for a financial services company that runs a critical application on Azure SQL Database in the Business Critical service tier. The database is named 'TransactionsDB' and has a size of 500 GB. The application experiences periodic performance degradation during end-of-month batch processing. Analysis shows that the degradation coincides with high log write activity and increased latency for write transactions. You have already verified that the log rate is within the service tier limits. The batch process performs a large number of INSERT, UPDATE, and DELETE operations on multiple tables. You need to optimize the transaction log performance without changing the application code or the service tier. The database uses the full recovery model and has a log backup every 5 minutes. What should you do?

You manage an Azure SQL Database that runs a customer-facing application. You notice that during peak hours, the DTU consumption reaches 100% and queries experience timeouts. You need to ensure consistent performance without manual intervention. What should you implement?

You are monitoring an Azure SQL Database and notice high PAGELATCH waits. What is the most likely cause?

You are designing a database monitoring solution for a critical Azure SQL Database. You need to retain performance data for 90 days and receive alerts when DTU usage exceeds 80%. Which combination of tools should you use?

The query returns a list of query hashes with high average duration. You need to identify which queries are most likely causing CPU pressure. What additional metric should you include?

You have an Azure SQL Database that is used by a reporting application. Reports run slowly during the day. You notice that the database is at the S1 tier. You need to improve report performance without changing application code. What should you do?

You manage an Azure SQL Database that has automatic tuning enabled. You receive an alert that the database is experiencing plan regression. The automatic tuning has forced a plan, but performance is still poor. What should you do first?

You have an Azure SQL Database in the General Purpose tier. You notice that the log write throughput is consistently above the service tier limit, causing transaction throttling. You need to resolve this without moving to Business Critical. What should you do?

You are monitoring an Azure SQL Database using sys.dm_db_wait_stats. You see a high percentage of WRITELOG waits. What is the most likely cause?

Which TWO actions can reduce storage costs for an Azure SQL Database? (Select two.)

Which THREE factors should you consider when choosing between a Gen5 and a Premium-series hardware configuration for an Azure SQL Database? (Select three.)

Which TWO metrics are available in Azure Monitor for an Azure SQL Database that can be used to set autoscale rules? (Select two.)

You need to optimize costs for SalesDB, which is used only during business hours (8 AM to 6 PM). The database currently runs 24/7. Which change should you make?

You are a database administrator for a large retail company. The company uses an Azure SQL Database in the Business Critical tier (8 vCores, 480 GB storage) to run its core transaction processing system. The database has automatic tuning enabled, including FORCE_LAST_GOOD_PLAN and CREATE_INDEX. You notice that the database is experiencing high CPU usage (90% average) during peak hours, and the Query Store shows that a specific query (Query ID 123) has regressed. The automatic tuning feature has forced a plan for this query, but the performance is still poor. You need to resolve the CPU issue and ensure the query runs efficiently. What should you do first?

You are a database consultant for a financial services company that uses an Azure SQL Managed Instance (MI) in the General Purpose tier (16 vCores, 1024 GB storage) for a critical application. The MI hosts a database that processes large batch transactions every night. Recently, the batch jobs have been failing due to timeout errors. You notice that the log write throughput is hitting the service tier limit (50 MB/s for General Purpose). The business requires the batch to complete within the same time window. You cannot change the application code or move to Business Critical tier due to budget constraints. You need to ensure the batch jobs complete successfully. What should you recommend?

You manage a SQL Server on Azure VM. Users report slow queries during peak hours. You suspect memory pressure. Which metric should you monitor in Azure Monitor to confirm this?

Your Azure SQL Database is experiencing high DTU consumption. You need to identify the top resource-consuming queries. What should you do?

You have an Azure SQL Managed Instance and notice that automatic tuning is not enabled. You want to automatically force a plan that performed better than the existing plan. What should you enable?

Your Azure SQL Database is configured with the Hyperscale service tier. You observe increased redo log latency. Which resource is most likely the bottleneck?

You are tuning a query in Azure SQL Database that uses a nonclustered columnstore index. The query is supposed to use batch mode execution but shows row mode. What is the most likely cause?

You need to configure Azure SQL Database to automatically scale up based on CPU usage. Which feature should you use?

Which TWO metrics should you monitor in Azure SQL Database to detect a potential memory pressure issue?

Which THREE actions can you take to optimize query performance in Azure SQL Database using Intelligent Query Processing?

Which TWO methods can you use to monitor Azure SQL Database wait statistics?

You are reviewing an ARM template for Azure SQL Database. The exhibit shows the database settings. You notice the database is not being automatically paused. What is the most likely explanation?

You are a database administrator for a medium-sized e-commerce company. The company runs its online transaction processing (OLTP) workload on an Azure SQL Database in the General Purpose service tier (DTU-based, S3). The database is used for order processing, inventory management, and customer data. Recently, during peak shopping hours (10 AM to 2 PM), users have reported that order entry forms take several seconds to submit, and inventory queries are timing out. Monitoring shows that DTU usage regularly hits 100% during these hours, with high PAGELATCH_IO waits. You need to resolve the performance issue with minimal cost increase. What should you do?

You manage a critical Azure SQL Database that supports a financial application. The database is configured with the Business Critical service tier (8 vCores) and uses Always On availability groups for high availability. Recently, the application team noticed that some transactions are experiencing high latency during writes. You investigate and find that the log write latency is averaging 10 ms, and there are occasional HADR_SYNC_COMMIT waits. The application requires durable writes with synchronous commit. You need to reduce write latency without compromising data durability. What should you do?

You are responsible for a set of Azure SQL Databases that are used by different departments in your organization. The databases are deployed in an elastic pool with Standard tier (eDTU 200). Usage patterns show that the marketing database uses high CPU during the day, while the sales database uses high IO at night. You want to optimize costs while ensuring each database gets the resources it needs. What should you do?

You administer a large Azure SQL Database that is used for a SaaS application. The database has a table with over 1 billion rows that is frequently queried by customer ID. The table currently has a clustered index on an identity column and a nonclustered index on customer ID. Queries that filter by customer ID are experiencing high IO and long execution times. You analyze the execution plan and see that the nonclustered index is used, but there are many key lookups. You need to optimize the query performance while minimizing storage overhead. What should you do?

You are managing an Azure SQL Database in the General Purpose service tier with 100 DTUs. The database supports an e-commerce application. Over the past week, you notice that CPU usage frequently reaches 100% during peak hours, causing query timeouts. You have identified that the most expensive query is a SELECT statement that joins five tables and returns aggregated sales data. You need to reduce CPU pressure without changing the service tier or adding indexes. What should you do?

You are the database administrator for a company that uses Azure SQL Managed Instance. The instance hosts a mission-critical database that experiences periodic performance degradation. You need to set up a proactive monitoring solution that sends alerts when the average DTU usage exceeds 80% over a 5-minute period. The solution should minimize cost. What should you do?

You are a database administrator for a large financial services company. You manage an Azure SQL Database in the Business Critical tier with a failover group configured for disaster recovery. The database has a heavy OLTP workload. You notice that the secondary replica is experiencing high log write latency, impacting the primary's performance due to synchronous commit. You need to minimize the performance impact on the primary while maintaining disaster recovery capabilities. What should you do?

You are a database administrator for a SaaS company. You manage an Azure SQL Database that uses the Hyperscale service tier. The database supports a multi-tenant application with unpredictable workloads. You notice that the database's page server is experiencing high IOPS utilization, causing query performance degradation. You need to improve performance by optimizing the page server usage. What should you do?

You are a database administrator for an e-commerce company that uses Azure SQL Managed Instance. You have a database that experiences high blocking due to long-running transactions. You need to configure a solution that automatically terminates the blocking process after it has been waiting for more than 5 seconds. The solution should be set at the database level and should not require any application changes. What should you do?

You are optimizing an Azure SQL Database that runs a reporting workload. The database is in the General Purpose tier. You notice that many queries are performing table scans on large tables. Which TWO actions would most likely improve query performance without increasing costs?

You are monitoring an Azure SQL Database. You need to identify which built-in tools can provide real-time performance data without additional cost. Which THREE should you select?

You are configuring performance optimization for an Azure SQL Database that uses the Hyperscale service tier. The database has heavy read-write workloads. Which THREE actions should you take to optimize performance?

You are managing an Azure SQL Database that is used by a real-time analytics application. The database uses the Hyperscale service tier. You notice that the transaction log rate is consistently high, causing performance degradation. You need to reduce the log generation rate without compromising data durability. What should you do?

You are a database administrator for a company that uses Azure SQL Database. You need to configure a diagnostic setting to send database metrics to a Log Analytics workspace for long-term analysis. The solution should be cost-effective and include metrics like CPU percentage, data IO, and log IO. What should you do?

Refer to the exhibit. An Azure SQL Database is experiencing performance degradation. Based on the Extended Events and wait statistics, which is the most likely root cause?

Refer to the exhibit. An Azure SQL Database in the Standard tier (S2: 50 DTU) is consistently showing high DTU consumption. Which action would most effectively reduce DTU usage?

Refer to the exhibit. The SalesDB database is experiencing log space full errors. Based on the exhibit, what is the most likely reason?

Refer to the exhibit. An automatic tuning recommendation to force the last good plan is active. What should the database administrator do next?

Refer to the exhibit. An Azure SQL Database is receiving Intelligent Insights degradation alerts. Which action should be taken first?

Refer to the exhibit. You notice the database db1 is currently 80% full on storage, and the service objective is S3. Which action would best prevent storage full errors while minimizing cost?

Refer to the exhibit. You observe DTU usage reaching 100% at 11:00 AM. Which recommendation would best resolve the performance issue?

Refer to the exhibit. A user reports being unable to connect to the database. What is the most likely cause?

Refer to the exhibit. Which action should you take to improve performance?

Refer to the exhibit. You are managing db1, which is currently in Standard tier. What is the maximum size you can set for db1 without changing the service objective?