Download New Updated (June) Microsoft 70-469 Practice Test Topic 12, Mix Questions Set 1 part02

Ensurepass

QUESTION 131  (Topic 12)

 

You have a server named Server1 that has 2 processors.

 

You plan to deploy multiple instances of SQL Server 2014 to Server1. Each instance will have multiple databases.

 

You need to recommend a method to allocate processor time to each database.

 

What should you include in the recommendation?

 

More than one answer choice may achieve the goal. Select the BEST answer.

 

A.

Resource Governor

B.

Max Degree of Parallelism

C.

Windows System Resource Manager (WSRM)

D.

Processor affinity

 

Answer: A

Explanation: SQL Server Resource Governor is a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.

Resource Governor

 

Incorrect:

D: PROCESS AFFINITY

Enables hardware threads to be associated with CPUs.

 

 

QUESTION 132  (Topic 12)

 

 

You plan to create a database.

 

The database will be used by a Microsoft .NET application for a special event that will last for two days.

 

During the event, data must be highly available.

 

After the event, the database will be deleted.

 

You need to recommend a solution to implement the database while minimizing costs. The solution must not affect any existing applications.

 

What should you recommend?

 

More than one answer choice may achieve the goal. Select the BEST answer.

 

A.

SQL Server 2014 Enterprise

B.

SQL Server 2014 Standard

C.

SQL Azure

D.

SQL Server 2014 Express with Advanced Services

 

Answer: B

Explanation: Programmability (AMO, ADOMD.Net, OLEDB, XML/A, ASSL) supported by Standard and Enterpirse editions only.

 

Reference: Features Supported by the Editions of SQL Server 2014

 

 

QUESTION 133  (Topic 12)

 

You have two SQL Server 2014 instances named SQLDev and SQLProd.

 

You plan to create a new database on SQLProd that will use SQL Server Authentication.

 

You need to ensure that when the new database is copied from SQLProd to SQLDev, users can connect to the database on SQLDev even if they do not have a login on the SQLDev instance.

 

 

What should you use?

 

More than one answer choice may achieve the goal. Select the BEST answer.

 

A.

Triggers

B.

Contained database

C.

SQL Server Analysis Services (SSAS) scripts

D.

Extended Events

E.

SQL Server Integration Services (SSIS) scripts

 

Answer: B

Explanation: A fully contained database includes all the settings and metadata required to define the database and has no configuration dependencies on the instance of the SQL Server Database Engine where the database is installed.

Contained Databases

 

 

QUESTION 134

DRAG DROP  (Topic 12)

 

You plan to deploy a database to SQL Azure.

 

You are designing two stored procedures named USP_1 and USP_2 that have the following requirements:

 

Prevent data read by USP_1 from being modified by other active processes.

Allow USP_2 to perform dirty reads.

 

You need to recommend the isolation level for the stored procedures.

 

The solution must maximize concurrency.

 

Which isolation levels should you recommend?

 

To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.

 

 

 

clip_image002

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 135  (Topic 12)

 

You are designing a SQL Server database for an order fulfillment system.

 

You create a table named Sales.Orders by using the following script:

 

clip_image004

 

Each order is tracked by using one of the following statuses:

 

 

Fulfilled

Shipped

Ordered

Received

 

You need to design the database to ensure that you can retrieve the status of an order on a given date. The solution must ensure that new statuses can be added in the future.

 

What should you do?

 

More than one answer choice may achieve the goal. Select the BEST answer.

 

A.

Implement change data capture on the Sales.Orders table.

B.

To the Sales.Orders table, add a column named Status that will store the order status. Update the Status column as the order status changes.

C.

Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes.

D.

To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the value of each column from null to the appropriate date as the order status changes.

 

Answer: B

 

 

QUESTION 136  (Topic 12)

 

You are the new database administrator for a SQL Server 2014 instance.

 

You conduct an assessment on the instance and determine that the auto create statistics setting on the database named DB1 has been turned off. You see no evidence that any maintenance has been occurring.

 

You want to set up monitoring to see if query performance is being affected.

 

You need to set up a monitoring process that will capture any cases where statistics could have been useful if they existed.

 

What should you do?

 < /b>

A.

Create a SQL Server Agent job to execute DBCC SHOWSTATISTICS on each of the

 

 

primary key columns in the database.

B.

Use the missing_column_statistics extended event.

C.

Query the sys.statistics system view to see all cases where the statistics were last needed.

D.

Write a query using the sys.dm_db_missing_index_group_stats DMV Joining to sys.indexes, filtering on is_hypothetical.

 

Answer: B

Explanation: The Missing Column Statistics event class indicates that column statistics that could have been useful for the optimizer are not available. By monitoring the Missing Column Statistics event class, you can determine if there are statistics missing for a column used by a query. This can cause the optimizer to choose a less efficient query plan than expected.

 

Missing Column Statistics Event Class

 

 

QUESTION 137

DRAG DROP  (Topic 12)

 

You use SQL Server 2014 Enterprise Edition. Your database contains a partitioned table named AuditData. AuditData is partitioned by year. Partition 1 contains data from the year 2010 and prior.

 

Management has decided to archive all AUDITDATA records from 2010 and prior. Management wants the records to be removed from the database entirely and provided to the backup team as a zipped text file. The data must no longer reside in the database. There is very little tolerance for performance degradation in your environment.

 

You need to remove all 2010 and prior data from the AuditData table by using the least amount of system resources possible.

 

Develop the solution by selecting and arranging the required SQL actions in the correct order.

 

You may not need all of the actions.

 

 

 

clip_image006

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 138  (Topic 12)

 

You create a stored procedure that retrieves all of the rows from a table named Table1.

 

You need to recommend a solution to ensure that all of the statements in the stored procedure can be executed if another transaction is modifying rows in Table1 simultaneously.

 

What should you recommend?

 

A.

Snapshot isolation

B.

A database snapshot

C.

Filegroups

D.

Indexes

 

Answer: A

Explanation: Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. Newer row versions created after the transaction has begun are ignored by the transaction.

Transaction Isolation Levels

 

 

QUESTION 139  (Topic 12)

 

You administer a SQL Server 2014 instance.

 

Users report that the SQL Server has seemed slow today. A large database was being restored for much of the day, which could be causing issues.

 

You want to write a query of the system views that will report the following:

 

Number of users that have a connection to the server Whether a user’s connection is active

Whether any connections are blocked

What qu
eries are being executed

 

Whether the database restore is still executing and, if it is, what percentage of the restore is complete.

 

Which system objects should you use in your query to best achieve this task?

 

A.

sys.dm_exec_requests, sys.dm_exec_sessions, sys.objects

B.

sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_exec_query_text,sys.objects

C.

sys.sysprocesses, sys.dm_exec_query_text, sys.objects

D.

sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_text

 

Answer: D

Explanation: * sys.dm_exec_requests

Returns information about each request that is executing within SQL Server.

* sys.dm_exec_sessions

 

 

 

Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more.

* sys.dm_exec_query_text

Returns the text of the SQL batch that is identified by the specified sql_handle.

 

sys.dm_exec_requests (Transact-SQL)

sys.dm_exec_sessions (Transact-SQL)

 

Incorrect:

* sys.dm_exec_query_stats

Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself.

* sys.objects

Contains a row for each user-defined, schema-scoped object that is created within a database.

 

 

QUESTION 140  (Topic 12)

 

You are creating a database that will store usernames and credit card numbers for an application.

 

You need to recommend a solution to store and reuse the credit card numbers in the database.

 

What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

 

A.

Data encryption

B.

Transparent Data Encryption (TDE)

C.

Encrypting File System (EPS)

D.

Data hashing

 

Answer: B

Explanation: If we are going to encrypt credit card number for storage, then we should have Data Encryption Key (DEK) for encrypting the credit card number. http://msdn.microsoft.com/en-us/library/bb934049.aspx

 

Free VCE & PDF File for Microsoft 70-469 Real Exam

Instant Access to Free VCE Files: MCSE|MCSA|MCITP…
Instant Access to Free PDF Files: MCSE|MCSA|MCITP…