EnsurePass
2017 Aug Microsoft Official New Released 70-762
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/70-762.html

Developing SQL Databases

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.

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

Answer: A Explanation:

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

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

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

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:

  • There are a large number of single use plans.

  • 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

Answer:

image

Explanation:

image

  • Optimize for ad hoc workloads

    The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set

    to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

  • Cost Threshold for Parallelism

Use the cost thresholdfor parallelism option to specify the threshold at which Microsoft 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 elapsed time in seconds required to run the serial plan on a specific hardware configuration.

5 means 5 seconds, but is is 5 seconds on a machine internal to Microsoftfrom some time in the 1990s. There#39;s no way to relate it to execution time on your current machine, so we treat it as a pure number now. Raising it to 50 is a common suggestion nowadays, so that more of your simpler queries run on a single thread.

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

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

Answer:

image

Explanation:

image

Bit is aTransact-SQL integer data type that can take a value of 1, 0, or NULL.

Smallint is aTransact-SQL integer data type that can take a value in the range from -32,768 to 32,767.

int, bigint, smallint, and tinyint (Transact-SQL) Exact-number data types that use integer data.

image

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

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

Answer:

image

Explanation:

image

Delete up to 4000 rows at a time. Keep doing it until all rows have been deleted.

Note that @@ROWCOUNT returnsthe number of rows affected by the last statement. References:https://msdn.microsoft.com/en-us/library/ms187316.aspx

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

Answer:

image

Explanation:

image

Requirement1: SQL Server: Memory Manager: Total Server Memory (KB)

This counter specifies theamount of memory the server has committed using the memory

manager.

Requirement2: SQL Server: Memory Manager: Granted Workspace Memory (KB) Specifies the total amount of memory currently granted to executing processes, such as hash, sort, bulk copy, andindex creation operations.

Requirement3: Process: working Set

Each time a process is created, it reserves the minimum working set size for the process. The virtual memory manager attempts to keep enough memory for the minimum working set resident when the process is active, but keeps no more than the maximum size.

References:

https://msdn.microsoft.com/en-us/library/ms190924.aspx https://blogs.technet.microsoft.com/askperf/2007/05/18/sql-and-the-working-set/

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.

Answer: B Explanation:

References: 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.

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

100% Free Download!
Download Free Demo:70-762 Demo PDF
100% Pass Guaranteed!
Download 2017 EnsurePass 70-762 Full Exam PDF and VCE
Get 10% off your purchase! Copy it:TJDN-947R-9CCD [2017.07.01-2017.07.31]

EnsurePass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

2017 EnsurePass IT Certification PDF and VCE