[Free] EnsurePass Microsoft 70-762 Real Exam Dumps Questions 81-90

February 7, 2020

70-762 Dumps 2020-02

, , , , ,

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

Question No.81

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 database that contains a table named Employees. The table stored information about the employees of your company.

You need to implement the following auditing rules for the Employees table:

image

image

Record any changes that are made to the data in the Employees table. Customize the data recorded by the audit operations.

Solution: You implement a stored procedure on the Employees table. Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

We should use table-valued functions, not procedures, to customize the recorded change data. References: https://msdn.microsoft.com/en-us/library/cc645858.aspx

Question No.82

Note: This question is part of a series of questions that use the same or similar answer choices. An Answer choice may be correct for more than one question in the series. Each question independent of the other questions in this series. Information and details provided in a question apply only to that question.

You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.

You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.

You need to ensure that the performance of each instance is consistent for the same queried and query plans.

What should you do?

  1. Create a sys.dm_os_waiting_tasks query.

  2. Create a sys.dm_exec_sessions query.

  3. Create a Performance Monitor Data Collector Set.

  4. Create a sys.dm_os_memory_objects query.

  5. Create a sp_configure `max server memory#39; query.

  6. Create aSQL Profiler trace.

  7. Create asys.dm_os_wait_stats query.

  8. Create an Extended Event.

Correct Answer: H

Explanation:

Advanced Viewing of Target Data from Extended Events in SQL Server

When your event session is currently active, you might want to watch the event data in real time, as it is received by the target.

Management gt; Extended Events gt; Sessions gt; [your-session] gt; Watch Live Data.

The query_post_execution_showplan extended event enables you to see the actual query plan in the SQL Server Management Studio (SSMS) UI. When the Details pane is visible, you can see a graph of the query plan on the Query Plan tab. By hovering over a node on the query plan, you cansee a list of property names and their values for the node.

image

References: https://msdn.microsoft.com/en-us/library/mt752502.aspx

Question No.83

HOTSPOT

You are analyzing the performance of a database environment. You need to find all unused indexes in the current database.

How should you complete the Transact-SQL statement?

To answer, select the appropriate Transact-SQL segments in the answer area.

image

Correct Answer:

image

Question No.84

DRAG DROP

You have a database named MyDatabase. You must monitor all the execution plans in XML format by using Microsoft SQL Trace. The trace must meet the following requirements:

Capture execution plans only for queries that run the MyDatabase database. Filter out plans with event duration of less than or equal to 100 microseconds. Save trace results to a disk on the server.

You need to create the trace.

In which order should you arrange the Transact-SQL segments to develop the solution? To answer, move all Transact-SQL segments to the answer area and arrange them in the correct order.

NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

DECLARE @traceEventId int = 122;

DECLARE @traceColumnIdForTextData int = 1; DECLARE @durationFilter bigint = 100 DECLARE @databaseID int;

SELECT @databaseId = DB_ID(`MyDatabase#39;);

image

Correct Answer:

image

Question No.85

HOTSPOT

You create a database table named FactSales by running the following Transact-SQL statements:

image

You must optimize the indexes without making changes to the ix_FactSales_EmployeeKey index. You need to implement a columnstore index for the table.

How should you complete the Transact-SQL code?

To answer, select the appropriate Transact-SQL segments in the answer area. NOTE: Each correct selection is worth one point.

image

Correct Answer:

image

Question No.86

You maintain a Microsoft Azure SQL Database instance.

You grant User1 the SELECT and EXECUTE permissions for all objects in the dbo schema. You must create a stored procedure that allows User1 to view the following information:

image

details for each connection to the database

image

a list of all active user connections and internal tasks

You need to create the stored procedure for User1 and ensure that User1 can run the stored procedure without any error.

How should you complete the Transact-SQL statements?

To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact- SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

NOTE: More than one combination of answer choices is correct. You will receive credit for any of the correct combinations you select.

image

Correct Answer:

image

Question No.87

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You have a database named DB1. There is no memory-optimized filegroup in the database.

You have a table and a stored procedure that were created by running the following Transact- SQL statements:

image

The Employee table is persisted on disk. You add 2,000 records to the Employee table.

You need to create an index that meets the following requirements:

image

image

image

Optimizes the performance of the stored procedure. Covers all the columns required from the Employee table. Uses FirstName and LastName as included columns.

image

Minimizes index storage size and index key size. What should you do?

  1. Create a clustered index on the table.

  2. Create a nonclustered index on the table.

  3. Create a nonclustered filtered index on the table.

  4. Create a clustered columnstore index on the table.

  5. Create a nonclustered columnstore index on the table.

  6. Create a hash index on the table.

Correct Answer: B

Explanation:

https://technet.microsoft.com/en-us/library/jj835095(v=sql.110).aspx

Question No.88

You suspect deadlocks on a database.

Which two trace flags in the Microsoft SQL Server error log should you locate? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

A.

1204

B.

1211

C.

1222

D.

2528

E.

3205

Correct Answer: AC

Explanation:

Trace flag 1204 returns the resources and types of locks participating in a deadlock and also the current command affected.

Trace flag 1222 returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.

References:

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags- transact-sql?view=sql-server-2017

Question No.89

You have a table named Person.Address that includes the following columns:

image

image

image

image

image

image

AddressID AddressLine1 AddressLine2 City StateProvinceID PostakCode

image

image

RowGuid ModifiedDate

You need to create a nonclustered index on PostalCode named IX_Address_PostalCode that uses the following included columns:

image

image

image

image

AddressLine1 AddressLine2 City StateProvinceID

How should you complete the Transact-SQL statement?

To answer, select the appropriate Transact_SQL segments in the answer are.

image

Correct Answer:

image

Question No.90

Case study Background

You have a database named HR1 that includes a table named Employee.

You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.

You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.

Employee Table

You use the following Transact-SQL statements to create, configure, and populate the Employee table:

image

Application

You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:

image

image

UspA: This stored procedure updates only the EmployeeStatus column. UspB: This stored procedure updates only the EmployeePayRate column.

The application uses views to control access to data. Views must meet the following requirements:

image

image

Allow user access to all columns in the tables that the view accesses. Restrict updates to only the rows that the view returns.

image

You observe that the four indexes require a large amount of disk space. You must reduce the amount of disk space that the indexes are using.

You need to create a single index to replace the existing indexes.

Which three Transact-SQL segments should you use to develop the solution?

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.

NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

image

Correct Answer:

image

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

Related Posts