Microsoft SQL Server 2000 Optimization Guide

JENNEY LYNNE FIELDS is President and Senior Consultant for Sutton Technology Group, Inc., Sutton, MA. She holds MCSE, MCT, and Novell CNE certification. UCI Corporation has been training technical IT professionals since 1984. UCI provides training services, curriculum development, and consulting services to programmers, and system support engineers. UCI expertise includes Internet, Programming, and System technologies. Headquarters are in Massachusetts: 800-884-1772, or visit their web page at http://www.unicorp.com

Foreword.


Preface.
I. OVERVIEW AND ARCHITECTURE.

1. Overview of SQL Server 2000.


Enhancements to SQL Server 2000. Large Memory Support. Internet Support. SQL Server V7.X Enhancements. Performance Analysis Tools. Resource Consumers. Key Considerations for Tuning SQL Server. Why Are Tuning and Optimization Necessary? Summary.

2. The Storage Engine.


Introduction to the Storage Engine. Storage Engine Features. Subsystem Overview. Implementation Considerations. Data Organization. Storage Layout. Database Files. System Databases. Creating, Altering and Dropping Databases. Filegroups. Maintaining Data and Log Files. Table Organization. Data Types. Indexes. Managing the Transaction Log. Database Locking Mechanisms. Memory Utilization. Processor Utilization. Summary.

3. Query Processor.


Introduction to the Query Processor. Query Execution. Query Optimization. Types of Query Optimizers. Statistics and the Query Optimizer. Improvements to the SQL Server Query Processor. Internet Support. Partitioning Views-Federated SQL Server 2000 Servers. Efficiency of Data Transfer. Query Governor. Query Optimization Techniques. Join Techniques. Hints. Indexing Strategies. Parallel Execution of Queries. Stored Procedures. Multiphase. Automatic Parameters. Transitive Predicates. Star Queries. Optimization for Data Modifications. Tools for Working with Queries. Summary.

II. PERFORMANCE CONSIDERATIONS.

4. Hardware Considerations.


Introduction. General Hardware Bottleneck Considerations. Disk Configurations. RAID or Not to RAID. Understanding RAID Levels. Hardware RAID vs Software RAID. Disk and Controller Considerations. Understanding Disk I/O. Transfer Rates. Disk Transfer Rates. Calculating Throughput. Determine Capacity of Controllers. PCI Bus Bandwidth. Tape Devices. Summarizing Disk I/O Subsystem Requirements. Suggestions for Improving Disk I/O. The More Hardware the Better. Read-Ahead Manager. Separate Activities. Disk Considerations for SQL Server and Windows NT/2000. Memory Considerations. Processor Considerations. Network Considerations. Summary.

5. Implementing High Availability Solutions.


Introduction to High Availability Solutions. Introduction to Clustering. Microsoft Cluster Server Implementation. Clustering Configurations. How Failover Works. SQL Server on Microsoft Cluster Server. Resource Issues Running SQL Server in a Cluster. Multiple Instances of SQL Server. Federated SQL Server 2000 Servers. Backup and Restore Capabilities. Log Shipping. Stand-By Server. SQL Server Replication. Summary.

6. Application Considerations.


Application Design Considerations. Normalization. Introduction to Indexes. Index Components. Clustered Indexes. Tables without Clustered Indexes. Clustered Index Example. Nonclustered Indexes. Example of a Nonclustered Index when a Clustered Index Exists. Example of a Nonclustered Index when a Clustered Index Has Been Dropped. Indexing Options. Creating Indexes. Optimizing Index Creation. Composite Indexes. Covering Indexes. Indexing Views. Index Selection. Understanding Index Selectivity and Density. Indexing Factors Affecting Query Performance. Tools for Managing Indexes. DBCC for Managing Tables, Indexes and Statistics. Summary.

III. DIAGNOSING AND RESOLVING PROBLEMS-TUNING AND OPTIMIZATION.

7. Performance Tuning with Windows NT/2000 Tools.


Monitoring Objectives. Create a Baseline. Windows NT/2000 Performance Monitor. Introduction to Performance Monitor. Performance Monitor Options. Monitor Each Key Area. Understanding CPU Utilization. Understanding Disk Utilization. Understanding Page Faulting-The Memory Object. Windows NT/2000 Objects-Process. Windows NT/2000 Objects-Thread. Windows NT/2000 Objects-Network. SQL Server Objects. SQL Server Default Performance Monitor Workspace File. SQL Server Object-Access Methods. SQL Server Object-Backup Device. SQL Server Object-Buffer Manager. SQL Server Object-Cache Manager. SQL Server Object-Databases. SQL Server Object-General Statistics. SQL Server Object-Locks. SQL Server Object-Memory Manager. SQL Server Object-Replication Agents Object. SQL Server Object-Replication Distribution Object. SQL Server Object-Replication Logreader Object. SQL Server Object-Replication Merge Object. SQL Server Object-Replication Snapshot Object. SQL Server Object-Statistics. SQL Server Object-User Settable Objects. Monitoring Performance with the Windows NT/2000 Control Panel. Virtual Memory. Application Responsiveness. Services. Network Bindings. Task Manager. System Information / Windows NT Diagnostics. Summary.

8. Monitoring and Configuring SQL Server.


Introduction. Enterprise Manager. sp_configure. Monitoring Activity. sp_monitor. Viewing and Modifying Database Options. Managing the Configuration of SQL Server. Monitoring User Activity. Monitoring and Managing the Integrity of the Database. Summary.

9. Automating SQL Server Tasks.


Automating the Monitoring of SQL Server. Database Maintenance Plan. Jobs. Alerts. Creating Alerts. Summary.

10. SQL Server Profiler.


Introduction to Server Profiler. Monitoring Events. Event Categories. Security Audit. Data Columns. Filters. General Trace Information. Sample Traces. Create a Trace Template. Create a Trace to Capture an SQL Server Workload. Reading the Trace File. Replay. Summary.

11. SQL Server Query Analyzer.


Introduction to the Query Analyzer. Execute SQL Statements and Commands. Object Browser. Color Coded for Ease of Use. Viewing Statistics, CPU Times, and Execution Profile. Performing Index Analysis. Create a Trace File from Query Analyzer. Analyze Data from SQL Server Profiler. ShowPlan and Graphical ShowPlan. Execute a Query with Graphical ShowPlan. Understanding Graphical ShowPlan. Performance Considerations. Interpreting Node Information. Reading Red Flags. Creating Missing Statistics. Managing Statistics. Managing Indexes. Re-Execute Query. Verify Successful Tuning Analysis. Summary.

12. Index Tuning Wizard.


Introduction to the Index Tuning Wizard. Using the Index Tuning Wizard. Select Tables to Tune. Index Tuning Recommendations. Analysis of Index Tuning Wizard Recommendations. Implementing Recommendations. Reviewing the Script File. Recommendations. Summary.

APPENDIX A Resources.
APPENDIX B Contents of the CD.


I. SYSTEM REQUIREMENTS. II. GETTING STARTED-LAUNCHING THE CD. Windows Users. Macintosh Users. III. NAVIGATING THE MEDIA EDITION-General Guidelines. IV SUPPORT.

INDEX.