• Duration
    • 5 days
  • Price
    • $3,500 + GST

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.

Target 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 Summary Outline

Module 01: Introduction to SQL Server architecture

Module 01: Introduction to SQL Server architecture

  • Architecture
  • SQLOS
    • Query Processor architecture

Module 02: SQL Server CPU

  • Architecture of the CPU subsystem
    • NUMA
    • Hyperthreading
    • L2 cache and SQL 2012
  • SQL Server configurations affecting CPU usage
    • Affinity mask
    • Parallelism
  • Monitoring SQL Server CPU
    • Monitoring and thresholds
    • Tuning methodology

Module 03: Introduction of SQL Server memory architecture

  • Memory architecture
  • Memory pools
  • SQL Server configurations affecting memory usage
    • AWE
    • MIN/MAX
    • Dynamic memory
  • Performance monitoring and tuning SQL Server memory
    • Monitoring and thresholds
    • Tuning methodology

Module 04: Physical Storage

  • Introduction to disk subsystem terminology and architecture
  • Disk subsystem RAID levels
  • Virtualization of SQL Server

Module 05: SQL Server files and filegroups

  • Files and their usage
  • Filegroups and their usage
  • Monitoring file activity and size

Module 06: Physical Architecture of Databases

  • Database architecture
    • Storage internals

Module 07: Transaction log and tempdb

  • Working with SQL Server transaction log
    • Transaction log architecture
    • Transaction log configuration (recovery models)
  • Working with SQL Server tempdb
    • Configuration of tempdb
    • Monitoring tempdb

Module 08: Performance monitoring and tuning the SQL Server disk subsystem

  • Monitoring and thresholds
  • Tuning methodology

Module 09: Introduction to SQL Server performance tuning tools

  • PerformanceStudio
  • Profiler
  • Sysmon
  • DMVs and DMFs
  • SQLDiag
  • Trace Flags
  • Error Logs
  • Third Party Tools

Module 10: SQL Server locking

  • Locks and latches
    • Locking architecture
    • Locking basics
  • Monitoring
    • Waits and Queues
    • Locking metadata

Module 11: SQL Server concurrency

  • Blocking
  • Deadlocks
  • Isolation levels

Module 12: Data types

  • Native
  • SQL CLR

Module 13: Uniderstanding statistics

  • Basics
    • density
    • cardinality
    • selectivity
    • granularity
  • Showing
  • Multicolumns statistics

Module 14: SQL Server Indexing and index maintenance

  • Index basics
  • Special indexes
    • Columnstore
    • Xml
    • Spatial
    • FTS
    • Hierarchyid
  • Fragmentation
  • Correcting fragmentation issues

Module 15: Understanding SQL Server Execution Plans

  • Reading SQL Server execution plans

Module 16: Review of common query coding which causes poor performance

Module 17: 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 18: 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
  • Optimizing data loading using partitioning

Module 19: Optimizing data loading

  • Recovery modeling
  • Trace flags
  • Optimizing data loading using partitioning

Module 20: SQL Server database maintenance

  • Database consistency checks
  • Physical defragmentation

Location Details

Sydney Mentor Francisco A González Díaz

Other SolidQ Courses