Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 191:

  HOTSPOT

  You have a server named SQL1 that has SQL Server 2014 installed. SQL1 has 32 processor cores and 64 GB of RAM.

  SQL1 hosts a database used for a public-facing marketing website. SQL1 performs Online Transaction Processing (OLTP) operations only.

  Several of the queries that run on SQL1 use the 32 processor cores and complete in 30 ms.

  You need to reduce the number of queries that use multiple cores. The solution must also reduce the number of processor cores used by the queries that require multiple processor cores.

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

  Hot Area:

  

  

  ---

  Cost Threshold for Parallelism The cost threshold for parallelism option specifies the threshold at which SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated cost required to run the serial plan on a specific hardware configuration, and is not a unit of time. The cost threshold for parallelism option can be set to any value from 0 through 32767. The default value is 5. Max Degree of Parallelism When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.

  References: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option

• Question 192:

  You administer a Microsoft SQL Server database.

  You need to ensure that the size of the transaction log file does not exceed 2 GB.

  What should you do?

  A. Execute sp_configure 'max log size', 2G.
  B. Use the ALTER DATABASE...MODIFY FILEGROUP command along with the name and maxsize parameter.
  C. In SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.
  D. Use the ALTER DATABASE...AUTO_SHRINK command along with the On parameter.
  C. In SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.

• Question 193:

  DRAG DROP

  You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data.

  BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book. You need to ensure that the new column is assigned a GUID for existing rows. Which four Transact-SQL statements should you

  use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

  ---

  Actually, in the real world, you don't have to use WITH VALUES at the end of the statement and it works just as well. But because the question specifically states which FOUR TSQL statements to use, we have to include it. References: http://msdn.microsoft.com/en-us/library/ms190273.aspx

• Question 194:

  Which of the following will show you a history of job errors that have occurred in jobs scheduled to run in an SQL Server 2012 installation?

  A. Look at the sys.all_job_hist table.
  B. In Management Studio, navigate to SQL Server Agent -> Error Logs and look at the log for the corresponding date.
  C. Open the c:\error\job.log file and search for the particular job and the errors you wish to view.
  D. You must use T-SQL to write a job-logging procedure to monitor jobs.
  D. You must use T-SQL to write a job-logging procedure to monitor jobs.

• Question 195:

  DRAG DROP

  You have a SQL Server database server that contains a database named CustomerDB. CustomerDB is protected by using transparent data encryption (TDE) and a certificate named TDE_Cert.

  The server fails.

  You deploy a new server and restore all of the backups to a folder named C:\backups.

  You need to restore the database to the new server.

  Which three statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

  Select and Place:

  

  

• Question 196:

  You have configured Resource Governor with three resource pools.

  You have assigned the first resource pool a minimum CPU and memory value of 20%.

  You have assigned the second resource pool a minimum CPU and memory value of 30%.

  You want to assign maximum CPU and memory values to the third resource pool.

  What is the maximum CPU and memory value you can assign to this resource pool?

  A. 30%
  B. 50%
  C. 70%
  D. 100%
  B. 50%

  ---

  The maximum resource value assigned to the third pool is 65%; the sum of the minimum resource values assigned to the other pools is 50%.

  Note: MIN_CPU_PERCENT and MAX_CPU_PERCENT

  These settings are the minimum and maximum guaranteed average CPU bandwidth for all requests in the resource pool when there is CPU contention. You can use these settings to establish predictable CPU resource usage for multiple workloads that is based on the needs of each workload. For example, assume the Sales and Marketing departments in a company share the same database. The Sales department has a CPU-intensive workload with high-priority queries. The Marketing department also has a CPU-intensive workload, but has lower-priority queries. By creating a separate resource pool for each department, you can assign a minimum CPU percentage of 70 for the Sales resource pool and a maximum CPU percentage of 30 for the Marketing resource pool. This ensures that the Sales workload receives the CPU resources it requires and the Marketing workload is isolated from the CPU demands of the Sales workload. Note that the maximum CPU percentage is an opportunistic maximum. If there is available CPU capacity, the workload uses it up to 100 percent. The maximum value only applies when there is contention for CPU resources. In this example, if the Sales workload is switched off, the Marketing workload can use 100 percent of the CPU if needed.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-resource-pool

• Question 197:

  You administer a Microsoft Azure SQL Database instance named Orders.

  Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is disabled.

  You need to ensure that the query can use the IX_OrderDate index.

  Which Transact-SQL command should you use?

  A. ALTER INDEX IX_OrderDate ON CustomerOrder DISABLE
  B. ALTER INDEX IX_OrderDate ON CustomerOrder ENABLE
  C. ALTER INDEX IX_OrderDate ON CustomerOrder REORGANIZE
  D. ALTER INDEX IX_OrderDate ON CustomerOrder REBUILD
  B. ALTER INDEX IX_OrderDate ON CustomerOrder ENABLE

  ---

  Enable a disabled index by using the Transact-SQL ALTER INDEX REBUILD command. Incorrect Answers:

  B: There is no ALTER INDEX ..ENABLE subcommand.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/enable-indexes-and-constraints https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

• Question 198:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 environment that contains a production SQL Server 2005 instance named SQL2005 and a development SQL Server 2012 instance named SQL2012.

  The development team develops a new application that uses the SQL Server 2012 functionality. You are planning to migrate a database from SQL2005 to SQL2012 so that the development team can test their new application.

  You need to migrate the database without affecting the production environment.

  Which three 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:

  

  

  ---

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

• Question 199:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 database named Human_Resources.

  You need to ensure that all read activity against any object in the Human_Resources database is audited and written to a text file.

  What should you do? (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:

  

  

  ---

  Create a new Audit. For destination, select File.

  Create a new Database Audit Specification on Human_Resources. For Audit Action Type, select Select, and for Object Class, select Database.

  Enable Audi and Audi Specification.

  Explanation:

  The general process for creating and using an audit is as follows.

  1. Create an audit and define the target.

  2. Create either a server audit specification or database audit specification that maps to the audit. Enable the audit specification.

  3. Enable the audit.

  4. Read the audit events by using the Windows Event Viewer, Log File Viewer, or the fn_get_audit_file function.

  References:

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

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

• Question 200:

  You manage a server that has SQL Server installed and hosts databases for five applications. Each application has a different login to the SQL Server instance. You need to create a TCP endpoint to be used only by a login named Application3. The solution must ensure that Application3 only uses the new endpoint. Which three statements should you execute? Each correct answer presents part of the solution.

  A. GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO Application3
  B. DENY CONNECT ON ENDPOINT::[TSQL Default TCP] TO Application3
  C. CREATE ENDPOINT Application3_EndPoint STATE=STARTED AS TCP (LISTENER_PORT=1388)
  D. GRANT .CONNECT ON ENDPOINT:: Application3_EndPoint TO Public
  E. GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO PUBLIC
  F. REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] TO Application3
  G. REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] TO PUBLIC
  A. GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO Application3
  C. CREATE ENDPOINT Application3_EndPoint STATE=STARTED AS TCP (LISTENER_PORT=1388)
  G. REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] TO PUBLIC

  ---

  C: Create the endpoint

  A: Grant permissions to Appliciation3 on the endpoint.

  G: Revoke information from public on the endpoint. Incorrect Answers:

  B: We should grant, not deny, access to Application3. D, E: Access to Public should not be granted.