Maricopa Community Colleges  CIS275CH   19994-20036 
Official Course Description: MCCCD Approval: 03/23/99
CIS275CH 19994-20036 L+L 3 Credit(s) 4 Period(s)
Performance Tuning and Optimization of Microsoft Structured Query Language Server 6.5
Fundamentals of Microsoft (MS) Structured Query Language (SQL) Server 6.5. Data storage structures, indexes, indexing strategies, data modification as well as transactions and locks covered. SQL Server configuration emphasized. Prerequisites: CIS275CG and CIS275CF, or permission of instructor.
Go to Competencies    Go to Outline
 
MCCCD Official Course Competencies:
 
CIS275CH   19994-20036 Performance Tuning and Optimization of Microsoft Structured Query Language Server 6.5
1. Identify factors that impact performance. (I)
2. Explain how to tune Windows NT for the Structured Query Language (SQL) Server. (II)
3. Compare and contrast data storage structures. (III)
4. Explain indexes and how they are stored. (IV)
5. Summarize how data is retrieved in various situations. (V)
6. Explain data modification. (VI)
7. Describe methods for updating data. (VI)
8. Compare and contrast various indexing strategies. (VII)
9. Create indexes. (VII)
10. List the steps in processing a query. (VIII)
11. Summarize special optimizer strategies. (IX)
12. Explain how to tune the query optimizer. (X)
13. Configure a SQL Server. (XI)
14. Explain various transaction processing modes and isolation levels. (XII)
15. Identify and describe types of locks. (XIII)
16. Demonstrate ability to detect, isolate and fix performance problems. (XIV)
17. Explain the impact of installation decisions. (XV)
Go to Description    Go to top of Competencies
 
MCCCD Official Course Outline:
 
CIS275CH   19994-20036 Performance Tuning and Optimization of Microsoft Structured Query Language Server 6.5
    I. Performance Issues
        A. Goals
        B. Factors that Impact Performance
        C. Microsoft (MS) Structured Query Language (SQL) Server Architectural
        D. Performance Tuning Methodology
        E. Credit Database
      II. Tuning Windows NT for SQL Server
          A. NT Performance-Measuring Tools
          B. SQL Server's Use of NT Resources
          C. Load-Simulation Tools
        III. Data Storage Structures
            A. External
            B. Internal
            C. DBCC Statements
          IV. Indexes
              A. Usage
              B. Internals
              C. Types
              D. Options
              E. Basic Information
            V. Data Retrieval
                A. Table Scans
                B. Using Indexes
                C. Operations
                D. Logical Database Design
                E. Denormalization Techniques
              VI. Data Modification
                  A. Inserting Data
                  B. Deleting Data
                  C. Updating Data
                  D. Page Management
                  E. Denormalization Considerations
                VII. Indexing Strategies
                    A. Decision Support Systems (DSS)
                    B. Online Transaction Processing (OLTP)
                    C. Indexing for Retrieval
                    D. Creating Index
                    E. Matching Index to Query
                    F. Indexing for OLTP
                    G. Indexing Guidelines
                  VIII. The Query Optimizer
                      A. Basics
                      B. Phase 1: Query Analysis
                      C. Phase 2: Index Selection
                      D. Phase 3: Join Selection
                      E. Choosing the Best Plan
                      F. Query Processing Phases
                      G. SHOWPLAN Output
                      H. STATISTICS Output
                    IX. Special Optimizer Strategies
                        A. Alternative
                        B. Join
                        C. Special Cases
                        D. Stored Procedure Processing
                      X. Tuning the Query Optimizer
                        XI. SQL Server Configuration
                            A. Guidelines
                            B. Memory and Input/Output (I/O) Management
                            C. Data Cache
                            D. Procedure Cache
                            E. Other Options
                            F. Database and Session Configuration
                          XII. Transactions
                              A. Processing Modes
                              B. Isolation Levels
                              C. Logging
                            XIII. Locking
                                A. Types of Locks
                                B. Granularity
                                C. Controlling
                                D. Deadlock
                                E. Cursors
                              XIV. Performance Analysis and Prototyping
                                XV. Other Performance Issues
                                    A. Installation Decisions
                                    B. Batch Operations
                                    C. Referential Integrity
                                    D. Distributed Data
                                Go to Description    Go to top of Competencies    Go to top of Outline