Microsoft 70-432 Online Practice Questions and Exam Preparation

Microsoft 70-432 Online Questions & Answers

• Question 41:

  You are mastering the company database. During the development, you find that Transact- SQL query below is running slowly:SELECT VideoTitle, UpcNum, RetailPrice, Release Date FROM Srvideo.VideoTitle WITH (INDEX(0)) WHERE ReleaseDate BETWEEN '20050401' AND '20050510' A clustered index exists on the VideoTitle column.

  There is a nonclustered index on the ReleaseDate column which contains the UpcNum and RetailPrice columns. The result of the avg_fragmentation_in_percent is 30 percent, when you search the sys.dm_db_index_physical_stats dynamic management function (DMF) or the VideoTitle table,In order to find this method to solve this problem, which is the correct answer?

  A. You should reproduce the whole indexes on the VideoTitle table.
  B. You should delete the query hint from the query.
  C. You should alter the query hint to force the optimizer to force a unclustered index seek.
  D. You should Re-create the index on only the ReleaseDate column.
  B. You should delete the query hint from the query.

• Question 42:

  You administer a Microsoft SQL Server 2008 R2 database that hosts an order-processing application.

  You need to ensure that the database allows full-text searches on the Customers table. You also need to ensure that the full-text index is ready for use by the users.

  You execute a Transact-SQL statement to create the full-text index on the Customers table by using the CHANGE_TRACKING

  OFF and NO POPULATION clauses in the CREATE FULLTEXT INDEX statement.

  You execute an ALTER FULLTEXT INDEX.

  Which command or commands should you use next?

  

  A. B.
  C. D.
  D

• Question 43:

  You administer a Microsoft SQL Server 2008 R2 database instance that supports a sales management application.

  Occasionally, users of the application report that they receive a deadlock error. You plan to troubleshoot this error.

  You need to ensure that the instance is configured to capture troubleshooting information that will create an XML document detailing the deadlock condition.

  Which two actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 44:

  You administer a SQL Server 2008 instance. The server contains a very large database that is used by an application that is constantly available.

  Users report that the server performance has degraded.

  You need to use the Database Engine Tuning Advisor to improve the performance of the application.

  You also need to ensure that the performance of the production server is unaffected while analyzing the workload.

  What should you do?

  A. Enable the XP_MSVER stored procedure on the local server.
  B. Enable the XP_MSVER stored procedure on the remote server.
  C. Use the dta.exe utility on the production server along with an XML input file.
  D. Configure a test server that has a similar hardware configuration. Use the dta.exe utility on the test server along with an XML input file.
  D. Configure a test server that has a similar hardware configuration. Use the dta.exe utility on the test server along with an XML input file.

• Question 45:

  Yon manage several SQL Server 2008 instances.

  You plan to collect performance data periodically on all instances by using the data collector. All collected data must be stored in the same database hosted on a single instance.

  You need to collect and load performance data in the management data warehouse every 6 hours.

  Which data collection process should you implement?

  A. Create a cached data collection.
  B. Create a scheduled non-cached data collection.
  C. Create an on-demand non-cached data collection.
  D. Create two different SQL Agent jobs that are scheduled at the same time. One job creates a data collection and the other job uploads the data collection.
  B. Create a scheduled non-cached data collection.

• Question 46:

  You administer a Microsoft SQL Server 2008 R2 instance.

  The database has been marked as suspect.

  You need to place the database into an EMERGENCY state for troubleshooting.

  What should you do?

  A. Execute sp_helpfile.
  B. Execute DBCC CHECKDB.
  C. Examine the msdb..suspect_pagestable.
  D. Execute DBCC CHECKDBalong with the REPAIR_FASTclause.
  E. Execute DBCC CHECKDBalong with the REPAIR_REBUILDclause.
  F. Restore the database from the most recent full backup. Apply any differential and log backups.
  G. Use the ALTER DATABASEstatement along with the SET EMERGENCYclause.
  H. Use the RESTORE DATABASEstatement along with the PAGESclause. Create a new log backup. Apply all differential and log backups, including the most recent backup.
  I. Use the RESTORE DATABASEstatement along with the PAGESclause. Apply any differential and log backups. Create a new log backup and then restore the new log backup.
  G. Use the ALTER DATABASEstatement along with the SET EMERGENCYclause.

• Question 47:

  You administer two SQL Server 2008 instances on separate servers named Server1 and Server2.

  The AdventureWorks database is set up for synchronous mirroring between the two instances.

  You need to configure the database to support automatic failover.

  What should you do?

  A. Configure the mirroring session by using the FAILOVER option.
  B. Configure the mirroring session with the SAFETY option set to OFF.
  C. Configure the mirroring session with the SAFETY option set to FULL.
  D. Configure the mirroring session by adding a witness server to it.
  D. Configure the mirroring session by adding a witness server to it.

• Question 48:

  You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. According to the company requirement, the names of all user- defined stored procedures must contain the prefix usp_on all instances. Besides this, you must make sure that stored procedures that do not contain this prefix cannot be created by you. What should you do?

  A. A policy should be created. The policy targets the name of the stored procedure that is evaluated on demand.
  B. A policy should be created. The policy targets the name of the stored procedure that is evaluated on change.
  C. A condition should be created. The condition targets the name of the stored procedure that is evaluated on change
  D. A condition should be created. The condition targets the name of stored procedure that is evaluated on demand.
  B. A policy should be created. The policy targets the name of the stored procedure that is evaluated on change.

• Question 49:

  You administer a Microsoft SQL Server 2008 instance that contains two databases named Work and WorkHistory. Work uses the Latin1_General_CS_AS collation. WorkHistory uses the Latin1_General_100_CI_AI collation. Each database has an Orders table that has the following definition:

  

  You need to return records from WorkHistory.dbo.Orders that exist in Work.dbo.Orders. Which Transact-SQL query should you use?

  

  A. B. C. D.
  B

• Question 50:

  You maintain multiple SQL Server 2008 instances.

  You are designing a consolidated repository of performance data.

  You need to ensure that the following requirements are met:

  What should you do?

  A. Create and schedule a single Microsoft SQL Service Integration Services (SSIS) package process to store and delete performance data in a single database for all instances.
  B. Create a SQL Agent job process on each instance to store and delete performance data in a single database for all instances.
  C. Configure a management data warehouse process on each instance to store and delete performance data in a single database for all instances.
  D. Configure an automated server-side trace process on each instance to store and delete performance data in a single database for all instances.
  C. Configure a management data warehouse process on each instance to store and delete performance data in a single database for all instances.