Braindump2go Free 70-433 Exam PDF Instant Download (31-40)

Microsoft 70-433 Exam Questions has already been updated recently! Braindump2go Provide you the Latest 70-433 Exam Dumps: 70-433 PDF and 70-433 VCE! Braindump2go helps you keep in step with Microsoft Official Exam Center!

Exam Code: 70-433
Exam Name: TS: Microsoft SQL Server 2008, Database Development
Certification Provider: Microsoft

Keywords: 70-433 Exam Dumps,70-433 Practice Tests,70-433 Practice Exams,70-433 Exam Questions,70-433 PDF,70-433 VCE Free,70-433 Book,70-433 E-Book,70-433 Study Guide,70-433 Braindump,70-433 Prep Guide

QUESTION 31
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

Answer: C

QUESTION 32
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

Answer: D

QUESTION 33
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

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 34
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

Answer: B
Explanation:
sys.dm_db_missing_index_details returns detailed information about missing indexes, excluding spatial indexes.

QUESTION 35
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

Answer: B

QUESTION 36
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

Answer: B

QUESTION 37
You have been tasked to delete a number of Database Mail messages that have been sent.
You need to delete all the emails that were sent more than one month ago.
Which Transact-SQL statements should you run?

A.    DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
EXEC msdb.dbo.sysmail_delete_log_sp @OneMonthAgo
B.    DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
EXEC msdb.dbo.sysmail_delete_mailitems_sp @OneMonthAgo
C.    DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
EXEC msdb.dbo.sysmail_delete_log_sp @OneMonthAgo,'Success'
D.    DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
EXEC msdb.dbo.sysmail_delete_mailitems_sp @OneMonthAgo,'Sent'

Answer: D
Explanation:
sysmail_delete_mailitems_sp [ [ @sent_before = ] 'sent_before' ]
[ , [ @sent_status = ] 'sent_status' ]
Permanently deletes e-mail messages from the Database Mail internal tables.
[ @sent_before= ] 'sent_before'
Deletes e-mails up to the date and time provided as the sent_before argument. sent_before is datetime with NULL as default. NULL indicates all dates.
[ @sent_status= ] 'sent_status'
Deletes e-mails of the type specified by sent_status. sent_status is varchar(8) with no default. Valid entries are sent, unsent, retrying, and failed. NULL indicates all statuses.
Database Mail messages and their attachments are stored in the msdb database.
Messages should be periodically deleted to prevent msdb from growing larger than expected and to comply with your organizations document retention program.
Use the sysmail_delete_mailitems_sp stored procedure to permanently delete email messages from the Database Mail tables. An optional argument allows you to delete only older e- mails by providing a date and time. E-mails older than that argument will be deleted.
Another optional argument allows you to delete only e-mails of a certain type, specified as the sent_status argument. You must provide an argument either for @sent_before or @sent_status. To delete all messages, use @sent_before = getdate().

QUESTION 38
You administer a SQL Server 2008 database that contains a table name dbo.Sales, which contains the following table definition:
CREATE TABLE [dbo].[Sales](
[SalesID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalesPersonID] [int] NULL,
[CommentDate] [date] NULL);
This table contains millions of orders.
You run the following query to determine when sales persons comment in the dbo.Sales table:
SELECT SalesID,CustomerID,SalesPersonID,CommentDate
FROM dbo.Sales
WHERE CommentDate IS NOT NULL
AND SalesPersonID IS NOT NULL;
You discover that this query runs slow.
After examining the data, you find only 1% of rows have comment dates and the SalesPersonID is null on 10% of the rows.
You need to create an index to optimize the query.
The index must conserve disk space while optimizing your query.
Which index should you create?

A.    CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CustomerID)
INCLUDE (CommentDate,SalesPersonID);
B.    CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (SalesPersonID)
INCLUDE (CommentDate,CustomerID);
C.    CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CustomerID)
INCLUDE(CommentDate)
WHERE SalesPersonID IS NOT NULL;
D.    CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CommentDate, SalesPersonID)
INCLUDE(CustomerID)
WHERE CommentDate IS NOT NULL;

Answer: D

QUESTION 39
You work as a network database administrator at ABC.com.
ABC.com has a database server named ABC-DB01 that hosts the Inventory database.
ABC.com has a user named Rory Allen has the SQL login rallen.
Rory Allen was granted the DELETE permissions to Stock table in the Inventory database.
How would you configure ABC-DB01 to revoke Rory Allen's SELECT permissions to the Stock table without disrupting other permissions he has to the Inventory database?

A.    By running the ADD USER rallen Transact-SQL statement.
B.    By running the TRUNCATE Inventory.Stock FROM rallen Transact-SQL statement.
C.    By running the DENY DELETE ON Inventory.Stock TO rallen Transact-SQL statement.
D.    By running DELETE SELECT ON Inventory.Stock TO rallen Transact-SQL statement.

Answer: C

QUESTION 40
You are creating a new table in a database.
Your business requires you to store data in the table for only seven days.
You need to implement a partitioned table to meet this business requirement.
Which tasks should you complete?

A.    Create the partition function
Create the partition scheme
Create the table
B.    Create the partition function
Create the table
Create a filtered index
C.    Add a secondary file to the primary filegroups
Create the table
Create the distributed partitioned view
D.    Create the partition function
Create the partition scheme
Create the distributed partitioned view

Answer: A

http://www.braindump2go.com/70-433.html