Ensurepass

QUESTION 121

You have run a server side trace that created 45 trace files. You want to load the trace files on your workstation in a database table called PerfData for further analysis. You need to load three files starting at c:\my_trace_38.trc. Which Transact-SQL statement should you use?

 

A.

SELECT * INTO PerfData

FROM ::fn_trace_gettable(‘c:\my_trace.trc’, 3)

B.

SELECT * INTO PerfData

FROM ::fn_trace_gettable(‘c:\my_trace_38.trc’, 3)

C.

SELECT * INTO PerfData

FROM ::fn_trace_gettable(‘c:\my_trace38.trc’, default)

D.

SELECT * INTO PerfData

FROM ( SELECT * FROM ::fn_trace_gettable

(‘c:\my_trace_38.trc’, default)

UNION ALL

SELECT * FROM ::fn_trace_gettable

(‘c:\my_trace_39.trc’, default)

UNION ALL

SELECT * FROM ::fn_trace_gettable

(‘c:\my_trace_40.trc’, default)

) Trc

 

Correct Answer: B

Explanation:

sys.fn_trace_gettable returns the content of one or more trace files in tabular form.

fn_trace_gettable ( ‘filename’ , number_files )

‘filename’

Specifies the initial trace file to be read. filename is nvarchar(256), with no default.

number_files

Specifies the number of rollover files to be read. This number includes the initial file specified in filename.

number_files is an int.

If number_files is specified as default, fn_trace_gettable reads all rollover files until it reaches the end of the trace.

 

 

QUESTION 122

You are using SQL Server Profiler to gather deadlock information. You need to capture an XML description of a deadlock. Which event should you use?

 

A.

Lock:Deadlock

B.

Showplan XML

C.

Deadlock Graph

D.

Lock:Deadlock Chain

 

Correct Answer: C

Explanation:

The Lock:Deadlock event class is produced when an attempt to acquire a lock is canceled because the attempt was part of a deadlock and was chosen as the deadlock victim. Use the Lock:Deadlock event class to monitor when deadlocks occur and which objects are involved. You can use this information to determine if deadlocks are significantly affecting the performance of your application.

You can then examine the application code to determine if you can make changes to minimize deadlocks.

The Deadlock Graph event class provides an XML description of a deadlock. This class occurs simultaneously with the Lock:Deadlock event class.

The Lock:Deadlock Chain event class is produced for each participant in a deadlock.

 

 

QUESTION 123

You are troubleshooting query performance on SQL Server 2008. You have profiler trace data in a table named PerfData. You need to determine which events are taking longer than one second of CPU time or run for more than two seconds. Which Transact-SQL statement should you use?

 

A.

SELECT TextData, Duration, CPU

FROM PerfData

WHERE EventClass = 12 AND

( CPU > 1000 OR

Duration > 2000 )

B.

SELECT TextData, Duration, CPU

FROM PerfData

WHERE EventClass = 12 AND

( CPU > 1000 OR

Duration > 2000000 )

C.

SELECT TextData, Duration, CPU

FROM PerfData

WHERE EventClass = 12 AND

( CPU > 1000000 OR

Duration > 2000 )

D.

SELECT TextData, Duration, CPU

FROM PerfData

WHERE EventClass = 12 AND

( CPU > 1000000 OR

Duration > 2000000 )

 

Correct Answer: B

Explanation:

Beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.

 

 

QUESTION 124

You are using the Database Engine Tuning Advisor (DTA) to analyze a workload. You need to save the recommendations generated by the DTA. Which command should you use?

 

A.

Preview Workload Table

B.

Export Session Results

C.

Import Session Definition

D.

Export Session Definition

 

Correct Answer: B

 

 

QUESTION 125

You need to capture and record a workload for analysis by the Database Engine Tuning Advisor (DTA). Which tool should you use?

 

A.

DTA utility

B.

Activity Monitor

C.

SQL Server Profiler

D.

Performance Monitor

 

Correct Answer: C

 

 

QUESTION 126

You have a database that uses stored procedures to perform INSERT, UPDATE, DELETE, and SELECT statements. You are tasked with providing a recommendation of indexes to be created and dropped from the database. You need to select the appropriate method to accomplish the task. Which method should you use?

 

A.

Index Usage DMVs

B.

Missing Index DMVs

C.

SQL Server Profiler

D.

Database Engine Tuning Advisor

 

Correct Answer: D

 

 

QUESTION 127

You are tasked with creating a workload that will be used by the Database Engine Tuning Advisor (DTA). You need to create a workload in an appropriate format. Which format should you choose? (Each correct answer represents a complete solution. Choose three.)

 

A.

XML File

B.

Transact-SQL Script

C.

SQL Server Event Log

D.

SQL Server Transaction Log

E.

SQL Server Profiler Trace File

F.

Performance Counter Log File

 

Correct Answer: ABE

Explanation:

Database Engine Tuning Advisor uses trace files, trace tables, Transact-SQL scripts, or XML files as workload input when tuning databases.

 

 

QUESTION 128

You need to build CREATE INDEX statements for all the missing indexes that SQL Server has identified. Which dynamic management view should you use?

 

A.

sys.dm_db_index_usage_stats

B.

sys.dm_db_missing_index_details

C.

sys.dm_db_missing_index_columns

D.

sys.dm_db_missing_index_group_stats

 

Correct Answer: B

Explanation:

sys.dm_db_missing_index_details returns detailed information about missing indexes, excluding spatial indexes.

 

 

QUESTION 129

You notice that a database server is responding slowly to queries. You run the following dynamic management views (DMV) query on the server.

 

SELECT TOP (10)

 

wait_type,

 

wait_time_ms

 

FROM sys.dm_os_wait_stats

 

ORDER BY wait_time_ms DESC;

 

The query returns a top wait type of SOS_SCHEDULER_YIELD.

 

You need to identify what is causing the server response issues.

 

Which resource should you investigate first?

 

A.

Disk

B.

CPU

C.

Memory

D.

Network

 

Correct Answer: B

 

 

QUESTION 130

You attempt to query sys.dm_db_index_usage_stats to check the status on the indexes in the Contoso database. The query fails and you receive the following error:

 

“The user does not have permission to perform this action.”

 

You need to have the least amount of permissions granted to access the dynamic management views. Which permissions should be granted?

 

A.

CONTROL

B.

VIEW SERVER STATE

C.

VIEW DATABASE STATE

D.

CREATE EXTERNAL ACCESS ASSEMBLY

 

Correct Answer: B

 

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

Try Microsoft 70-433 Free Demo