Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 31:

  You have an Azure SQL database named DB1.

  You need to query the fragmentation information of data and indexes for the tables in DB1.

  Which command should you run?

  A. sys.dm_db_index_usage_stats

  B. DBCC CHECKALLOC

  C. DBCC SHOWCONTIG

  D. sts.dm_db_index_physical_stats

  Correct Answer: D

  sys.dm_db_index_physical_stats (Transact-SQL)

  Returns size and fragmentation information for the data and indexes of the specified table or view in SQL Server. For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the

  IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. If row-overflow data exists in the table, one row is returned for the

  ROW_OVERFLOW_DATA allocation unit in each partition.

  Reference:

  https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql

• Question 32:

  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?

  A. ROW_MODE_MEMORY_GRANT_FEEDBACK

  B. BATCH_MODE_MEMORY_GRANT_FEEDBACK

  C. BATCH_MODE_ADAPTIVE_JOINS

  D. BATCH_MODE_ON_ROWSTORE

  Correct Answer: D

  batch mode on rowstore. This feature unlocks the advantages of batch mode execution in cases where there is no columnstore participating in the query (as in the case in this question).

  Note: Batch mode is a different execution mode primarily targeted at analytics queries which are characterized as scanning many rows, and doing significant aggregations, sorts, and group-by operations across these rows. Batch mode has

  been reserved for queries which involve columnstore indexes until now.

  Performing scans and calculations using batches of ~ 900 rows at a time rather than row by row is much more efficient for analytic-type queries. For queries that can take advantage of it, batch mode can easily make queries execute many

  times faster than the same query against the same data in row mode.

  Incorrect:

  Not A: ROW_MODE_MEMORY_GRANT_FEEDBACK

  Row mode memory grant feedback expands on the memory grant feedback feature by adjusting memory grant sizes for both batch and row mode operators.

  Key feature benefits:

  Reduce wasted memory. For an excessive memory grant condition, if the granted memory is more than two times the size of the actual used memory, memory grant feedback will recalculate the memory grant. Consecutive executions will

  then request less memory.

  Decrease spills to disk. For an insufficiently sized memory grant that results in a spill to disk, memory grant feedback will trigger a recalculation of the memory grant. Consecutive executions will then request more memory.

  Not B: BATCH_MODE_MEMORY_GRANT_FEEDBACK

  Not applicable since no columnstore index is used.

  Adaptive Query Processing – Batch Mode Memory Grant Feedback

  Enter the Batch Mode Memory Grant Feedback, introduced in SQL Server 2017. This process provides a feedback mechanism to the cached plan for the actual amount of memory used, and updates the cached plan for future use. The first

  thing to talk about is “Batch Mode”. In Batch Mode, operators process the data in groups of rows. This is a complicated way of saying that the query needs to use a ColumnStore index somewhere. Without a ColumnStore index, the query will

  be processed in Row Mode.

  The feedback mechanism is triggered when one of the following occurs:

  A spill to disk has occurred

  The memory grant is at least two times greater than the actual memory used.

  When one of these conditions occurs, the memory grant is recalculated, and the cached plan is updated with the new memory grant values. Since this updates the cached plan, this feature is designed for workloads that repeat the SQL

  statement, usually with different parameter values.

  Not C: batch mode adaptive joins

  Not applicable since no columnstore index is used.

  Reference: https://techcommunity.microsoft.com/t5/azure-sql-blog/introducing-batch-mode-on-rowstore/ba-p/386256

  https://techcommunity.microsoft.com/t5/azure-sql-blog/public-preview-of-row-mode-memory-grant-feedback-in-azure-sql/ba-p/386224

  https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing

  https://techcommunity.microsoft.com/t5/sql-server-blog/introducing-batch-mode-adaptive-joins/ba-p/385411

• Question 33:

  You have an Azure subscription that contains two instances of SQL Server on Azure Virtual Machines named VM1 and VM2. Both instances run Microsoft SQL Server 2019 CU8.

  You need to deploy a failover cluster instance (FCI) to VM1 and VM2 that will use Azure shared disks. The solution must maximize resiliency.

  Which quorum option should you use?

  A. node majority with a cloud witness

  B. node majority with no witness

  C. node majority with a file share witness

  D. node majority with a disk witness

  Correct Answer: D

  Configure quorum

  Since the disk witness is the most resilient quorum option, and the FCI solution uses Azure shared disks, it's recommended to configure a disk witness as the quorum solution.

  Reference: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/failover-cluster-instance-azure-shared-disks-manually-configure

• Question 34:

  You have an Azure SOI database named SQLDb1 that contains the resources shown in the following table.

  

  Column1 contains JSON data.

  You need to compress Column1. The solution must minimize the amount of storage used.

  What should you use?

  A. the COMPRESS()function

  B. columnstore archive compression

  C. row compression

  D. columnstore compression

  Correct Answer: A

  The COMPRESS (Transact-SQL) function compresses the input expression, using the GZIP algorithm. The function returns a byte array of type varbinary(max).

  Syntax: COMPRESS ( expression )

  Arguments

  expression

  A

  binary(n)

  char(n)

  nchar(n)

  nvarchar(max)

  nvarchar(n)

  varbinary(max)

  varbinary(n)

  varchar(max)

  or

  varchar(n)

  expression

  Incorrect:

  The max size for a column of type NVARCHAR(MAX) is 2 GByte of storage. Since NVARCHAR uses 2 bytes per character, that's approx. 1 billion characters.

  A table cannot be enabled for compression when the maximum row size plus the compression overhead exceeds the maximum row size of 8060 bytes. For example, a table that has the columns c1 CHAR(8000) and c2 CHAR(53) cannot be

  compressed because of the additional compression overhead.

  Compression has no effect on Nvarchar columns, except for Page compression.

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/compress-transact-sql

• Question 35:

  You have an Azure subscription that contains 20 Azure SQL databases.

  You create a Transact-SQL statement to perform index maintenance on a database.

  You need to schedule the statement to run once daily against each database by using Transact-SQL commands.

  What should you use to schedule the statement?

  A. an Azure function

  B. a SQL Server Agent Job

  C. an elastic job

  D. Azure Automation

  Correct Answer: C

• Question 36:

  You have an Azure SQL database named DB1.

  You need to ensure that DB1 will support automatic failover without data loss if a datacenter fails. The solution must minimize costs.

  Which deployment option and pricing tier should you configure?

  A. Azure SQL Database Business Critical

  B. Azure SQL Database Standard

  C. Azure SQL Database managed instance General Purpose

  D. Azure SQL Database Hyperscale

  Correct Answer: A

  By default, the cluster of nodes for the premium availability model is created in the same datacenter. With the introduction of Azure Availability Zones, SQL Database can place different replicas of the Business Critical database to different

  availability zones in the same region. To eliminate a single point of failure, the control ring is also duplicated across multiple zones as three gateway rings (GW). The routing to a specific gateway ring is controlled by Azure Traffic Manager

  (ATM). Because the zone redundant configuration in the Premium or Business Critical service tiers does not create additional database redundancy, you can enable it at no extra cost. By selecting a zone redundant configuration, you can

  make your Premium or Business Critical databases resilient to a much larger set of failures, including catastrophic datacenter outages, without any changes to the application logic. You can also convert any existing Premium or Business

  Critical databases or pools to the zone redundant configuration.

  Incorrect Answers:

  Note:

  There are several versions of this question with different incorrect answer options. Other incorrect answers you may see on the exam include:

  1.

  Azure SQL Database Hyperscale

  2.

  Azure SQL Database Basic

  3.

  Azure SQL Database managed instance General Purpose

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla

• Question 37:

  You have an Azure subscription.

  You plan to deploy an instance of SQL Server on Azure Virtual Machines by using an Azure Marketplace image.

  You need to register the SQL Server IaaS Agent extension (SqlIaasExtension). The solution must meet the following requirements:

  1.

  Install critical updates for SQL Server automatically.

  2.

  Minimize performance impact on the virtual machine. Which management mode should you select?

  A. full

  B. lightweight

  C. NoAgent

  Correct Answer: A

  You can choose to register your SQL IaaS extension in three management modes:

  *

  Full mode installs the SQL IaaS Agent to the VM to deliver full functionality. Use it for managing a SQL Server VM with a single instance. Full mode installs two Windows services that have a minimal impact to memory and CPU - these can be monitored through task manager. There is no cost associated with using the full manageability mode. System administrator permissions are required. As of September 2021, restarting the SQL Server service is no longer necessary when registering your SQL Server VM in full management mode.

  Note: The SQL Server IaaS Agent extension allows for integration with the Azure portal, and depending on the management mode, unlocks a number of feature benefits for SQL Server on Azure VMs:

  Feature benefits: The extension unlocks a number of automation feature benefits, such as portal management, license flexibility, automated backup, automated patching and more.

  Incorrect:

  *

  NoAgent mode is dedicated to SQL Server 2008 and SQL Server 2008 R2 installed on Windows Server 2008. There is no impact to memory or CPU when using the NoAgent mode. There is no cost associated with using the NoAgent manageability mode, the SQL Server is not restarted, and an agent is not installed to the VM.

  *

  Lightweight mode copies extension binaries to the VM, but does not install the agent. Lightweight mode only supports changing the license type and edition of SQL Server and provides limited portal management. Use this option for SQL Server VMs with multiple instances, or those participating in a failover cluster instance (FCI). Lightweight mode is the default management mode when using the automatic registration feature, or when a management type is not specified during manual registration. There is no impact to memory or CPU when using the lightweight mode, and there is no associated cost.

  Reference: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-iaas-agent-extension-automate-management

• Question 38:

  You have an on-premises Microsoft SQL Server 2019 database named SQL1 that uses merge replication.

  You need to migrate SQL1 to Azure.

  Which service should you use?

  A. Azure SQL Edge

  B. Azure SQL Database

  C. SQL Server on Azure Virtual Machines

  D. Azure SQL Managed instance

  Correct Answer: C

  Merge Replication is only supported by SQL Server.

  Incorrect:

  Not A: Azure SQL Edge is an optimized relational database engine geared for IoT and IoT Edge deployments.

  Not B: Azure SQL Database does not support merge replication.

  Not D: Azure SQL Managed Instance does not support merge replication.

  Reference:

  https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-transactional-overview

  https://learn.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication

• Question 39:

  You have an Azure virtual machine named VM1 that runs Windows Server 2022 and hosts a Microsoft SQL Server 2019 instance named SQL1. You need to configure SQL! to use mixed mode authentication. Which procedure should you run?

  A. sp_addremoteIogin

  B. xp_instance_regwrite

  C. sp_cnarge_users_login

  D. xp_grant_login

  Correct Answer: B

• Question 40:

  You have SQL Server 2019 or an Azure virtual machine that runs Windows Server 2019.

  The virtual machine has 4 vCPUs and 28 GB of memory.

  Vou scale up the virtual machine to 8 vCPUs and 64 GB of memory.

  You need to reduce tempdb contention without regatively affecting server performance.

  What is the number of secondary data files that you should configure for tempdb?

  A. 2

  B. 4

  C. 8

  D. 64

  Correct Answer: B

  Correct answer is B, because you should have 8 data files for 8vCPU in total, but you already have some data files and you just need to add/configure extra secondary files