|
|
Learn to master the numerous aspects of performance tuning and optimization from this essential video guide. This multimedia course makes learning performance tuning easy through the use of practical, real-world (a table with over 7 million rows used for demonstration) exercises structured around every component within Management Studio, SQL Server Profiler and other sophisticated tools.
With this comprehensive video guide you'll:
• Learn techniques to detect performance problems and to configure SQL Server 2005 to 24/7 continuous diagnostics. • Understand how performance tuning fits into the different stages of a project's life cycle. • Identify the most common issues DBAs face day to day and provide solutions which actually work in enterprise environments to resolve common and specialized problems to optimization issues. • Uncover performance problems that have been introduced by the new features of SQL Server 2005 which can enhance developer productivity but reduce performance. • Benefit from live demonstration on how to detect and improve the performance of poorly performing queries. • Expand your knowledge on high performance table design, index architecture and maintenance, query filtering, sorting, operators, conditionals, joins, cross apply operator, aggregate grouping functions, correlated subqueries and stored procedure optimization. • Study transaction isolation levels, locking, blocking and deadlocks.
Master the use of troubleshooting, tuning and monitoring tools.
CD-ROM 1 - THE SCIENCE OF TUNING 1. Welcome to Performance Tuning & Optimization 2. High Performance Begins with Database Design 3. The All Important Indexes, Statistics & Locking 4. Tuning & Optimizing Queries and Stored Procedures 5. Enemies of Performance: Locking, Blocking and Deadlocks 6. Dynamic Management Views (DMV) & Functions (DMF) 7. Diagnosing & Fixing Common Performance Problems 8. Benchmarking and Establishing Performance Baselines 9. Data Warehouse, Data Mart, ETL & OLAP Cubes 10. Computer Management Utility & System Properties 11. SQL Server Management Studio 12. SQL Server 2005 Performance Dashboard Reports 13. Business Intelligence Development Studio 14. SQL Server Profiler & SQL Server Agent 15. Performance Monitor & Configuration Tools 16. Database Engine Tuning Advisor
CD-ROM 2 -TABLE DESIGN 1. Referential Integrity: Primary & Foreign Keys 2. Surrogate Primary Key and Natural Key 3. 3NF - Third Normal Form Design 4. 3NF Conversion of AW - AdventureWorks3NF 5. OLTP versus OLAP Design: Dimensional Modeling 6. Table Layout and Naming Conventions 7. Speedy Thin Tables with Thin Columns 8. Vertical Partitioning by Usage Frequency 9. Horizontal Partitioning Strategies 10. Quasi Real-Time Flat Reporting Tables 11. Auditing, History & Staging Tables 12. Star and Snowflake Schemas 13. Dimension and Fact Tables 14. High Performance Physical Database Design 15. Database Growing and Shrinking Options 16. Database Management DMVs & DMFs
CD-ROM 3 - INDEXING 1. Clustered Indexes - Heap Table - New Features 2. Non-clustered & Partitioned Index - FILL FACTOR 3. PK, FK and Unique Constraint Indexes 4. Nonkey Columns in Indexes for Covered Queries 5. Online & Parallel Indexes for 24/7 Operations 6. XML Indexes - Primary & Secondary 7. Indexes on Views, Computed Columns & Full Text 8. Determining Index Disk Space Requirements 9. Insert, Delete & Update Effects on Indexes 10. Indexing Related DMVs and DMFs 11. Index Deterioration - Detecting Fragmentation 12. Complete Rebuild: ALTER INDEX with REBUILD Option 13. Optimize Only: ALTER INDEX with REORGANIZE Option 14. Index Statistics - UPDATE STATISTICS 15. Single Row and Range of Rows Retrieval 16. High Performance Strategy for Choosing Indexes
CD-ROM 4 - QUERY & SPROC TUNING 1. Search Conditions: the WHERE Clause in Queries 2. Efficient Joining of Tables - Index Seek vs. Table Scan 3. Equi, Anti, Range, Self, Outer & Cross Apply Joins 4. Aggregations with GROUP BY & UNION of Sets 5. Pattern Matching, Functions, INTERSECT & EXCEPT 6. CTE - Common Table Expression & Dynamic SQL 7. Recursive CTE-s, Org Chart & Tree Processing 8. Correlated Subqueries vs. WHILE, IN vs. EXISTS 9. Query Related DMVs and DMFs 10. Index Selection, Key Distribution Statistics 11. Nested Loop Joins, Merge Join & Hash Join 12. GUI & Textual Query Plans in Management Studio 13. Join Hints, Query Hints and Table Hints 14. Stored Procedure Optimization & Procedure Cache 15. Temporary Tables, Table Variables & Recompilation 16. High Performance with Set-Based Programming
CD-ROM 5 - TRANSACTIONS & LOCKING 1. SQL Server 2005 Locking Protocol 2. Shared, Update, Exclusive, & Intent Locks 3. Row-level, Page-level & Table-level Locks 4. Locking and Blocking Simulation 5. Deadlock Simulation & Detection by SQL Profiler 6. Transaction Isolation Levels - Snapshot Isolation 7. sp_who, sp_lock and Activity Monitor 8. Blocking Detection with sys.dm_os_waiting_tasks 9. SQL Profiler Transactions & Locks Tracing 10. Blocking Transactions Server Standard Report 11. Performance Dashboard Report with Head Blocker 12. Detecting Deadlocks with Trace Flags 1204 & 1222 13. Transactions and Locking Related DMVs & DMFs 14. Locking Hints & Changing Isolation Levels 15. Application Resource Locks - sp_getapplock 16. Bottleneck and Workload Analysis
CD-ROM 6 - ADVANCED OPTIMIZATION 1. Designing Partitioned Tables and Indexes 2. Using Memory Efficiently for Performance 3. Analyze Query in Database Tuning Advisor 4. Database Engine Tuning Advisor for Indexes 5. Database Engine Tuning Advisor for Partitioning 6. Memory Consumption Report & DBCC memorystatus 7. Parallel Processing & Workflow Management 8. Optimizing Queries by Using Plan Guides 9. Plan Forcing & the sys.dm_exec_cached_plans DMV 10. Optimizing by Correlated datetime Columns 11. Snapshot Isolation, Plan Caching & Recompilation 12. Top 10 High Performance T-SQL Development Tips 13. Top 10 High Performance Administration Tips 14. Top 10 DW and BI Performance Issues 15. High Performance Tips for SSIS Packages 16.12 Step Performance Tuning Bootcamp
CD-ROM 7 - MONITORING PERFORMANCE 1. Troubleshooting Query Performance 2. Server-Side Tracing and Recording 3. Troubleshooting & Analysis with Traces 4. Performance Dashboard Reports 5. Correlating Profiler Trace & Perfmon Chart 6. System Monitor Performance Logs & Alerts 7. Trace Storage in SQL Server Profiler 8. Trace Event Selection in SQL Server Profiler 9. Filtering Configuration in SQL Server Profiler 10. Peakload & Bottleneck Analysis of Trace Data 11. Adhoc Monitoring with DMVs and DMFs 12. Recording Performance Data from DMVs and DMFs 13. High Performance CPU & Memory Configuration 14. High Performance Disk Configuration 15. Proper Capacity Planning Techniques 16. TOP 10 Performance Tuning Success Secrets
BONUS CD 1. 7 T-SQL Scripts
|