Administering Microsoft Azure SQL Solutions354 questions across 36 pages are complete and free, with no account or card.
354 questions in this bank
Free access · progress saved on this device
You have an Azure SQL database named DB1. You need to display the estimated execution plan of a query by using the query editor in the Azure portal. What should you do first?
Answer: A
When SET SHOWPLAN_ALL is ON, SQL Server returns execution information for each statement without executing it, and Transact-SQL statements are not executed.
Source: Adapted from community discussion — Ciupaz
HOTSPOT - You have an Azure SQL database. You have a query and the associated execution plan as shown in the following exhibit.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point. Hot Area:
This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.
Answer: See figure below
Box 1: Key Lookup - The Key Lookup cost is 99% so that is the performance bottleneck. Box 2: nonclustered index - The key lookup on the clustered index is used because the nonclustered index does not include the required columns to resolve the query. If you add the required columns to the nonclustered index, the key lookup will not be required.
Source: Adapted from community discussion — BrenFa101
HOTSPOT - You have SQL Server on an Azure virtual machine that contains a database named Db1. You need to enable automatic tuning for Db1. How should you complete the statements? To answer, select the appropriate answer in the answer area. NOTE: Each correct selection is worth one point. Hot Area:
This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.
Answer: See figure below
Box 1: SET QUERY_STORE = ON (OPERATION MODE = READ_WRITE); Must enable the Query Store. Incorrect: If the server may be Azure SQL or Managed Instance then the response should be SET AUTOMATIC_TUNNIG=AUTO, but as it is a SQL server the Query store needs to be first enabled. Box 2: SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON) To configure individual automatic tuning options via T-SQL, connect to the database and execute the query such as this one: ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON) Setting the individual tuning option to ON will override any setting that database inherited and enable the tuning option. Setting it to OFF will also override any setting that database inherited and disable the tuning option. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable
Source: Adapted from community discussion — [Removed]
You have an instance of SQL Server on Azure Virtual Machines that has a database named DB1. You plan to implement Azure SQL Data Sync for DB1. Which isolation level should you configure?
Answer: B
HOTSPOT - You have SQL Server on an Azure virtual machine. You review the query plan shown in the following exhibit.
For each of the following statements, select yes if the statement is true. Otherwise, select no. NOTE: Each correct selection is worth one point. Hot Area:
This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.
Answer: See figure below
Box 1: No - There is only one query plan available. Force has no effect. Box 2: No - Adding an index will not increase IO usage. Box 3: Yes - The performance would improve. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store
Source: Adapted from community discussion — miguelmol
A data engineer creates a table to store employee information for a new application. All employee names are in the US English alphabet. All addresses are locations in the United States. The data engineer uses the following statement to create the table.
You need to recommend changes to the data types to reduce storage and improve performance. Which two actions should you recommend? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
Answer: AE
A: Money takes less space compared to VARCHAR(20) E: Date takes less space compared to Datetime. Reference: https://docs.microsoft.com/eN-Us/sql/t-sql/data-types/data-types-transact-sql
Source: Adapted from community discussion — Balti
You have an Azure SQL database. You identify a long running query. You need to identify which operation in the query is causing the performance issue. What should you use to display the query execution plan in Microsoft SQL Server Management Studio (SSMS)?
Answer: C
However, for identifying a long-running query and analyzing its execution in real-time, the best choice would be the actual execution plan. This option allows you to see the actual query plan generated by the SQL Server engine during query execution, including the actual number of rows, actual resource consumption, and any potential issues such as missing indexes or inefficient operations.
Source: Adapted from community discussion — duzee
You have a version-8.0 Azure Database for MySQL database. You need to identify which database queries consume the most resources. Which tool should you use?
Answer: C
The Query Store feature in Azure Database for MySQL provides a way to track query performance over time. Query Store simplifies performance troubleshooting by helping you quickly find the longest running and most resource-intensive queries. Query Store automatically captures a history of queries and runtime statistics, and it retains them for your review. It separates data by time windows so that you can see database usage patterns. Data for all users, databases, and queries is stored in the mysql schema database in the Azure Database for MySQL instance. Reference: https://docs.microsoft.com/en-us/azure/mysql/concepts-query-store
Source: Adapted from community discussion — cck21
You have an Azure subscription that contains an Azure SQL database. The database contains a table named table1. You execute the following Transact-SQL statements.
You need to reduce the time it takes to perform analytic queries on the database. Which configuration should you enable?
Answer: D
...This feature, Batch Mode on Rowstore, allows all operators enabled for batch mode to operate as such in SQL Server. What does this mean? It means that query operations can process data faster, more efficiently and mimic what makes columnstore so fast. Instead of reading row by row (row store mode) it can read in chunks i.e. batches of 900 rows at a time. The performance impact of this can be tremendous which effectively uses CPU more efficiently. https://sqlespresso.com/2020/06/16/what-is-batch-mode-on-rowstore-in-sql-server/
Source: Adapted from community discussion — OBIJUAN88
DRAG DROP - You create a new Azure SQL managed instance named SQL1 and enable Database Mail extended stored procedures. You need to ensure that SQL Server Agent jobs running on SQL1 can notify administrators when a failure occurs. Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.
Answer: See figure below
Given answer is correct, 1) Create a Database Mail account 2) Create a profile 3) Enable email notification The database mail configuration wizard would start the profile creation first, however, need to complete the account creation first before the profile creation can be completed. So the order is correct.
Source: Adapted from community discussion — Pranava_GCP
Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page
DP-300 practice, question 221. 354 questions available.
Version 1.0.0. How we source questions