MCTS SQL Server 2008 Administration 2 — Questions and Answers
Question 1: Which SQL Server 2008 feature allows you to compress data at the row or page level to reduce storage space?
- Sparse columns
- Data compression (Correct answer)
- Filestream
- Vardecimal storage format
Correct answer: Data compression
Data compression in SQL Server 2008 supports ROW and PAGE compression, reducing storage and potentially improving I/O performance.
Question 2: You need to monitor SQL Server performance over time and capture a baseline. Which tool is BEST suited for this?
- SQL Server Profiler
- Performance Monitor (PerfMon)
- Data Collector and Management Data Warehouse (Correct answer)
- Activity Monitor
Correct answer: Data Collector and Management Data Warehouse
The Data Collector with Management Data Warehouse (MDW) in SQL Server 2008 is designed for automated, long-term performance data collection and baseline analysis.
Question 3: A database is set to the BULK_LOGGED recovery model. What happens to bulk operations in this model?
- They are fully logged like in FULL recovery model
- They are minimally logged, reducing log space usage (Correct answer)
- They are not logged at all
- They fail unless TABLOCK hint is used
Correct answer: They are minimally logged, reducing log space usage
BULK_LOGGED recovery model minimally logs bulk operations (like BULK INSERT, SELECT INTO) to reduce log growth while still supporting log backups.
Question 4: Which system view in SQL Server 2008 shows the current wait statistics for the SQL Server instance since it was last started?
- sys.dm_exec_requests
- sys.dm_os_wait_stats (Correct answer)
- sys.dm_exec_sessions
- sys.dm_os_schedulers
Correct answer: sys.dm_os_wait_stats
sys.dm_os_wait_stats returns information about all the waits encountered by threads that executed, helping identify performance bottlenecks.
Question 5: What is the purpose of the 'suspect_pages' table in the msdb database?
- To list pages that need defragmentation
- To track database pages that encountered 823 or 824 errors during reads (Correct answer)
- To identify pages locked by long-running transactions
- To store orphaned page allocations
Correct answer: To track database pages that encountered 823 or 824 errors during reads
The msdb.dbo.suspect_pages table records pages that failed with I/O errors (error 823/824/829), helping DBAs identify potential disk or corruption issues.
Question 6: You want to implement a maintenance solution that automatically rebuilds indexes with fragmentation above 30%. Which approach is MOST appropriate?
- Use DBCC SHOWCONTIG in a scheduled job
- Use sys.dm_db_index_physical_stats in a SQL Agent job with conditional ALTER INDEX REBUILD (Correct answer)
- Enable the auto_shrink database option
- Use DBCC DBREINDEX on all tables nightly
Correct answer: Use sys.dm_db_index_physical_stats in a SQL Agent job with conditional ALTER INDEX REBUILD
Querying sys.dm_db_index_physical_stats to check avg_fragmentation_in_percent and conditionally running ALTER INDEX REBUILD is the modern, recommended approach for index maintenance.
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?
- SQL Server Audit (Correct answer)
- C2 Audit mode
- SQL Trace
- DDL Triggers
Correct answer: SQL Server Audit
SQL Server Audit, introduced in SQL Server 2008, provides a structured mechanism to track and log server and database-level events for compliance.
MCTS SQL Server 2008 Administration 2
Which SQL Server 2008 feature allows you to compress data at the row or page level to reduce storage space?