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 a Microsoft SQL Server 2019 database named DB1 and an Azure SQL managed instance named SQLMI1. You need to move a SQL Server Agent job from DB1 to SQLMI1. Which job attribute is unsupported in SQLMI1?
Answer: D
DRAG DROP - You create an Azure SQL managed instance and a job that performs backups. You need to configure the job to notify a distribution group by email when the job fails. The solution must minimize administrative effort. 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. NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
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.
You have an Azure SQL managed instance. You need to configure the SQL Server Agent service to email job notifications. Which statement should you execute?
Answer: C
Correct. To send e-mail using SQL Agent jobs in SQL Managed Instance, there should be a profile that must be called AzureManagedInstance_dbmail_profile. Otherwise, SQL Managed Instance will be unable to send emails via SQL Agent. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance?view=azuresql#sql-agent-job-notifications
Source: Adapted from community discussion — dryd
You deploy a database to an Azure SQL Database managed instance. You need to prevent read queries from blocking queries that are trying to write to the database. Which database option should set?
Answer: D
D is correct If READ_COMMITTED_SNAPSHOT is set to OFF (the default on SQL Server), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.
Source: Adapted from community discussion — Pranava_GCP
You have an Azure SQL database. You discover that the plan cache is full of compiled plans that were used only once. You run the select * from sys.database_scoped_configurations Transact-SQL command and receive the results shown in the following table.
You need relieve the memory pressure. What should you configure?
Answer: C
Option C is correct The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused. Article: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option?view=sql-server-ver15
Source: Adapted from community discussion — klever
You have SQL Server on an Azure virtual machine that contains a database named DB1. You view a plan summary that shows the duration in milliseconds of each execution of query 1178902 as shown in the following exhibit:
What should you do to ensure that the query uses the execution plan which executes in the least amount of time?
Answer: A
yes, force execution plan for 1221065 is low. So we are forcing 1220917 to use change the query plan (to use 1221065 plan). Its a word game guys
Source: Adapted from community discussion — kcocaep
HOTSPOT - You have an Azure SQL database named DB1. The automatic tuning options for DB1 are configured as 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 - By default CREATE INDEX is disabled. It is here configured as INHERIT so it is disabled. Box 2: No - By default DROP INDEX is disabled. Box 3: Yes - FORCE LAST GOOD PLAN (automatic plan correction) - Identifies Azure SQL queries using an execution plan that is slower than the previous good plan, and queries using the last known good plan instead of the regressed plan. Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable
Source: Adapted from community discussion — humpy
You have an Azure SQL database named DB1. You run a query while connected to DB1. You review the actual execution plan for the query, and you add an index to a table referenced by the query. You need to compare the previous actual execution plan for the query to the Live Query Statistics. What should you do first in Microsoft SQL Server Management Studio (SSMS)?
Answer: C
Answer should be D. C does not allow comparing a Live query statistics, only 2 save query plans. D allows you to compare a previous query plan against a plan of a running query. https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-usage-scenarios?view=sql-server-ver16
Source: Adapted from community discussion — Ben999
You have an Azure SQL database. Users report that the executions of a stored procedure are slower than usual. You suspect that a regressed query is causing the performance issue. You need to view the query execution plan to verify whether a regressed query is causing the issue. The solution must minimize effort. What should you use?
Answer: C
Did you read the Microsoft documentation before posting that - because if you had you'd know there is nothing mentioned about being able to view an execution plan via QPI..... https://learn.microsoft.com/en-us/azure/azure-sql/database/query-performance-insight-use?view=azuresql your welcome
Source: Adapted from community discussion — Dalamain
You have an Azure SQL database. The database contains a table that uses a columnstore index and is accessed infrequently. You enable columnstore archival compression. What are two possible results of the configuration? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.
Answer: BE
Compressing columnstore indexes with archival compression, causes the index to perform slower than columnstore indexes that do not have the archival compression. Use archival compression only when you can afford to use extra time and CPU resources to compress and retrieve the data. The benefit of archival compression, is reduced storage, which is useful for data that is not accessed frequently. For example, if you have a partition for each month of data, and most of your activity is for the most recent months, you could archive older months to reduce the storage requirements.
Source: Adapted from community discussion — eric0718
Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page
DP-300 practice, question 231. 354 questions available.
Version 1.0.0. How we source questions
