MCTS SQL Server 2008 Administration 2

Question 1

Which SQL Server 2008 feature allows you to compress data at the row or page level to reduce storage space?

Accepted Answer

Data compression

Answer

Sparse columns

Answer

Filestream

Answer

Vardecimal storage format

Question 2

You need to monitor SQL Server performance over time and capture a baseline. Which tool is BEST suited for this?

Accepted Answer

Data Collector and Management Data Warehouse

Answer

SQL Server Profiler

Answer

Performance Monitor (PerfMon)

Answer

Activity Monitor

Question 3

A database is set to the BULK_LOGGED recovery model. What happens to bulk operations in this model?

Accepted Answer

They are minimally logged, reducing log space usage

Answer

They are fully logged like in FULL recovery model

Answer

They are not logged at all

Answer

They fail unless TABLOCK hint is used

Question 4

Which system view in SQL Server 2008 shows the current wait statistics for the SQL Server instance since it was last started?

Accepted Answer

sys.dm_os_wait_stats

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_sessions

Answer

sys.dm_os_schedulers

Question 5

What is the purpose of the 'suspect_pages' table in the msdb database?

Accepted Answer

To track database pages that encountered 823 or 824 errors during reads

Answer

To list pages that need defragmentation

Answer

To identify pages locked by long-running transactions

Answer

To store orphaned page allocations

Question 6

You want to implement a maintenance solution that automatically rebuilds indexes with fragmentation above 30%. Which approach is MOST appropriate?

Accepted Answer

Use sys.dm_db_index_physical_stats in a SQL Agent job with conditional ALTER INDEX REBUILD

Answer

Use DBCC SHOWCONTIG in a scheduled job

Answer

Enable the auto_shrink database option

Answer

Use DBCC DBREINDEX on all tables nightly

Question 7

Which SQL Server 2008 feature enables row-level auditing of security-relevant events and writes results to the Windows Security event log or a file?

Accepted Answer

SQL Server Audit

Answer

C2 Audit mode

Answer

SQL Trace

Answer

DDL Triggers