Microsoft 70-764 Exam Questions and Answers 2021

NEW QUESTION 1
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You maintain a Microsoft SQL Server instance that contains the following databases SalesDb1, SalesDb2, and SalesDb3. Each database has tabled named Products and Sales. The following table shows the configuration of each database.

The backup strategies for each database are described in the following table.

Each full or differential backup operation writes into a new file and uses a different sequence number. You observe the following database corruption issues.

SalesDb3 reports a number of database corruption issues related to error 823 and 824 when reading data pages. You must display the following information about the corrupted pages:
database name
impacted file id
impacted file physical name
impacted page id
event type that identifies the error type
error count
Users report performance issues when they run queries against SalesDb2. You plan to monitor query statistics and execution plans for SalesDb2 by using Query Store. The monitoring strategy must meet the following requirements:
Perform automatic data cleanup when query store disk usage reaches 500 megabyte (MB).
Capture queries based on resource consumption.
Use a stale query threshold value of 60 days.
The query optimizer generates suboptimal execution plans for a number of queries on the Sales table in SalesDb2. You will create a maintenance plan that updates statistics for the table. The plan should only update statistics that were automatically created and have not been updated for 30 days. The update should be based on all data in the table.
You need to monitor query statistics and execution plans for SalesDb2.
Which options should you set for the Query Store configuration? To answer, select the appropriate options in the answer area.

Answer:

Explanation: Operation Mode: Read-Write Query Store Capture Mode: Auto Size Based Cleanup Mode: AUTO Operation Mode: Read-Write
It is strongly recommended to activate size-based cleanup to makes sure that Query Store always runs in read-write mode and collects the latest data.
Query Store Capture Mode: Auto
Auto – Infrequent queries and queries with insignificant compile and execution duration are ignored. Thresholds for execution count, compile and runtime duration are internally determined.
Size Based Cleanup Mode: AUTO
It is strongly recommended to activate size-based cleanup to makes sure that Query Store always runs in read-write mode and collects the latest data.
OFF – size based cleanup won’t be automatically activated.
AUTO - size based cleanup will be automatically activated when size on disk reaches 90% of max_storage_size_mb. This is the default configuration value.
Size based cleanup removes the least expensive and oldest queries first. It stops at approximately 80% of max_storage_size_mb.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-query-store-option https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store#set-the

NEW QUESTION 2
You have a database named DB1. You complete a full backup on January1, 2021 to a backup set named DB1_Backup. You create a differential backup January 2, 2021 to the same backup set. You perform transaction log backups each day at 1:00 PM.
DB1 experiences a catastrophic failure.
You need to restore the database to January 3, 2021 at 11:00 AM.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segment from the list of Transact-SQL segments to the answer area and arrange them in the
correct order.

Answer:

Explanation: This example restores a database, differential database, and transaction log backup of the MyAdvWorks
database. Step 1:
-- Assume the database is lost at this point. Now restore the full
-- database. Specify the original full database backup and NORECOVERY.
-- NORECOVERY allows subsequent restore operations to proceed. RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1 WITH NORECOVERY; GO
Step 2:
-- Now restore the differential database backup, the second backup on
-- the MyAdvWorks_1 backup device. RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1 WITH FILE = 2, NORECOVERY;
Step 3:
-- Now restore each transaction log backup created after
-- the differential database backup. RESTORE LOG MyAdvWorks FROM MyAdvWorks_log1 WITH NORECOVERY;
GO
RESTORE LOG MyAdvWorks FROM MyAdvWorks_log2 WITH RECOVERY;
GO
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-differential-database-backup-

NEW QUESTION 3
You are designing a SQL Server database for an order fulfillment system. You create a table named Sales.Orders by using the following script:

Each order is tracked by using one of the following statuses:
Fulfilled
Shipped
Ordered
Received
You need to design the database to ensure that you can retrieve the status of an order on a given date. The solution must ensure that new statuses can be added in the future.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

• A. To the Sales.Orders table, add a column named Status that will store the order statu
• B. Update the Status column as the order status changes.
• C. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Statu
• D. Insert new rows into the table as the order status changes.
• E. Implement change data capture on the Sales.Orders table.
• F. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate.Update the value of each column from null to the appropriate date as the order status changes.

Answer: A

NEW QUESTION 4
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have an on-premises server that runs Microsoft SQL Server 2021 Standard Edition. You need to identify missing indexes.
What should you use?

• A. Activity Monitor
• B. Sp_who3
• C. SQL Server Management Studio (SSMS) Object Explorer
• D. SQL Server Data Collector
• E. SQL Server Data Tools (SSDT)
• F. SQL Server Configuration Manager

Answer: D

Explanation: Data Collector can gather performance information from multiple SQL Server instances and store it in a single repository. It has three built-in data collecting specifications (data collectors) designed to collect the most important performance metrics. The information collected by default is about disk usage, query statistics, and server activity.
The Query Statistics data collection set collects information about query statistics, activity, execution plans and text on the SQL Server instance.
Missing indexes can be found with the execution plans.
References: https://www.sqlshack.com/sql-server-performance-monitoring-data-collector/

NEW QUESTION 5
Note: This question is part of a series of questions that use the same scenario. For your convenience, the
scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You are a database administrator for a company that has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases, and each customer uses a dedicated instance. The environments that you manage are shown in the following table.

You need to configure monitoring for Tailspin Toys.
In the table below, identify the monitoring tool that you must use for each activity.
NOTE: Make only one selection in each column.

Answer:

Explanation: Monitoring from application: Transact-SQL
Transact-SQL can be used to monitor a customized application. Trend analysis: System Monitor
System Monitor can provide trend analysis. From question:

Tailspin Toys has a custom application that accesses a hosted database named TSpinDB. The application will monitor TSpinDB and capture information over time about which database objects are accessed and how frequently they are accessed.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/performance-monitoring-and-tuning-tools

NEW QUESTION 6
You administer a Microsoft SQL Server 2021 instance that has multiple databases. You have a two-node SQL Server failover cluster.
The cluster uses a storage area network (SAN). You discover I/O issues. The SAN is at capacity and additional disks cannot be added.
You need to reduce the I/O workload on the SAN at a minimal cost. What should you do?

• A. Move user databases to a local disk.
• B. Expand the tempdb data and log files.
• C. Modify application code to use table variables.
• D. Move the tempdb files to a local disk.

Answer: D

Explanation: You can configure TempDB on a local disk when you, for example, installing your SQL Server cluster. References: https://www.mssqltips.com/sqlservertip/2817/sql-server-2012-cluster-with-tempdb-on-local-disk/

NEW QUESTION 7
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You need to deploy a new Microsoft SQL Server environment that meets the following requirements:
The SQL Server instance must be highly available.
There must be minimal downtime incurred during hardware failure or operating system maintenance.
All instance-level security settings and SQL Server Agent jobs must be available without additional synchronization tasks.
What should you implement?

• A. a Microsoft Azure Stretch Database
• B. log shipping
• C. an Always On Availability Group with all replicas in synchronous-commit mode
• D. a file share witness
• E. a Microsoft SQL Server failover cluster instance (FCI)
• F. a Windows cluster with a shared-nothing architecture
• G. an Always On Availability Group with secondary replicas in asynchronous-commit mode

Answer: E

Explanation: As part of the SQL Server Always On offering, Always On Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level—a failover cluster instance (FCI). An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets.
When there is hardware or software failure of a server, the applications or clients connecting to the server will experience downtime. When a SQL Server instance is configured to be an FCI (instead of a standalone instance), the high availability of that SQL Server instance is protected by the presence of redundant nodes in the FCI.
References:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-s

NEW QUESTION 8
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a Microsoft Azure SQL database that has Blob Auditing configured. You need to review the audit logs.
Solution: From Microsoft SQL Server Management Studio, you connect to the database, and then you execute the following statement.

Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: The fn_get_audit_file, not dm_db_audit_file, the returns information from an audit file created by a server audit in SQL Server.
This example reads from a file that is named ShiraServer/MayaDB/SqlDbAuditing_Audit/2021-07-14/10_45_22_173_1.xel:
SELECT * FROM sys.fn_get_audit_file ('https://mystorage.blob.core.windows.net/sqldbauditlogs/ShiraServer/MayaDB/SqlDbAuditing_Audit/2021-07-
Note: Blob auditing logs are saved as a collection of blob files within a container named sqldbauditlogs. References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql

NEW QUESTION 9
You run the sp_who system stored procedure. A process is blocked by a long-running operation.
You need to identify details of the processes involved in the deadlock including the resources that are being accessed. You must also be able to view the deadlock chain.
What should you use?

• A. DBCC OPENTRAN()
• B. SQL Profiler
• C. sys.dm_exec_sessions
• D. Query Store

Answer: A

NEW QUESTION 10
You are implementing a SQL Server 2021 five-node failover cluster. You need to choose a quorum configuration.
Which configuration should you use?

• A. Distributed File System (DFS)
• B. Node Majority
• C. Cluster Shared Volume (CSV)
• D. Node and Disk Majority

Answer: D

Explanation: Node and Disk Majority (recommended for clusters with an even number of nodes)

NEW QUESTION 11
You need to grant access to an OLTP database regardless of the user operating the application. Which strategy should you use?

• A. Application role
• B. Database user
• C. Server login
• D. Server role

Answer: A

NEW QUESTION 12
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it As a result, these questions wilt not appear in the review screen.
You have a database named db1 that contains a table named Table1. You need to audit all updates to Table.
Solution: From Local Group Policy Editor, you configure auditing for object access. Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 13
You manage a SQL Server 2014 instance that contains a database named DB1. Users report that some queries to DB1 take longer than expected.
Although most queries run in less than one second, some queries take up to 20 seconds to run. You need to view all of the performance statistics for each database file.
Which method should you use?

• A. Query the sys.dm_os_tasks dynamic management view.
• B. Query the sys.dm_os_performance_counters dynamic management view.
• C. Query the sys.dm_io_virtual_file_stats dynamic management function.
• D. Examine the Data File I/O pane in Activity Monitor.

Answer: C

Explanation: sys.dm_io_virtual_file_stats Returns I/O statistics for data and log files.

NEW QUESTION 14
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
A company has a server that runs Microsoft SQL Server 2021 Web edition. The server has a default instance that hosts a database named DB1.
You need to ensure that you can perform auditing at the database level for DB1.
Solution: You migrate DB1 to a named instance on a server than runs Microsoft SQL Server 2021 Standard edition.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2021 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine

NEW QUESTION 15
You want to simulate read, write, checkpoint, backup, sort, and read-ahead activities for your organization's SQL Server 2021 deployment.
Which of the following tools would you use to accomplish this goal?

• A. SQLIO
• B. SQLIOSim
• C. SQLIOStress
• D. chkdsk

Answer: B

Explanation: The SQLIOSim utility has been upgraded from the SQLIOStress utility. The SQLIOSim utility more accurately simulates the I/O patterns of Microsoft SQL Server.
References:
https://support.microsoft.com/en-us/help/231619/how-to-use-the-sqliosim-utility-to-simulate-sql-server-activity-

NEW QUESTION 16
You have a database named Saleshistory that records sales transactions for your organization. You create indexes in the database. The database has grown over time and now contains hundreds of indexes. You need to identify the indexes that are not being used. Which dynamic management object should you use?

• A. sys.dm_os_sys_info
• B. sys.dm_dbjndex_operational_stats
• C. sys.dm_db_index_usage_stats
• D. sys.dm_db_stats_properties

Answer: C

Explanation: The sys.dm_db_index_usage_stats returns counts of different types of index operations and the time each type of operation was last performed.
The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-