New Updated Microsoft MCSE 70-465 Real Exam Questions and Answers Download 81-90

Ensurepass

QUESTION 81

You have a query that is used by a reporting dashboard. Users report that the query sometimes takes a long time to run. You need to recommend a solution to identify what is causing the issue. What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

 

A.

Set the blocked process threshold, and then run SQL Server Profiler.

B.

Set the blocked process threshold, and then create an alert.

C.

Enable trace flag 1204, and then create an alert.

D.

Create a job that queries the sys.dm_os_waiting_tasks dynamic management view.

 

Correct Answer: B

Explanation:

Step 1: Turn on the blocked process report. This will look for any blocking taking 20 seconds or longer.

 

–Make sure you don’t have any pending changes

 

SELECT *

FROM sys.configurations

WHERE value &lt
;> value_in_use;

GO

 

exec sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE

GO

 

exec sp_configure ‘blocked process threshold (s)’, 20; GO

RECONFIGURE

GO

 

Step 2: Set up a trace to capture the blocked process report. Run it as a server side trace. blocked process threshold Server Configuration Option

 

 

QUESTION 82

You are troubleshooting an application that runs a query. The application frequently causes deadlocks. You need to identify the isolation level used by the query when a deadlock occurs.

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

 

A.

Query the sys.dm_exec_requests dynamic management view.

B.

Create a trace in SQL Server Profiler that contains the Deadlock graph event.

C.

Query the sys.dm_exec_sessions dynamic management view.

D.

Enable trace flag 1222, and then view the SQL Server error log.

 

Correct Answer: C

Explanation:

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

Include the column:

transaction_isolation_level

smallint

Transaction isolation level of the session.

0 = Unspecified

1 = ReadUncomitted

2 = ReadCommitted

3 = Repeatable

4 = Serializable

5 = Snapshot

Is not nullable.

sys.dm_exec_sessions (Transact-SQL)

 

 

 

 

QUESTION 83

You have four databases that are accessed by using an Online Transaction Processing
(OLTP) application. The databases are stored on a server named SQL1 that has SQL Server 2014 installed. You plan to deploy an additional server that has SQL Server 2014 installed.

 

You need to design a high-availability solution for the databases that meets the following requirements:

 

clip_image002[36]If SQL1 fails, the databases must be available.

clip_image002[37]Users must be able to run reports against a secondary copy of the databases.

 

What should you include in the design?

 

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

 

A.

AlwaysOn availability groups

B.

Database mirroring

C.

Log shipping

D.

Failover Clustering

 

Correct Answer: A

Explanation:

The AlwaysOn Availability Groups feature is a high-availability and disaster- recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together.

Failover and Failover Modes (AlwaysOn Availability Groups)

 

 

QUESTION 84

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:

 

clip_image002[38]Number of users that have a connection to the server

clip_image002[39]Whether a user’s connection is active

clip_image002[40]Whether any connections are blocked

clip_image002[41]What queries 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

 

Correct 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 85

You are designing a monitoring application for a new SQL Server 2014 instance. You need to recommend a solution to generate a report that displays the 10 most frequent wait types that occur for the instance. What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

 

A.

The SQL Server error log

B.

The sys.dm_os_wait_stats dynamic management view

C.

The DBCC SQLPERF(WAITSTATS) command

D.

SQL Server Profiler

 

Correct Answer: B

Explanation:

sys.dm_os_wait_stats

Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.

Columns include:

waiting_tasks_count

Number of waits on this wait type. This counter is incremented at the start of each wait. sys.dm_db_wait_stats (Windows Azure SQL Database)

 

 

QUESTION 86

You have a database hosted on SQL Server 2012 R2. The database contains 5 million rows.

You need to recommend a repeatable method to migrate the database to SQL Azure. Which method should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

 

A.

Create a SQL Server Integration Services (SSIS) package, and then run the package.

B.

Back up the database, and then restore the database.

C.

Extract a data-tier application, and then import the application.

D.

Generate scripts to create all of the all database objects and all of the data, and then execute the scripts by using SQL Azure.

 

Correct Answer: A

Explanation:

SQL Server Integration Services

Most flexibility

Data Transfer Efficiency: Good

 

/ SSIS can be used to perform a broad range of data migration tasks. SSIS provides support for complex workflow and data transformation between the source and destination. It is a good choice to transfer of data for databases that require many changes to work on Microsoft Azure SQL Database. You can use SSIS data transfer packages with another mechanism for transferring the database schema, such as a Data-tier Application package.

SSIS for Azure and Hybrid Data Movement

 

Incorrect:

Not D: Generate Scripts Wizard

Has explicit option for Azure SQL Database scripts generation Data Transfer Efficiency: Poor

 

Good for smaller database

 

/ Using the Generate Scripts wizard to migrate a SQL Server database to Azure SQL Database should be limited to:

Teams who have experience with the wizard.

 

Migrating simple databases that need few schema changes to run on Azure SQL Database. The scripts generated from the source database can be modified before being used to create the new version of the database on Azure SQL Database, but using a database project in the SQL Server Data Tools has richer support for making schema changes.

 

Migrating small databases that do not have much data. The wizard generates scripts that use insert statements instead of bulk copies to transfer the data. The insert statements can be throttled when the tables contain too much data, and are not as fast as bulk copies.

 

 

QUESTION 87

You are designing a database named DB1.

 

Changes will be deployed to DB1 every Wednesday night.

 

You need to recommend a strategy to deploy the changes to DB1. The strategy must meet the following requirements:

 

clip_image002[42]The strategy must not disrupt backup operations.

clip_image002[43]DB1 must be online while the changes are deployed.

clip_image002[44]You must be able to undo quickly any changes made to objects.

 

What should you recommend?

 

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

 

A.

Perform a copy-only database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.

B.

Create a database snapshot. If the deployment fails, recover the objects from the database snapshot.

C.

Create a database snapshot. If the deployment fails, revert the database to the database snapshot.

D.

Perform a full database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.

 

Correct Answer: C

Explanation:

Database Snapshots (SQL Server)

 

 

QUESTION 88

DRAG DROP

You plan to install two SQL Server 2014 environments named Environment1 and Environment.

 

Your company identifies the following availability requirements for each environment:

 

clip_image002[45]Environment1 must have mirroring with automatic failover implemented.

clip_image002[46]Environment2 must have AlwaysOn with automatic failover implemented.

 

You need to identify the minimum number of SQL Server 2014 servers that must be deployed to each environment to ensure that all data remains available if a physical server fails.< /font>

 

How many servers should you identify?

 

To answer, drag the appropriate number to the correct environment in the answer area.

 

clip_image003[4]

 

Correct Answer:

clip_image004[4]

QUESTION 89

DRAG DROP

You are designing a database for a university. The database will contain two tables named Classes and Enrollment that have the following specifications:

 

clip_image002[47]Classes will store brochures in the XPS format. The brochures must be structured in folders and must be accessible by using UNC paths.

clip_image002[48]Enrollment will store information about students and their classes. Performance must be enhanced for queries of the current enrollments.

 

You need to identify which SQL Server technology meets the specifications of each table.

 

Which technologies should you identify?

 

To answer, drag the appropriate technology to the correct table in the answer area.

 

clip_image005[4]

 

Correct Answer:

clip_image006[4]

 

 

QUESTION 90

You have a server that has SQL Server 2014 installed. The server contains 100 user databases.

 

You need to recommend a backup solution for the user databases.

 

The solution must meet the following requirements:

clip_image002[49]Perform a transaction log backup every hour.

clip_image002[50]Perform a full backup of each database every week.

clip_image002[51]Perform a differential backup of each database every day.

clip_image002[52]Ensure that new user databases are added automatically to the backup solution.

 

What should you recommend?

 

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

 

A.

Policy-Based Management

B.

A Data Definition Language (DDL) trigger

C.

SQL Server Agent jobs

D.

A maintenance plan

 

Correct Answer: D

Explanation:

Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies.

 

Maintenance plans can be created to perform the following task (among others):

Back up the database and transaction log files. Database and log backups can be retained for a specified period. This lets you create a history of backups to be used if you have to restore the database to a time earlier than the last database backup. You can also perform differential backups.

Maintenance Plans

 

Instant Access to Download Latest Complete Collection of Microsoft MCSE 70-465 Real Exam

Try Microsoft MCSE 70-465 Free Demo