Microsoft Microsoft Certifications 70-762 Questions & Answers

• Question 21:

  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 SQL Server instances. There are many SQL jobs that run during off-

  peak hours.

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

  You need to collect query performance data while minimizing the performance impact on the SQL Server.

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

  Correct Answer: C

  SQL Server Data Collector is a feature for performance monitoring and tuning available in SQL Server Management Studio.

  Integration Services packages transform and load the collected data into the Microsoft Data Warehouse database.

  Collection sets are defined and deployed on a server instance and can be run independently of each other. Each collection set can be applied to a target that matches the target types of all the collector types that are part of a collection set.

  The collection set is run by a SQL Server Agent job or jobs, and data is uploaded to the management data warehouse on a predefined schedule.

  Predefined data collection sets include:

  The Query Statistics data collection set collects information about query statistics, activity, execution plans and text on the SQL Server instance. It does not store all executed statements, only 10 worst performing ones.

  Disk Usage data collection set collects information about disk space used by both data and log files for all databases on the SQL Server instance, growth trends, and average day growth.

  Etc.

  References:

  http://www.sqlshack.com/sql-server-performance-monitoring-data-collector/

• Question 22:

  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 SQL Server instances. There are many SQL jobs that run during off-

  peak hours.

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

  You need to create a baseline set of metrics to report how the computer running SQL Server operates under normal load. The baseline must include the resource usage associated with the server processes.

  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 asys.dm_os_wait_stats query.

  H. Create an Extended Event.

  Correct Answer: D

  sys.dm_os_memory_objects returns memory objects that are currently allocated by SQL Server. You can usesys.dm_os_memory_objects to analyze memory use and to identify possible memory leaks.

  Example: The following example returns the amount of memory allocated by each memory object type. SELECT SUM (pages_in_bytes) as 'Bytes Used', type FROMsys.dm_os_memory_objects GROUP BY type ORDER BY 'Bytes Used' DESC; GO

• Question 23:

  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 that contains a table named Employees. The table stored information about the employees of your company. You need to implement the following auditing rules for the Employees table:

  - Record any changes that are made to the data in the Employees table.

  -

  Customize the data recorded by the audit operations.

  Solution: You implement a user-defined function on the Employees table.

  Does the solution meet the goal?

  A.

  Yes

  B.

  No

  Correct Answer: A

  SQL Server 2016 provides two features that track changes to data in a database: change data capture and change tracking. These features enable applications to determine the DML changes (insert, update, and delete operations) that were

  made to user tables in a database.

  Change data is made available to change data capture consumers through table-valued functions (TVFs).

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

• Question 24:

  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 that contains a table named Employees. The table stored information about the employees of your company. You need to implement the following auditing rules for the Employees table:

  - Record any changes that are made to the data in the Employees table.

  -

  Customize the data recorded by the audit operations.

  Solution: You implement a check constraint on the Employees table.

  Does the solution meet the goal?

  A.

  Yes

  B.

  No

  Correct Answer: B

  Check constraints cannot be used to track changes in a table. References:https://msdn.microsoft.com/en-us/library/bb933994.aspx

• Question 25:

  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 that contains a table named Employees. The table stored information about the employees of your company. You need to implement the following auditing rules for the Employees table:

  - Record any changes that are made to the data in the Employees table.

  -

  Customize the data recorded by the audit operations.

  Solution: You implement a stored procedure on the Employees table.

  Does the solution meet the goal?

  A.

  Yes

  B.

  No

  Correct Answer: B

  We should use table-valued functions, not procedures, to customize the recorded change data. References: https://msdn.microsoft.com/en-us/library/cc645858.aspx

• Question 26:

  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.

  Your company has employees in different regions around the world.

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

  -Employee ID

  -

  date and time employee checked in to work

  -

  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

  Datetimeoffset, not datetimeofset, defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hourclock.

  Syntaxis: datetimeoffset [ (fractional seconds precision) ]

  For the use "datetimeoffset", the Fractional seconds precision is 7.

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

• Question 27:

  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.

  Your company has employees in different regions around the world.

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

  Employee ID date and time employee checked in to work 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 adate 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.

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

• Question 28:

  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.

  Your company has employees in different regions around the world. You need to create a database table that stores the following employee attendance information: Employee ID date and time employee checked in to work 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: A

  Datetimeoffset defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

  Syntaxis: datetimeoffset [ (fractional seconds precision) ]

  Forthe use "datetimeoffset(0)", the Fractional seconds precision is 0, which is required here.

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

• Question 29:

  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. The Account table was created using the following Transact-SQL statement:

  

  There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.

  You frequently run the following Transact-SQL SELECT statements:

  

  You must avoid table scans when you run the queries. You need to create one or more indexes for the table. Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  Create a clustered index on the AccountNumber column as it is unique, not a non nonclustered one. References:https://msdn.microsoft.com/en-us/library/ms190457.aspx

• Question 30:

  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. The Account table was created using the following Transact-SQL statement:

  

  There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.

  You frequently run the following Transact-SQL SELECT statements:

  

  You must avoid table scans when you run the queries. You need to create one or more indexes for the table. Solution: You run the following Transact-SQL statement:

  CREATE CLUSTERED INDEX PK_Account ON Account(ProductCode);

  Does the solution meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  We need an index on the productCode column as well. References:https://msdn.microsoft.com/en-us/library/ms190457.aspx