Microsoft PRO:MS SQL Serv 08,Design,Optimize, and Maintain DB Admin Solu Sample Questions:

1. You administer a SQL Server 2008 instance.
You plan to deploy a new database to the instance. The database will be subject to frequent inserts and
updates.
The database will have multiple schemas. One of the schemas will contain a large amount of read-only
reference data.
You need to design the physical database structure for optimal backup performance.
What should you do?

A) Create the database by using a single data file and multiple log files.
B) Create the database by using a single log file and a filegroup that has multiple data files.
C) Create the database by using a single log file and multiple filegroups.
D) Create the database by using a single data file and a single log file.

2. You administer a SQL Server 2008 infrastructure.
A financial application uses several instances across multiple servers. The majority of queries require distributed transactions across all servers.
The application administrator reports that the application experiences excessive deadlocks.
You need to design a data collector type that provides the appropriate information for remote servers.
What collector should you use?

A) Performance Counters collector
B) A custom Query collector that uses WMI
C) T-SQL Query collector
D) Query Activity collector

3. You administer a SQL Server 2008 infrastructure.
Developers in your company have rights to author policies. A test server is used to develop and test the
policies.
The Policy-Based Management feature generates SQL Server Agent alerts when a policy is violated.
The developers are able to create and modify policies, but are unable to test policy violation alerts.
You need to grant the necessary permission to the developers to test the policies. You also need to comply
with
the least privilege principle when you grant the permission.
What should you do?

A) Add the developers to the PolicyAdministratorRole role in the MSDB database.
B) Add the developers to the sysadmin server role.
C) Grant the ALTER TRACE permission to the developers.
D) Grant the EXECUTE permission on the sys.sp_syspolicy_execute_policy stored procedure to the developers.

4. You are a professional level SQL Sever 2008 Database Administrator.
The capacity planning information is required by your company. A long-term infrastructure monitoring strategy should be implemented.
Which two actions should be contained in the strategy? (Choose more than one)

A) A maintenance plan that rebuilds indexes every week should be created.
B) After deployment of the production databases, the system should be basedlined.
C) The system log and the application log should be cleared every hour.
D) System monitor counters should be reviewed regularly.
E) All databases should be backed up every day.

5. You administer a SQL Server 2008 infrastructure.
You plan to design a maintenance strategy for a mission-critical database that includes a large table named Orders. The design plan includes index maintenance operations.
You must design the strategy after considering the following facts:
The Orders table in the database is constantly accessed.
New rows are frequently added to the Orders table.
The average fragmentation for the clustered index of the Orders table is less than 2 percent.
The Orders table includes a column of the xml data type.
You need to implement the strategy so that the performance of the queries on the table is optimized.
What should you do?

A) Reorganize the clustered index of the Orders table by decreasing the fill factor.
B) Exclude the clustered index of the Orders table from scheduled reorganizing or rebuilding operations.
C) Drop the clustered index of the Orders table.
D) Rebuild the clustered index of the Orders table offline once a month.

Solutions: