|
|
|
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 |
|
|
|
Exams:
-
There are no exams directly associated with this course
|
|
Price Options ex VAT:
Classroom Training
? |
Distance Learning
? |
eLearning Options
? |
Book Learning
? |
£
1185
(€1683)
|
£
674.00
(€957)
|
- |
No Books Supported for Course at present
|
|
|
Call Free on 0800 169 1890
Print 2 Page Flyer
Last Modified 01 May 2008
|
|
