MCITP Administrator: Microsoft SQL Server 2005 Optimization and Maintenance (Exam 70-444) Study Guide

Introduction

TABLE I.1: SQL Server 2005 Editions and Minimum Operating System Version and Editions for 32-Bit Machines

Chapter 1: Optimizing the Performance of Databases and Database Servers

Table 1.1: System Stored Procedures

Table 1.2: SQL Server 2005 System Functions

Table 1.3: SQL Server Trace Flags

Table 1.4: CLR DMVs

Table 1.5: Full-Text Index DMVs

Table 1.6: I/O DMVs

Table 1.7: Query Notification DMVs

Table 1.8: Replication DMVs

Table 1.9: Service Broker DMVs

Table 1.10: SQL Operating System DMVs

Table 1.11: Memory-Related Error Messages

Table 1.12: tempdb-Related Error Messages

Table 1.13: _Common DBCCs

Table 1.14: Database DMVs

Table 1.15: Query DMVs

Table 1.16: Transaction DMVs

Chapter 2: Optimizing the Performance of Queries

Table 2.1: Description of sys.dm_db_index_physical_stats Columns

Table 2.2: SQL Server 2005 Wait Types

Table 2.3: ANSI Transaction Isolation Levels

Table 2.4: Data Anomalies Prevented by Isolation Level

Table 2.5: SQL Server 2005 Entities That Can Request Locks

Table 2.6: SQL Server 2005 Resource Types

Table 2.7: SQL Server 2005 Lock Request Modes

Table 2.8: SQL Serer 2005 Lock Compatibility

Table 2.9: SQL Server 2005 Lock Request Status

Table 2.10: Lock Event Classes

Table 2.11: SQL Server 2005 Locking System Stored Procedures

Table 2.12: sys.dm_tran_locks DMV Columns

Table 2.13: SQL Server 2005 Table Hints

Table 2.14: Deadlock Related Lock Event Classes

Chapter 3: Optimizing and Implementing a Data Recovery Plan for a Database

Table 3.1: Example of a Full Backup Strategy

Table 3.2: Example of an Incremental Backup Strategy

Table 3.3: Example of a Differential Backup Strategy

Table 3.4: Example of Incremental and Differential Backup Strategy

Table 3.5: Example of a File Backup Strategy

Chapter 4: Designing a Strategy to Monitor a Database Solution

Table 4.1: SQL Trace System Stored Procedures

Table 4.2: Database Event Classes

Table 4.3: Errors and Warnings Event Classes

Table 4.4: Lock Event Classes

Table 4.5: Stored Procedures Event Classes

Table 4.6: T-SQL Event Classes

Table 4.7: Triggers versus Event Notifications

Chapter 5: Designing a Strategy to Maintain a Database Solution

Table 5.1: SSRS Trace Log Files

Chapter 7: Designing a Database Data Management Strategy

Table 7.1: Data Quality Attributes

Chapter 8: Designing a Strategy to Manage Replication

Table 8.1: The Default Settings for Objects Copied to the Subscriber

Table 8.2: Predefined Replication Agent Alerts

Chapter 10: Designing a Strategy to Manage and Maintain Database Security

Table 10.1: Service Account Choices and Recommendations

Table 10.2: SQL Server Subsystems

Table 10.3: Server-Level Permissions

Table 10.4: Server-Level Roles

Table 10.5: Fixed Database Roles