Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 31:

  You administer a Microsoft SQL Server environment. The environment contains a SQL Server Reporting Services (SSRS) instance.

  You need to ensure that users can create subscriptions.

  What should you configure?

  A. cache refresh options
  B. site security for service account
  C. SSRS Execution Account
  D. folder level security
  D. folder level security

  ---

  User requirements. The author of the subscription must have permission to "Manage reports" and "Manage all subscriptions." The author must also have the necessary credentials to access the external data source that contains subscriber data.

  An item-level task is a collection of permissions that relate to a report, folder, report model, resource, or shared data source.

  References: https://docs.microsoft.com/en-us/sql/reporting-services/security/tasks-and-permissions-item-level-tasks

• Question 32:

  You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows.

  You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements: Does not return an error Closes all opened transactions

  Which Transact-SQL statement should you use?

  

  A. OptionA
  B. OptionB
  C. OptionC
  D. OptionD
  D. OptionD

• Question 33:

  Which feature should you enable and configure so session requests addressed to a specific instance can be allocated different processor resources based on session request properties?

  A. Resource Governor
  B. Windows System Resource Manager
  C. Processor affinity
  D. I/O affinity
  A. Resource Governor

  ---

  Resource Governor enables you to allocate session requests to different resources based on the characteristics of the session request properties. References: https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor

• Question 34:

  You have a default installation of SQL Server that hosts an Online Transaction Processing (OLTP) application.

  Users report that they experience poor overall query performance for the application.

  You query the wait statistics and discover that the two top waits are CXPACKET and SOS_SCHEDULER_YIELD.

  You need to modify the SQL Server settings to resolve the issue causing the poor query performance.

  Which two settings should you modify? Each correct answer presents part of the solution.

  A. max degree of parallelism (MAXDOP)
  B. Minimum Memory
  C. optimize for ad hoc workloads
  D. Boost SQL Server priority
  E. cost threshold for parallelism
  A. max degree of parallelism (MAXDOP)
  E. cost threshold for parallelism

  ---

  A: Lower the MAXDOP.

  When high CXPACKET values are encountered, a possible issue, even in case when parallelism is evenly distributed, is when the cost of creating the parallel plan is higher than the cost of the serialized thread. This is often something that is

  overlooked and by the rule of thumb of reaching for altering of the Max Degree of Parallelism (MAXDOP), by setting it to 1 (each and every query will be processed by the single CPU core). Configuring MAXDOP settings to 1 should be the

  last resource used in troubleshooting excessive CXPACKET wait times.

  When a high CXPACKET value is accompanied with a LATCH_XX and with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD, it is an indicator that slow/inefficient parallelism itself is the actual root cause of the performance issues. And in

  such a scenario if the LATCH_XX waits is ACCESS_METHODS_DATASET_PARENT or ACCESS_METHODS_SCAN_RANGE_GENERATOR class, then it is highly possible that the parallelism level is the bottleneck and the actual root

  cause of the query performance issue. This is a typical example when MAXDOP should be reduced.

  E: The Cost Threshold for Parallelism (CTFP) value is in seconds and it means that for every query for which SQL Server estimates that running time will be longer than 5 seconds, a parallel plan will be created.

  To prevent unwanted parallelism, the CTFP number could be increased and by the aforementioned rule of thumb, a minimum value of 25. Recent analysis indicates that 50 should be the optimal minimal number for modern computers.

  References: https://www.sqlshack.com/troubleshooting-the-cxpacket-wait-type-in-sql-server/

• Question 35:

  You administer a Microsoft SQL Server environment. One of the SQL Server instances contains a database named Contoso.

  You plan to migrate Contoso to Microsoft Azure SQL Database.

  To do so, you need to implement a contained database.

  Which two actions should you perform? Each correct answer presents part of the solution.

  A. Set database containment to CONTAINED.
  B. Enable database property contained database authentication.
  C. Set database containment to PARTIAL.
  D. Set database containment to CLOUD.
  E. Enable server property contained database authentication.
  F. Enable database property encryption enabled.
  B. Enable database property contained database authentication.
  C. Set database containment to PARTIAL.

  ---

  Contained database authentication is defined on the database, not on the server.

  A database is converted to a contained database by changing the CONTAINMENT option to partial.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/databases/migrate-to-a-partially-contained-database

• Question 36:

  Note: This question is part of a series of questions that use the same set of answers choices. An answer choice may be correct for more than one question in the series.

  You administer a Microsoft SQL Server database server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database.

  The recovery model and backup schedule are configured as shown in the following table:

  

  One of the hard disk drives that stores the reporting database fails at 16:40 hours.

  You need to ensure that the reporting database is restored. You also need to ensure that data loss is minimal.

  What should you do?

  A. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
  B. Perform a partial restore.
  C. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
  D. Perform a point-in-time restore.
  E. Restore the latest full backup.
  F. Perform a page restore.
  G. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
  H. Restore the latest full backup. Then, restore the latest differential backup.
  H. Restore the latest full backup. Then, restore the latest differential backup.

• Question 37:

  You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table.

  What should you do?

  A. Reference a user-defined function within the computed column.
  B. Create a BEFORE trigger that maintains the state of the computed column.
  C. Add a default constraint to the computed column that implements hard-coded values.
  D. Add a default constraint to the computed column that implements hard-coded CASE statements.
  A. Reference a user-defined function within the computed column.

• Question 38:

  You administer a database that has User A, B and C.

  User A must be able to create new tables and stored procedures, User B must be able to select, update, delete and insert data.

  User C must be able to provide windows logins to the database

  To which role or roles should you add to UserB? Select all that apply.

  A. db_accessadmin
  B. db_ddladmin
  C. db_datawriter
  D. db_datareader
  E. db_owner
  C. db_datawriter
  D. db_datareader

  ---

  User A = db_ddladmin User B= db_datawriter, db_datareader User C = db_accessadmin

• Question 39:

  You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly.

  You discover that a large amount of memory is consumed by single-use dynamic queries.

  You need to reduce procedure cache usage from these statements without creating any additional indexes.

  What should you do?

  A. Add a HASH hint to the query.
  B. Add a LOOP hint to the query.
  C. Add a FORCESEEK hint to the query.
  D. Add an INCLUDE clause to the index.
  E. Add a FORCESCAN hint to the Attach query.
  F. Add a columnstore index to cover the query.
  G. Enable the optimize for ad hoc workloads option.
  H. Cover the unique clustered index with a columnstore index.
  I. Include a SET FORCEPLAN ON statement before you run the query.
  J. Include a SET STATISTICS PROFILE ON statement before you run the query.
  G. Enable the optimize for ad hoc workloads option.

  ---

  Reference: http://msdn.microsoft.com/en-us/library/cc645587.aspx

• Question 40:

  If you are required to move an SQL 2012 database from one server to another, which of the following will do this at the database level?

  A. You just have to mirror the database.
  B. Detach the database, move the database files, and attach the database on the new server.
  C. Use the SQL Server Move Wizard to define the old and new server as well as the scheduled move time.
  D. The database must be created on the new machine and then the data moved with Export/Import.
  B. Detach the database, move the database files, and attach the database on the new server.

  ---

  References: https://msdn.microsoft.com/en-us/library/ms187858%28v=sql.110%29.aspx