[Free] 2019(Nov) EnsurePass Microsoft 70-762 Dumps with VCE and PDF 41-50

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

Question No.41

Note: The 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 question in the series. Information and details provided in a question apply only to that question.

You have a database named DB1. The database does not have a memory optimized filegroup. You create a table by running the following Transact-SQL statement:

image

The table is currently used for OLTP workloads. The analytics user group needs to perform real- time operational analytics that scan most of the records in the table to aggregate on a number of columns.

You need to add the most efficient index to support the analytics workload without changing the OLTP application.

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

Explanation:

A nonclustered columnstore index enables real-timeoperational analytics in which the OLTP workload uses the underlying clustered index, while analytics run concurrently on the columnstore index.

Columnstore indexes can achieve up to 100xbetter performance on analytics and data warehousing workloads and up to 10x better data compression than traditional rowstore indexes.These recommendations will help your queries achieve the very fast query performance that columnstore indexes are designed to provide.

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

Question No.42

Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.

You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.

Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high WRITELOG value.

You need to improve the application response time.

Solution: You add a unique clustered index to the Employees table. Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: A

Explanation:

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

Question No.43

You use Microsoft SQL Server Profile to evaluate a query named Query1. The Profiler report indicates the following issues:

image

At each level of the query plan, a low total number of rows are processed.

image

The query uses many operations. This results in a high overall cost for the query. You need to identify the information that will be useful for the optimizer.

What should you do?

  1. Start a SQL Server Profiler trace for the event class Auto Stats in the Performance event category.

  2. Create one Extended Events session with the sqlserver.missing_column_statistics eventadded.

  3. Start a SQL Server Profiler trace for the event class Soft Warnings in the Errors and Warnings event category.

  4. Create one Extended Events session with the sqlserver.missing_join_predicate event added.

Correct Answer: D

Explanation:

The Missing JoinPredicate event class indicates that a query is being executed that has no join predicate. This could result in a long-running query.

Question No.44

HOTSPOT

You are reviewing the execution plans in the query plan cache. You observe the following:

image

There are a large number of single use plans.

image

There are a large number of simple execution plans that use multiple CPU cores. You need to configure the server to optimize query plan execution.

Which two setting should you modify on the properties page for the Microsoft SQL Server instance?

To answer, select the appropriate settings in the answer area.

image

Correct Answer:

image

Question No.45

Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.

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

image

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

image

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

image

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: A

Explanation:

Create a clustered index on theAccountNumber column as it is unique. Create a nonclustered index that includes the ProductCode column.

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

Question No.46

HOTSPOT

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

You have a database named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.

image

The customer table includes a column that stores the data for the last order that the customer placed.

You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized.

The Leads table must include the columns described in the following table.

image

The data types chosen must consume the least amount of storage possible. You need to select the appropriate data types for the Leads table.

In the table below, identify the data type that must be used for each table column. NOTE: Make only one selection in each column.

image

Correct Answer:

image

Question No.47

HOTSPOT

You are profiling a frequently used database table named UserEvents. The READ_COMMITED_SNAPSHOT database option is set to OFF.

In the trace results, you observe that lock escalation occurred for one stored procedure even though the number of locks in the database did not exceed memory or configuration thresholds. Events details are provided in the following table:

image

You need to modify the uspDeleteEvents stored procedure to avoid lock escalation. How should you modify the stored procedure?

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

image

Correct Answer:

image

Question No.48

DRAG DROP

You are analyzing the memory usage of a Microsoft SQL Server instance. You need to obtain the information described on the following table.

image

Which performance counter should you use for each requirement?

To answer, drag the appropriate performance counters to the correct requirements. Each performance counter may be used once, more than once or not at all. You may need to drag the split bat between panes or scroll to view content.

NOTE: Each correct selection is worth one point.

image

Correct Answer:

image

Question No.49

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:

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

Note: The 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 question in the series. Information and details provided in a question apply only to that question.

You have a database named DB1. The database does not use a memory-optimized filegroup. The database contains a table named Table1. The table must support the following workloads:

image

You need to add the most efficient index to support the new OLTP workload, while not deteriorating the existing Reporting query performance.

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 clusteredcolumnstore index on the table.

  5. Create a nonclustered columnstore index on the table.

  6. Create a hash index on the table.

Correct Answer: C

Explanation:

A filtered index is an optimized nonclustered index, especially suited to cover queries that select from awell-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

References: https://technet.microsoft.com/en-us/library/cc280372(v=sql.105).aspx

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

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>