Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 51:

  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 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.

  You have a database that is 130 GB and contains 500 million rows of data.

  Granular transactions and mass batch data imports change the database frequently throughout the day. Microsoft SQL Server Reporting Services (SSRS) uses the database to generate various reports by using several filters.

  You discover that some reports time out before they complete.

  You need to reduce the likelihood that the reports will time out.

  Solution: You create a file group for the indexes and a file group for the data files. You store the files for each file group on separate disks.

  Does this meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  Consider creating two additional File Groups: Tables and Indexes. It is best not to put your stuff in PRIMARY as that is where SQL SERVER stores all of its data and meta-data about your objects. You create your Table and Clustered Index (as that is the data for the table) on [Tables] and all Non-Clustered indexes on [Indexes].

• Question 52:

  You are experiencing performance issues with the database server.

  You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems.

  What should you create?

  A. a System Monitor report
  B. a sys.dm_tran_database_transaction dynamic management view query
  C. an Extended Events session that uses Query Editor
  D. an Activity Monitor session in Microsoft SQL Management Studio.
  D. an Activity Monitor session in Microsoft SQL Management Studio.

  ---

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

• Question 53:

  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 table that has a clustered index and a nonclustered index. The indexes use different columns from the table. You have a query named Query1 that uses the nonclustered index. Users report that Query1 takes a long time to report results. You run Query1 and review the following statistics for an index seek operation:

  

  You need to resolve the performance issue. Solution: You rebuild the clustered index. Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  The query uses the nonclustered index, so improving the clustered index will not help. We should update statistics for the nonclustered index.

• Question 54:

  You have a view that includes an aggregate.

  You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses.

  You need to ensure that you can update the view.

  What should you create?

  A. a nonclustered index
  B. a schema-bound view
  C. a stored procedure
  D. an INSTEAD OF trigger
  B. a schema-bound view

  ---

  Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding.

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql

• Question 55:

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

  You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.

  Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the Microsoft SQL Server instance and observe several high values that include a

  disk performance issue. You examine wait statistics and observe an unusually high WRITELOG value.

  You need to improve the application response time.

  Solution: You add a unique clustered index to the Employees table.

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

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

• Question 56:

  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
  A. Yes

  ---

  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 57:

  You suspect deadlocks on a database.

  Which two trace flags in the Microsoft SQL Server error log should you locate? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. 1204
  B. 1211
  C. 1222
  D. 2528
  E. 3205
  A. 1204
  C. 1222

  ---

  Trace flag 1204 returns the resources and types of locks participating in a deadlock and also the current command affected. Trace flag 1222 returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.

  References: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

• Question 58:

  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 independent of the other questions in this series.

  Information and details provided in a question apply only to that question.

  You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run

  during off-peak hours.

  You must monitor and optimize the SQL Server to maximize throughput, response time, and overall SQL performance.

  You need to examine delays in executed threads, including errors with queries and batches.

  What should you do?

  A. Create a sys.dm_os_waiting_tasks query.
  B. Create a sys.dm_exec_sessions query.
  C. Create a Performance Monitor Data Collector Set.
  D. Create a sys.dm_os_memory_objects query.
  E. Create a sp_configure `max server memory'query.
  F. Create a SQL Profiler trace.
  G. Create a sys.dm_os_wait_stats query.
  H. Create an Extended Event.
  G. Create a sys.dm_os_wait_stats query.

  ---

  sys.dm_os_wait_stats returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches. Incorrect Answers:

  A: sys.dm_os_waiting_tasks returns information about the wait queue of tasks that are waiting on some resource.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql

• Question 59:

  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 independent of the other questions in this series.

  Information and details provided in a question apply only to that question.

  You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run

  during off-peak hours.

  You observe that many deadlocks appear to be happening during specific times of the day.

  You need to monitor the SQL environment and capture the information about the processes that are causing the deadlocks. Captured information must be viewable as the queries are running.

  What should you do?

  A. A. Create a sys.dm_os_waiting_tasks query.
  B. Create a sys.dm_exec_sessions query.
  C. Create a PerformanceMonitor Data Collector Set.
  D. Create a sys.dm_os_memory_objects query.
  E. Create a sp_configure `max server memory' query.
  F. Create a SQL Profiler trace.
  G. Create a sys.dm_os_wait_stats query.
  H. Create an Extended Event.
  F. Create a SQL Profiler trace.

  ---

  To view deadlock information, the Database Engine provides monitoring tools in the form of two trace flags, and the deadlock graph event in SQL Server Profiler.

  Trace Flag 1204 and Trace Flag 1222 When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each nodeinvolved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable bothtrace flags to obtain two representations of the same deadlock event.

  References:https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

• Question 60:

  You run the following Transact-SQL statement:

  

  There are multiple unique OrderID values. Most of the UnitPrice values for the same OrderID are different.

  You need to create a single index seek query that does not use the following operators: Nested loop Sort Key lookup

  Which Transact-SQL statement should you run?

  A. CREATE INDEX IX_OrderLines_1 ON OrderLines (OrderID, UnitPrice) INCLUDE (Description, Quantity)
  B. CREATE INDEX IX_OrderLines_1 ON OrderLines (OrderID, UnitPrice) INCLUDE (Quantity)
  C. CREATE INDEX IX_OrderLines_1 ON OrderLines (OrderID, UnitPrice, Quantity)
  D. CREATE INDEX IX_OrderLines_1 ON OrderLines (UnitPrice, OrderID) INCLUDE (Description, Quantity)
  A. CREATE INDEX IX_OrderLines_1 ON OrderLines (OrderID, UnitPrice) INCLUDE (Description, Quantity)

  ---

  An index with nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all

  the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

  Note: All data types except text, ntext, and image can be used as nonkey columns.

  Incorrect Answers:

  C: Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns.

  D: The most unique column should be the first in the index.

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017