Microsoft Microsoft Certifications 70-762 Questions & Answers

• Question 101:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.

  You need to identify the indexes that are not being used so that you can remove them from the database.

  Solution: You run the sys.dm_db_index_operational_stats dynamic management view.

  Does the solution meet the goal?

  A. Yes

  B. No

  Correct Answer: B

• Question 102:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.

  You need to identify the indexes that are not being used so that you can remove them from the database.

  Solution: You run the sys.dm_db_index_usage_stats dynamic management view.

  Does the solution meet the goal?

  A. Yes

  B. No

  Correct Answer: A

  References: https://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/

  https://www.sqlshack.com/how-to-identify-and-monitor-unused-indexes-in-sql-server/

• Question 103:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.

  You need to identify the indexes that are not being used so that you can remove them from the database.

  Solution: You run the sys.dm_db_index_physical_stats dynamic management view.

  Does the solution meet the goal?

  A. Yes

  B. No

  Correct Answer: B

• Question 104:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.

  You have a database named dbReporting. Users run a large number of read-only ad hoc queries against the database. The application and all ad hoc queries use default database transaction isolation levels. You set the value of the

  READ_COMMITTED_SNAPSHOT database option to ON.

  You have an application that updates 10 tables sequentially and modifies a large volume of records in a single transaction. The updates are isolated from each other.

  Users report an error which indicates that the version store us full.

  You need to reduce the number of occurrences of the error.

  Solution: You increase the maximum database size for the dbReporting database.

  Does the solution meet the goal?

  A. Yes

  B. No

  Correct Answer: B

• Question 105:

  You are creating the following two stored procedures:

  A natively-compiled stored procedure

  An interpreted stored procedure that accesses both disk-based and memory-optimized tables

  Both stored procedures run within transactions.

  You need to ensure that cross-container transactions are possible.

  Which setting or option should you use?

  A. the BEGIN TRANSACTION AND COMMIT OR ROLLBACK TRANSACTION statements for the connection

  B. the SERIALIZABLE table hint on memory-optimized tables

  C. the CONTAINS MEMORY_OPTIMIZED_DATA option for the file group

  D. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF option for the database

  Correct Answer: A

  Your Transact-SQL contains the code BEGIN TRANSACTION, along with an eventual COMMIT TRANSACTION. Two or more statements can be corralled into the same transaction.

  In explicit mode, you must either use the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT or code a table hint about the transaction isolation level on the memory-optimized table in the FROM clause.

  Incorrect Answers:

  B: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-2017

• Question 106:

  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.

  You need to collect data from the following two sources:

  1.

  The performance counters of the operating system

  2.

  Microsoft SQL Server events

  You must analyze the two datasets side-by side by using a single tool.

  Solution: You use dynamic management views and Data Collector Sets (DCs) in Performance Monitor to collect performance data. You use SQL Server Management Studio (SSMS) to analyze the data.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  Reference: https://www.sqlshack.com/dba-guide-sql-server-performance-troubleshooting-part-2-monitoring-utilities/

• Question 107:

  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 questions sets might have more than onecorrect 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.

  You need to collect data from the following two sources:

  1.

  The performance counters of the operating system

  2.

  Microsoft SQL Server events

  You must analyze the two datasets side-by side by using a single tool.

  Solution: You use SQL Server Profiler and SQL Server Extended Events to collect performance data. You use SQL Server Profiler to analyze the data.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  Reference: https://www.sqlshack.com/dba-guide-sql-server-performance-troubleshooting-part-2-monitoring-utilities/

• Question 108:

  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 questions sets might have more than onecorrect 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.

  You need to collect data from the following two sources:

  1.

  The performance counters of the operating system

  2.

  Microsoft SQL Server events

  You must analyze the two datasets side-by side by using a single tool.

  Solution: You use dynamic management views and SQL Server Profiler to collect performance data. You use SQL Server Management Studio (SSMS) to analyze the data.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: A

  Reference: https://www.sqlshack.com/dba-guide-sql-server-performance-troubleshooting-part-2-monitoring-utilities/

• Question 109:

  NOTE: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.

  Your company has employees in different regions around the world.

  You need to create a database table that stores the following employee attendance information:

  1.

  Employee ID

  2.

  Date and time employee checked in to work

  3.

  Date and time employee checked out of work

  Date and time information must be time zone aware and must not store fractional seconds.

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  Datetime2 stores fractional seconds.

  Datetime2 defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and

  optional user-specified precision.

  Reference:

  https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-2017

  https://msdn.microsoft.com/en-us/library/bb677335.aspx