Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 231:

  Which of the following DBCC commands can you use to check the physical and logical integrity of an SQL Server 2012 database?

  A. CVU
  B. VERIFYINTEGRITY
  C. CHECKINTEGRITY
  D. CHECKDB
  D. CHECKDB

  ---

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

• Question 232:

  Which of the following SQL Server 2012 indexes is ordered independently of the actual physical order of the table data on which the index is based?

  A. CLUSTERED
  B. BITMAP
  C. NONCLUSTERED
  D. IND_ORDERED
  C. NONCLUSTERED

  ---

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

• Question 233:

  You are developing a database application by using Microsoft SQL Server 2012. You have a query that runs slower than expected.

  You need to capture execution plans that will include detailed information on missing indexes recommended by the query optimizer.

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

• Question 234:

  HOTSPOT

  You have a SQL Server database that contains all of the sales data for your company.

  Users of the database report the following issues:

  Connections to the database fail when the users first attempt to connect in the morning.

  Inserting new records in the database takes longer than expected.

  You need to resolve the issues.

  Which two database properties should you modify? To answer, select the appropriate properties in the answer area.

  Hot Area:

  

  

  ---

  When Auto Close is enabled, the database is automatically shut down when the last user connection is ended. When a new connection is made to the database, the database automatically restarts. Auto Close should be disabled on

  production databases.

  When Auto Shrink is enabled, SQL Server will check the database every 30 minutes, and shrink it if the database has too much unused space.

  The shrinking process is resource intensive and can hurt server performance.

  The shrinking process causes severe index fragmentation, which hurts performance.

  References:

  https://www.red-gate.com/simple-talk/sql/database-administration/database-properties-health-check/

• Question 235:

  HOTSPOT

  You administer a Microsoft SQL Server 2012 database instance. Other applications run on the server. Some of the applications are throwing errors because of insufficient memory.

  You need to ensure that the other applications have sufficient memory.

  Which Setting should you configure?

  Hot Area:

  

  

• Question 236:

  Which of the following SQL Server 2012 indexes stores data in a column-wise fashion instead of the traditional row-wise method?

  A. Bitmap Index
  B. Clustered Index
  C. Function-based Index
  D. Columnstore Index
  C. Function-based Index

  ---

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

• Question 237:

  Which of the following is a disaster recovery solution offered in SQL Server 2012 and involves maintaining a standby copy of a primary database?

  A. AlwaysOn
  B. Log Shipping
  C. Clustering
  D. DataGuard
  A. AlwaysOn

• Question 238:

  You administer a Microsoft SQL Server database.

  Users report that an application that accesses the database displays an error, but the error does not provide meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error.

  You need to identify the root cause of the issue by retrieving the error message.

  What should you do?

  A. Flag all stored procedures for recompilation by using sp_recompile.
  B. Create an Extended Events session by using the sqlserver.error_reported event.
  C. Create a Performance Monitor session to capture the SQLServer:SQL Errors object.
  D. Create a SQL Profiler session to capture all ErrorLog and EventLog events.
  B. Create an Extended Events session by using the sqlserver.error_reported event.

  ---

  Event sqlserver.error_reported: This event gets fired every time that an error happens in the server

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

• Question 239:

  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:

  

  At 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are corrupted on the reporting database.

  You also need to ensure that data loss is minimal.

  What should you do?

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

  ---

  Restores a file or filegroup in a multi-filegroup database. Note that under the simple recovery model, the file must belong to a read-only filegroup. After a full file restore, a differential file backup can be restored.

  Page restore

  Restores individual pages. Page restore is available only under the full and bulk-logged recovery models.

  Piecemeal restore

  Restores the database in stages, beginning with the primary filegroup and one or more secondary filegroups. A piecemeal restore begins with a RESTORE DATABASE using the PARTIAL option and specifying one or more secondary

  filegroups to be restored.

• Question 240:

  Which of the following SQL Server 2012 tools assists DBAs in running and analyzing trace results?

  A. SSIS
  B. SQL Profiler
  C. AlwaysOn
  D. EXP
  B. SQL Profiler

  ---

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