[Free] EnsurePass Microsoft 70-762 Real Exam Dumps Questions 111-120

Get Full Version of the Exam
http://www.EnsurePass.com/70-762.html

Question No.111

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some questions sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have a 3-TB database. The database server has 64 CPU cores. You plan to migrate the database to Microsoft Azure SQL Database.

You need to select the service tier for the Azure SQL database. The solution must meet or exceed the current processing capacity.

Solution: You select the Basic service tier. Does this meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

image

Premium service is required for 3 TB of storage. Single database DTU and storage limits

References:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu

Question No.112

You have a reporting application that uses a table named Table1. You deploy a new batch update process to perform updates to Table1.

The environment is configured with the following properties:

image

The database is configured with the default isolation setting.

image

The application and process use the default transaction handling.

You observe the application cannot access any rows that are in use by the process. You have the following requirements:

image

image

image

Ensure the application is not blocked by the process. Ensure the application has a consistent view of the data Ensure the application does not read dirty data.

You need to resolve the issue and meet the requirements with the least amount of administrative effort.

What should you do?

  1. Enable the database for the ALLOW_SNAPSHOT_ISOLATION isolation level. Modify the application for the SERIALIZABLE isolation level.

  2. Enable the database for the READ_COMITTED_SNAPSHOT isolation level.

  3. Enable the application for the WITH (NOLOCK) hint.

  4. Enable the database for the ALLOW_SNAPSHOT_ISOLATION isolation level. Modify the application and the update process for the SNAPSHOT isolation level.

Correct Answer: B

Explanation:

Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. This activates the mechanism for storing row versions in the temporary database (tempdb).

READ COMMITTED is the default isolation level for SQL Server. It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions. Other transactions can still modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non- repeatable reads, or quot;phantomquot; data.

Question No.113

You have two databases with the following settings:

image

You run the following Transact-SQL statements:

image

You need to select data from DiskTable and insert the data into MemTable. You must complete the insertion operation into MemTable as an explicit transaction without immediate durability.

Which four Transact-SQL segments should you use?

To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

image

Correct Answer:

image

Question No.114

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.

The Account table was created by using the following Transact-SQL statement:

image

There are more than 1 billion records in the Account table. The AccountNuwber 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:

SELECT ProductCode, SUM(Balance) AS TotalSUH FROM Account WHERE ProductCode lt;gt; #39;CD#39; GROUP 8Y ProductCode; SELECT AccountNupber, Balance FROM Account WHERE ProductCode – #39;CDquot;;

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

image

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: A

Question No.115

You have an existing Microsoft SQL Trace script. You plan to convert the script to an Extended Events session.

You need to collect the trace ID and other required information. Which system table should you use?

  1. dbo.syssessions

  2. trace xenction_map

  3. dbo. syinotifications

  4. sysdbmaintplan.

Correct Answer: A

Question No.116

HOTSPOT

You have a database named Database1 .

Users report that they experience deadlock issues. You run the sp_readerlog stored procedure. You view the output from the Process List section as shown in the Process List exhibit. (Click the Process List tab.)

image

You view the contents of the Resource List section as shown in the Resource List exhibit. (Click the Resource List tab.)

image

You view deadlock information as shown in the Deadlock List exhibit. (Click the Deadlock List tab.)

image

You need to identify the causes of the deadlock.

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

image

Correct Answer:

image

Question No.117

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 table that has a clustered index and a nonclustered index. The indexes use different columns from the table. You have a query named Query1 that uses the nonclustered index.

Users report that Query1 takes a long time to report results. You run Query1 and review the following statistics for an index seek operation:

image

You need to resolve the performance issue. Solution: You rebuild the clustered index.

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

The query uses the nonclustered index, so improving the clustered index will not help. We should update statistics for the nonclustered index.

Question No.118

You have a relational data warehouse that contains 1 YB of data.

You have a stored procedure named usp-sp1 that generates an HTML fragment. The HTML fragment contains color and font style.

You need to return the HTML fragment. What should you do?

  1. Use the NOLOCK option

  2. Execute the DBCC UPDATEUSAGE statement.

  3. Use the max worker threads option.

  4. Use a table-valued parameter.

  5. Set SET ALLOW_SNAPSHOT_SOLATION to on.

  6. Set SET XACT_ABORT to on.

  7. Execute the ALTER TABLE T1 SET (LOCK_ESCALATION AUTO ): statement.

  8. Use the OUTPUT parameters.

Correct Answer: G

Explanation:

A SQL Server stored procedure that you can call is one that returns one or more OUT parameters, which are parameters that the stored procedure uses to return data back to the calling application.

References:

https://docs.microsoft.com/en-us/sql/connect/jdbc/using-a-stored-procedure-with-output- parameters?view=sql-server-2017

Question No.119

You run the following Transact-SQL statements:

image

Records must only be added to the orders table by using the view. If a customer name does not exist then a new customer name must be created.

You need to ensure that you can insert rows into the orders table by using the view. What should you do?

  1. Add the CustomerlD column from the Orders table and the WfTH CHECK OPTION statement to the view.

  2. Create an INSTEAD of trigger on the view.

  3. Add the WITH SCHEMABINDING statement to the view statement and create a clustered index on the view.

  4. Remove the subquery from the view, add the WITH SCHEMABINDING statement, and add a trigger to the Orders table to perform the required logic.

Correct Answer: A

Explanation:

The WITH CHECK OPTION clause forces all data-modification statements executed against the view to adhere to the criteria set within the WHERE clause of the SELECT statement defining the view. Rows cannot be modified in a way that causes them to vanish from the view.

References: http://www.informit.com/articles/article.aspx?p=130855amp;seqNum=4

Question No.120

You have multiple stored procedures inside a transaction.

You need to ensure that all the data modified by the transaction is rolled back if a stored procedure causes a deadlock or times out.

What should you do?

  1. Use the NOLOCK option.

  2. Execute the DBCC UPDATEUSAGE statement.

  3. Use the max worker threads Option.

  4. Use a table-valued parameter.

  5. Set SET ALLOW SNAPSHOT ISOLATION to ON,

  6. Set SET XACT ABORT to ON.

  7. Execute the alter table T1 set (lock escalation = auto); statement.

  8. Use the output parameters.

Correct Answer: F

Explanation:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server- 2017

Get Full Version of the Exam
70-762 Dumps
70-762 VCE and PDF

Leave a Comment

Your email address will not be published. Required fields are marked *