Optimizing Microsoft SQL Server 7.0
(Microsoft Training Course: 2013) - 3 days - £1700 exc VAT

THIS COURSE HAS NOW RETIRED. PLEASE SEE THE SUPPLEMENTARY INFORMATION FOR DETAILS OF ALTERNATIVE COURSES.
This course provides students with the knowledge and skills necessary to optimize the Microsoft SQL Server version 7.0 environment, write efficient queries, and design enterprise client applications. It is intended for those who have considerable experience with SQL Server and want to optimize the product for the enterprise environment.

1. Overview of Enterprise Performance Issues
2. SQL Server Architecture and Monitoring
3. Physical Database Structures
4. Designing a SQL Server Environment
5. Introduction to Optimizing Queries
6. Index Strategies
7. Analyzing Queries
8. Integrating SQL Server in an Enterprise Solution

---

Supplementary Information This course has now retired and is no longer available. We suggest you look at Course 2790: Troubleshooting and Optimizing Database Servers Using Microsoft SQL Server 2005.

---

Module 1: Overview of Enterprise Performance Issues

• Performance Monitoring and Tuning Methodology
• Strategies for Tuning Performance
• Developing a Performance Tuning Methodology
• Establishing a Performance Baseline
• Detecting Performance Problems
• Performance Monitoring Tools
• Load Simulation Tools

Skills

• Identify performance goals.
  
• Identify factors that affect performance.
  
• Develop a performance monitoring and tuning methodology.
  
• Describe the tools available for monitoring performance.
  
• Describe the tools available for simulating loads.
  

top

---

Module 2: SQL Server Architecture and Monitoring

• Client-Server Components
• Client-Server Communication Process
• Relational Engine Components
• Storage Engine Components
• Introduction to Thread Architecture
• How SQL Server Uses Threads and Processors
• Modifying Default Values That Affect Threads and Processors
• Monitoring Thread and Processor Use
• Memory Architecture and Monitoring
• How SQL Server Uses Virtual Memory
• Modifying Default Values That Affect Memory
• Monitoring Memory Use
• Hard Disk Input/Output (I/O) Architecture and Monitoring
• How the Buffer Manager Uses the Buffer Cache
• Modifying Default Values That Affect Hard Disk I/O
• Optimizing the Network for SQL Server
• Lab: Monitoring SQL Server Performance

Skills

• Describe the components of SQL Server that are involved in client-server communication.
  
• Describe the client-server communication process involved in submitting and processing a query.
  
• Describe the relational engine of SQL Server and how it enhances server performance and self-tuning.
  
• Describe the storage engine of SQL Server and how it enhances server performance and self-tuning.
  
• Describe the thread architecture that SQL Server uses and be able to modify and monitor processor use.
  
• Describe the memory architecture that SQL Server uses and be able to modify and monitor memory use.
  
• Describe the hard disk input/output (I/O) architecture that SQL Server uses and be able to modify and monitor hard disk I/O use.
  
• Describe how to optimize the network for SQL Server.
  

top

---

Module 3: Physical Database Structures

• Introduction to Data Structures
• Using Allocation Pages
• IAM Pages Track Object Placement
• Introduction to Data Page Components
• How SQL Server Organizes text, ntext, and image Data
• How SQL Server Retrieves Stored Data
• How SQL Server Uses the sysindexes Table
• Finding Rows
• Choosing the Appropriate Clustered Index
• Modifying Data
• Updating Data
• Lab: Estimating the Size of Tables and Indexes

Skills

• Describe the physical database structures created with SQL Server.
  
• Explain how data is organized on pages in SQL Server.
  
• Describe how SQL Server retrieves stored data and uses indexes.
  
• Describe how adding, updating, and deleting data physically affects SQL Server tables.
  

top

---

Module 4: Designing a SQL Server Environment

• Growing Data and Log Files Automatically
• Designing a Disk System by Using RAID
• Introduction to RAID
• Comparing RAID Levels
• Hardware Versus Software Solutions
• Factors That Influence I/O Performance
• Estimating Disk Subsystem Performance
• Monitoring Disk Activity
• Designing a Disk System by Using Filegroups
• Introduction to Filegroups
• Using Filegroups for Performance
• Using Filegroups for Maintenance
• Guidelines for Creating Filegroups
• Distributing Data Across Disks by Using Filegroups
• Separating Tables and Indexes for Performance
• Combining Filegroups with Hardware RAID
• Preparing for System Failure by Using SQL Server Failover Clustering
• Introduction to Clustering
• Benefits of Windows Clustering
• Active/Passive Failover Clustering Configuration
• Active/Active Failover Clustering Configuration
• Preventing Data Loss by Using a Standby Server
• Setting Up and Maintaining a Standby Server Manually
• How Log Shipping Works
• Setting Up a Standby Server to Use Log Shipping
• Replacing the Primary Server
• Restoring the Primary Server
• Lab: Automating the Maintenance of a Standby Server.

Skills

• Manage database and log file growth.
  
• Use RAID to improve SQL Server 7.0 performance and availability.
  
• Use filegroups to improve performance.
  
• Describe the benefits of using SQL Server Failover Clustering.
  
• Use a standby server solution to prevent data loss.
  

top

---

Module 5: Introduction to Optimizing Queries

• Introduction to the Query Optimizer
• How the Query Optimizer Uses Cost-Based Optimization
• How the Query Optimizer Works
• Query Optimization Phases
• Caching Query Plan
• Setting a Cost Limit
• Obtaining Query Plan Information
• Querying the sysindexes Table
• Viewing STATISTICS Statements Output
• Viewing SHOWPLAN_ALL and SHOWPLAN_TEXT Output
• Viewing Graphical Showplan
• Elements of Graphical Showplan
• Reading Graphical Query Plan Output
• Indexing Fundamentals
• Understanding the Data
• Limiting a Search
• Determining Selectivity
• Determining Density
• Determining Distribution of Data
• Introduction to Statistics
• Updating Statistics
• Viewing Statistics
• Lab: Obtaining Index Information
• Lab: Viewing Index Statistics

Skills

• Explain the role of the query optimizer and how it works to ensure that queries are optimized.
  
• Use various methods for obtaining query plan information so that they can determine how the query optimizer processed a query and validate that the most efficient query plan was generated.
  
• Implement indexing strategies to reduce page reads and design indexes that can benefit from how the query optimizer processes queries.
  
• Understand how statistics are created, stored, maintained, and used by the query optimizer to optimize queries.
  

top

---

Module 6: Index Strategies

• Accessing Data
• Table Scans and Indexes
• Index Architecture and Navigation
• Using Row Identifiers to Access Data
• Using an Index to Cover a Query
• Introduction to Indexes That Cover a Query
• Locating Data by Using Indexes That Cover a Query
• Example of Single Page Navigation
• Example of Partial Scan Navigation
• Example of Full Scan Navigation
• Identifying Whether an Index Can Be Used to Cover a Query
• Determining Whether an Index Is Used to Cover a Query
• Guidelines to Creating Indexes That Can Cover a Query
• Using Index Tuning Tools to Improve Query Performance
• Indexing Strategies
• Evaluating I/O for Queries That Access a Range of Data
• Indexing for Multiple Queries
• Guidelines for Creating Indexes
• Lab: Analyzing How Queries Are Covered by Different Types of Indexes

Skills

• Explain the various ways that SQL Server can access data.
  
• Create indexes that cover queries.
  
• Use indexing tools to create useful indexes and to analyze a given query or batch.
  
• Identify indexing strategies that reduce page reads.
  

top

---

Module 7: Analyzing Queries

• Queries That Use the AND Operator
• Queries That Use the OR Operator
• Queries That Use Join Operations
• Selectivity and Density of a Join Clause
• How Joins Are Processed
• How Nested Loop Joins Are Processed
• Multimedia: How Merge Joins Are Processed
• Considerations When Merge Join Is Used
• How Hash Joins Are Processed
• Evaluating Query Performance
• Determining When to Override the Query Optimizer
• Overriding the Query Optimizer
• Testing and Reevaluating Query Performance
• Lab: Analyzing Queries That Use the AND and OR Operators
• Lab: Analyzing Queries That Use Different Join Strategies

Skills

• Analyze the performance gain of writing efficient queries and creating useful indexes for queries that contain the AND logical operator.
  
• Analyze the performance gain of writing efficient queries and creating useful indexes for queries that contain the OR logical operator.
  
• Evaluate how the query optimizer uses different join strategies for query optimization.
  
• Evaluate when to override the query optimizer.
  

top

---

Module 8: Integrating SQL Server in an Enterprise Solution

• Designing Enterprise Application Architecture
• Identifying Logical Layers
• Designing Physical Tiers
• Accessing Data
• Managing Distributed Components and Transactions
• Introduction to COM+ Services
• Managing Distributed Transactions
• Using Message Queuing
• Managing Connections
• Selecting a Connection Strategy
• Using Pooling
• Enabling MDAC Resource Pooling
• Controlling MDAC Resource Pooling
• Controlling the Number of Connections
• Lab: Managing Clients and Connections
• Lab: Adding Transaction Support to a Multi-Tier Application

Skills

• Describe enterprise application architecture, including logical layers, physical models, and clients.
  
• Manage distributed components and transactions.
  
• Manage connections from components and clients to a database.
  

top

---

Before attending this course, students must have the following pre-requisites:

• Course 832, System Administration of Microsoft SQL Server 7.0, or equivalent experience.
  
• Course 833, Implementing a Database Design on Microsoft SQL Server 7.0, or equivalent experience.
  

After completing this course, students will be able to identify performance goals and factors that affect SQL Server performance; describe the architecture of SQL Server and how it enhances server performance and self-tuning; describe the physical database structures within SQL Server; design a SQL Server environment that maximizes system and data availability and server performance; obtain and evaluate query plan information and statistics; identify and create indexing strategies that can reduce page reads; write efficient queries and determine whether to override the query optimizer; and design and manage enterprise application architecture.

This course has now retired and is no longer available. We suggest you look at Course 2790: Troubleshooting and Optimizing Database Servers Using Microsoft SQL Server 2005.