6-    Change Management

Policy-Based Management

-          Policy-Based Management Overview

-          Policy-Based Management Step By Step

-          Scripting Policy-Based Management

-          Policy-Based Management Implementation

-          DDL Trigger Syntax

-          Database Triggers

-          Server Triggers

Trigger Views

Sqlcmd

-          Scripting Overview

-          Executing sqlcmd from the Command Prompt

-          Executing sqlcmd from Management Studio

Creating Change Scripts

-          PowerShell

-          Version Tables

 

7-    Configuring the Server for Optimal Performance

What Every DBA Needs to Know about Performance

-          The Performance Tuning Cycle

-          Defining Good Performance

-          Focus on What’s Most Important

Optimizing the Server

Configuring Server Hardware

-          Windows Server System Reference Architecture

-          Windows Server Catalog

CPU

-          32-bit x86 Processors

-          x64

-          IA64

-          Choosing between x64 or IA64

-          Hyperthreading

-          Cache

-          Multi-Core

-          System Architecture

Memory

-          Physical Memory

-          Physical Address Space

-          Virtual Memory Manager

-          The Page File

-          Page Faults

-          Virtual Address Space

-          32-Bit System Memory Configuration

-          64-bit Systems

-          Memory Configuration Scenarios

I/O

-          Network

-          Disks

-          Storage Design

-          Designing a Storage System

-          Large Storage System Considerations: SAN Systems

-          Server Configuration

-          Fragmentation

 

8-    Optimizing SQL Server 2008

Application Optimization

-          Defining a Workload

-          System Harmony Is the Goal

The Silent Killer: I/O Problems

-          SQL Server I/O Process Model

-          Database File Placement

-          Tempdb Considerations

Table and Index Partitioning

-          Why Consider Partitioning?

-          Implementing Partitioning

-          Creating a Partition Function

-          Creating Filegroups

-          Creating a Partition Scheme

-          Creating Tables and Indexes

Data Compression

-          Row Compression

-          Page Compression

-          Estimating Space Savings

-          Monitoring Data Compression

-          Data Compression Considerations

Memory Considerations and Enhancements

-          Tuning SQL Server Memory

-          Windows 2008 Hot-Add CPU

-          Configuring SQL Server 2008 for Dynamic Memory on an X86 Platform

-          64-bit Versions of SQL Server 2008

-          Resource Governor

CPU Considerations

-          Data Locality

-          Cache Coherency

-          Affinity Mask

-          Max Degree of Parallelism (MAXDOP)

-          Affinity I/O Mask

-          Max Server Memory

-          Index Creation Memory Option

-          Minimum Memory per Query

 

9-    Monitoring Your SQL Server

The Goal of Monitoring

Choosing the Appropriate Monitoring Tools

Performance Monitor

-          CPU Resource Counters

-          Isolating Processor Bottlenecks

-          Disk Activity

-          Memory Usage

-          Performance Monitoring Tools

Monitoring Events

-          The Default Trace

-          SQL Trace

-          Event Notifications

-          SQL Server Extended Event Notifications

Monitoring with Dynamic Management Views and Functions

-          What’s Going on Inside SQL Server

-          Viewing the Locking Information

-          Viewing Blocking Information

-          Index Usage in a Database

-          View Queries Waiting for Memory Grants

-          Connected User Information

-          Filegroup Free Space

-          Query Plan and Query Text for Currently Running Queries

-          Memory Usage

Monitoring Logs

-          Monitoring the SQL Server Error Log

-          Monitoring the Windows Event Logs

Management Data Warehouse

-          System Data Collection Sets

-          Viewing Data Collected By the System Data Collection Sets

-          Creating Your Own Data Collection Set

-          Examining the Data You Collected

 

10-    Performance Tuning T-SQL

Physical Query Processing

-          Compilation

-          Tools and Commands for Recompilation Scenarios

-          Parser and Algebrizer

-          Optimization

Tuning Process

-          Database I/O Information

-          Working with the Query Plan

-          Join Algorithms

-          Index Access Methods

-          Data Modification Query Plan

-          Query Processing Enhancements on Partitioned Tables and Indexes

-          Gathering Query Plans for Analysis with SQL Trace

 

11-    Indexing Your Database

Noteworthy Index-Related Features in SQL Server

Partitioned Tables and Indexes

-          Why Use Partitioned Tables and Indexes?

-          Prerequisites for Partitioning

-          Creating Partitioned Tables

Filtered Indexes and Filtered Statistics

Index Maintenance

-          Monitoring Index Fragmentation

 

-          Cleaning Up Indexes

 

Database Tuning Advisor

-          Using the DTA to Tune Individual Queries

-          Too Many Indexes?

-          Tuning a Workload

 

12-    Replication

Replication Overview

-          Replication Types

-          Replication Components

Replication Models

-          Single Publisher, One or More Subscribers

-          Multiple Publishers, Single Subscriber

-          Multiple Publishers Also Subscribing

-          Updating Subscriber

-          Peer-to-peer

Implementing Replication

-          The Setup

-          Setting Up Distribution

-          Implementing Snapshot Replication

-          Implementing Transactional and Merge Replication

Peer-to-Peer Replication

Scripting Replication

Monitoring Replication

-          Replication Monitor

-          Performance Monitor

-          Replication DMVs

-          sp_replcounters