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

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?

Accepted Answer

Data Collector and Management Data Warehouse

Answer

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?

Accepted Answer

They are minimally logged, reducing log space usage

Answer

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?

Accepted Answer

sys.dm_os_wait_stats

Answer

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?

Accepted Answer

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

Answer

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.

(MCTS) Microsoft Certified Technology Specialist Practice Test

(MCTS) Microsoft Certified Technology Specialist Practice Test

MCTS SQL Server 2008 Administration 2