You are using the Database Engine Tuning Advisor (DTA) to analyze a workload. You need to save
the recommendations generated by the DT

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

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?

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

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

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?

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?

You are given a database design to evaluate. All of the tables in this database should have a
clustered index.
You need to determine the tables that are missing a clustered index by using the system catalog
views.
Which TransactSQL
statement should you use?

You need to identify which tables are referenced by name in a stored procedure that does not use
dynamic SQL.
Which catalog view should you use?

How many years of experience do you have in developing databases using SQL Server 2008?