Latest MCTS 70-432 Dumps Real Exam Download 31-40

Ensurepass

QUESTION 31

You administer a SQL Server 2008 instance.

You have a stored procedure that implements a database maintenance process.

You need to create a SQL Server Agent job that runs the stored procedure. You also need to ensure that the job is removed after successful completion.

What should you do?

 

A. Create a job that uses the Automatically delete job option.

B. Create an Alert that will be raised when the job completes. This Alert will run another job to delete the maintenance job.

C. Create a job that is assigned to the Database Maintenance category.

D. Create a job that is scheduled to run once.

 

Correct Answer: A

Explanation/Reference:

 

 

QUESTION 32
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 administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table.

The table has the following definition:

CREATE TABLE [OrderItems]

(OrderID INT NOT NULL,

OrderDate DATETIME NOT NULL,

OrderLine INT NOT NULL,

ProductID INT NOT NULL,

Quantity INT NOT NULL,

PriceEach MONEY NOT NULL,

);

Currently, the table is partitioned by Quarter.

You need to make the table unpartitioned.

What should you do?

 

A. Remove the clustered index from the table.

B.            Use the ALTER PARTITION FUNCTION … SPLIT RANGE statement.

C.            Use the ALTER TABLE statement to remove the COLLATE option.

D.            Execute the DBCC CLEANTABLE command on the OrderItems table.

E. Create a new Filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with NEXT USED clause Use ALTER INDEX REORGANIZE statement.

F.             Create a new Filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with NEXT USED clause Use ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause

G. Create a new table. Use the ALTER TABLE statement along with the SWITCH PARTITION clause. Use ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.

H. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data onto the partition scheme.

I.             Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG ALL TO ( SECONDARY );

J.             Run the following statement: EXECUTE sp_tableoption @TableNamePattern =’OrderItems’, @OptionName= ‘PartitionByYear’, @OptionValue= ‘true’;.

 

Correct Answer: A

Explanation/Reference:

 

 

QUESTION 33

You manage several SQL Server 2008 instances.

You plan to collect performance data periodically on all instances by using the data collector. All collected data must be stored in the same database hosted on a single instance.

You need to collect and load performance data in the management data warehouse every 6 hours.

Which data collection process should you implement?

 

A. Create a scheduled non-cache data collection.

B. Create two different SQL Agent jobs that are scheduled at the same time. One job creates a data collection and the other job uploads the data collection.

C. Create a cached data collection.

D. Create an on-demand non-cache data collection.

 

Correct Answer: A

Explanation/Reference:

 

 

QUESTION 34

You administer a Microsoft SQL Server 2008 R2 database instance.

You need to identify queries or stored procedures that are consuming server resources. You also need to view the query plan that is being used by each of these queries or procedures.

Which Dynamic Management Views (DMVs) should you use to find details about specific queries?

(Choose all that apply.)

 

A. dm_io_virtual_file_stats

B. dm_exec_query_stats

C. dm_exec_connections

D. dm_exec_query_plan

E. dm_exec_sql_text

 

Correct Answer: BD

Explanation/Reference:

http://www.docstoc.com/docs/46104087/How-To-Determine-and-Identify-Resource-Consuming-Queries-in-SQL-Server-2005 http://msdn.microsoft.com/es-es/library/ms189747(v=sql.105).aspx

 

 

QUESTION 35

You troubleshoot a SQL Server 2008 instance.

You suspect that an application uses an inefficient locking strategy and causes concurrency problems.

You need to identify the following:

The sessions that wait for a resource

The resource needed

The sessions that block the resource

Which dynamic management view should you use?

 

A. sys.dm_os_wait_stats

B. sys.dm_tran_active_transactions

C. sys.dm_exec_requests

D. sys.dm_os_waiting_tasks

 

Correct Answer: C

Explanation/Reference:

http://msdn.microsoft.com/es-es/library/ms177648(v=sql.105).aspx

 

 

QUESTION 36

You administer a SQL Server 2008 instance. The production server hosts databases for several missioncritical applications.

You plan to use the Resource Governor to limit the effect of queries that are executed by Microsoft SQL

Server Management Studio.

You need to ensure that the following requirements are met:

Queries initiated through SQL Management Studio do not exceed 20 percent of CPU utilization.

Queries initiated by the mission-critical applications are allowed to consume 100 percent utilization when required.

What should you do?

 

A.            Alter the default resource pool and set the MAX_CPU_PERCENT option to 20. Assign this resource pool to the workload group used by SQL Server Management Studio.

B.            Alter the default resource pool and set the MAX_CPU_PERCENT option to 20. Assign this resource pool to the workload group used by the mission-critical applications.

C.            Create a new resource pool and set the MAX_CPU_PERCENT option to 20. Assign this resource pool to the workload group used by SQL Server Management Studio.

D.            Create a new resource pool and set the MAX_CPU_PERCENT option to 20. Assign this resource pool to the workload group used by the mission-critical applications.

 

Correct Answer: C

Explanation/Reference:

First, you should create a new resource pool and set the MAX_CPU_PERCENT option to 20. Then assign this resource pool to the workload group used by SQL Server Management Studio. Explanation: You can use Resource Governor in a variety of ways to monitor and manage the workloads on your SQL Server system. This topic provides an overview of how to configure Resource Governor and illustrates how Resource Governor can be used. The scenarios that are provided include Transact-SQL code examples for creating and changing workload groups and resource pools. Configuring Resource Governor After you install SQL Server 2008, Resource Governor is available for use but is not enabled. The internal and default workload groups and their corresponding resource pools exist. To create and use your own resource pools and workload groups, you must complete the following steps:

1. Create a resource pool that has the limits you specify.

2. Create a workload group that has the limits and policies that you specify, and identify the resource pool to which the workload group belongs.

3. Define and activate a classifier function that you want to use for incoming requests. When the preceding steps are completed, you can

 

 

QUESTION 37

You administer a SQL Server 2008 instance.

Users report that applications that run on the server perform poorly. You suspect that the performance issues are related to table scans.

You need to use an appropriate Windows System Monitor object to capture the appropriate information.

Which performance object should you use?

 

A. SQLServer:Databases

B. SQLServer:Memory Manager

C. SQLServer:Access Methods

D. SQLServer:Buffer Manager

 

Correct Answer: C

Explanation/Reference:

The Access Methods object in SQL Server provides counters to monitor how the logical data within the database is accessed.

 

 

QUESTION 38

You migrate an application from Microsoft SQL Server 2000 to Microsoft SQL Server 2008.

You need to monitor the SQL Server instance to record the use of features that will be discontinued.

What should you do?

 

A. Use the SQL Server 2008 Upgrade Advisor

B.            Use a SQL server-side trace that captures the Deprecation Announcement and Deprecation FinalSupport event classes.

C.            Use the SQL Server Profiler that captures the SQL:BatchCompleted and Exception event classes.

D.            Use a SQL server-side trace that captures the SQL:BatchCompleted and Exception event classes.

 

Correct Answer: B

Explanation/Reference:

 

 

QUESTION 39

You maintain a SQL Server 2008 instance.

You create a new SQL Agent job that includes a Windows Powershell job step. The Powershell job step uses the SQLCmd utility to transfer data between servers.

You need to ensure that an operator named ResponseTeam is notified by e-mail message if the job fails.

What should you do?

 

A. Enable Notifications. Create the ResponseTeam operator.

Direct the Notification to the ResponseTeam operator on failure of the Powershell job step.

B.            Create the ResponseTeam operator. Configure the job step proxy account to use the ResponseTeam operator account. Select the proxy account for the e-mail profile.

C. Enable Notifications. Create the ResponseTeam operator. Direct the Notification to the ResponseTeam operator on job failure.

D.            Create the ResponseTeam operator. Assign the ResponseTeam operator as the fail-safe operator. Enable the job.

 

Correct Answer: C

Explanation/Reference:

 

 

QUESTION 40

You maintain multiple SQL Server 2008 instances.

You develop a failure recovery strategy.

You need to find out the edition and the build of SQL Server that is deployed in your environment.

What should you use to find the required information?

 

A. DBCC HELP

B. EXEC xp_msver

C. SELECT @@VERSION

D. SELECT * FROM sys.dm_os_sys_info

 

Correct Answer: C

Explanation/Reference:

SELECT @@VERSION returns the edition as well as the build of SQL Server, eg:

Microsoft SQL Server 2008 R2 (RTM) – 10.50.1617.0 (X64) Apr 30 2011 19:25:40 Copyright (c)

Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 76XX: Service Pack 1)

EXEC xp_msver does not return an ‘edition’.

 

Download Latest MCTS 70-432 Real Free Tests , help you to pass exam 100%.