5-Day (SQ-PERFTUN-401-EN)
Description
This five day intensive course is designed to give attendees a broad look at the performance tuning concepts and methods found in SQL Server 2005 and SQL Server 2008. This course is designed to prepare the SQL Server DBA for a transition to SQL Server 2005 or SQL Server 2008 while discussing best practices for a variety of performance topics that SQL Server DBAs will face in their day-to-day job functions.
Audience
This course is intended for:
- SQL Server administrators who are responsible for the performance of their database servers and installations.
- SQL Server developers who are responsible for developing SQL Server queries and stored procedures.
Prerequisites
Before attending this course, it is recommended that students have the following skills:
- Experience with SQL Server
- Understanding of database concepts
- Experience with SQL Server administration
- Experience with Transact-SQL programming
- Knowledge of SQL Server Performance Tuning concepts
Course Objectives
This course has been designed with the objective of providing a classroom setting in which the attendee not only learns the topic concepts listed in the course outline, they have an opportunity to learn common SQL Server performance tuning best practices and knowledge gained from the experiences of the instructor.
Course Outline
Module 1 - Introduction to SQL Server architecture
Module 2 - Introduction to SQL Server performance tuning tools
- Performance Studio
- Profiler
- Sysmon
- DMVs and DMFs
- SQLDiag
- Trace Flags
- Error Logs
- Third Party Tools
Module 3 - Introduction to SQL Server CPU
- Architecture of the CPU subsystem
- Hyperthreading
- Dual (Quad) cores
Module 4 - SQL Server configurations affecting CPU usage
- NUMA
- Affinity mask
- Parallelism
Module 5 - Performance monitoring and tuning SQL Server CPU
- Monitoring and thresholds
- Tuning methodology
Module 6 - Introduction of SQL Server memory architecture
- Memory architecture
- Memory pools
Module 7 - SQL Server configurations affecting memory usage
- AWE
- MIN/MAX
- Dynamic memory
Module 8 - Performance monitoring and tuning SQL Server memory
- Monitoring and thresholds
- Tuning methodology
Module 9 - SQL Server concurrency
- Locking architecture
- Locking basics
- Snapshot Isolation
Module 10 - Monitoring SQL Server concurrency
- Locking metadata
- Waits and Queues
Module 11 - SQL Server Indexing and index maintenance
- Index basics
- Fragmentation
- Correcting fragmentation issues
Module 12 - Introduction to the Query Processor
- Query Processor architecture
- Plan use and reuse
Module 13 - Understanding SQL Server Execution Plans
- Reading SQL Server execution plans
Module 14 - Review of common query coding which causes poor performance
Module 15 - Plan Guides, query hints, UDFs, and Computed Columns
- What are plan guides and how do you create them
- What are query hints and why they are used
- What are UDFs and do they cause performance issues
- What are computed columns
Module 16 - Introduction to disk subsystem terminology and architecture
Module 17 - Disk subsystem RAID levels
Module 18 - SQL Server files and filegroups
- Files and their usage
- Filegroups and their usage
- Monitoring file activity and size
Module 19 - Performance monitoring and tuning the SQL Server disk subsystem
- Monitoring and thresholds
- Tuning methodology
Module 20 - Working with SQL Server transaction log
- Transaction log architecture
- Transaction log configuration
Module 21 - Working with SQL Server tempdb
- Configuration of tempdb
- Monitoring tempdb
Module 22 - SQL Server partitioning (database and table)
- Why partition at the database level or table level
- Creating table partitions
- Working with table partitions
- Monitoring table partitions
Module 23 - SQL Server database maintenance
- Database consistency checks