Developing High-Performance SQL Server Databases @ Learning Tree

Learning Tree ,London, United Kingdom

May 2005 – London, United Kingdom

I attended this Developing High-Performance SQL Server Databases course to optimise the performance of UKTV’s websites.

Course content:

  • Fundamental Concepts
  • Analysing performance
    • Selecting an appropriate monitoring tool
    • Interpreting STATISTICS IO output
    • Pinpointing performance problems with Profiler data
  • Developing a monitoring plan
    • Establishing a performance baseline
    • Creating server-side Profiler traces
    • Monitoring SQL Server and the operating system with System Monitor
  • Managing Storage
  • Database architecture
    • Page and extent allocation
    • Controlling data placement with file groups
  • Defining tables
    • Selecting the correct data types
    • Specifying text and image locations
    • Examining internal page structures
  • Creating and managing indexes
    • Clustered vs. nonclustered
    • Defining indexed views
    • Analysing and repairing fragmentation
  • Memory and Locking
  • Managing memory
    • Buffer pool
    • Buffer manager
    • Lasywriter
    • Checkpoint
    • Log writer
  • Designing transactions
    • Investigating lock types and their compatibility
    • Choosing isolation levels
    • Designing transactions to limit lock duration
    • Resolving contention problems
    • Implementing row versioning
  • Optimising Queries
  • Query optimiser architecture
    • Phases
    • Strategies
    • Data access plans
    • Auto-parameterisation
    • Avoiding recompilation of queries
  • Maintaining up-to-date statistics
  • Index vs. column
  • Automatic vs. manual
  • Full-scan vs. sample
  • Distinguishing among query types
    • Point
    • Multipoint
    • Range
    • Prefix match
    • Extremal
    • Ordering
    • Grouping
    • Join
  • Designing effective indexes
    • Relating indexes to query types
    • Providing alternate access paths
    • Increasing sort efficiency
    • Reducing I/O with covering indexes
    • Implementing sparse indexes
  • Designing a Physical Data Model
  • Storing summarised data for faster retrieval
    • Defining roll-up tables
    • Materialising aggregated data with indexed views
  • Minimising response time by introducing redundant data
    • Maintaining redundant data with triggers
    • Enhancing performance with surrogate keys
    • Creating a read-only query database
  • Solving performance problems with partitioning strategies
    • Partitioning tables
    • Defining partitioned views
  • Monitoring with X-Events
    • Constructing packages
    • Analysing captured results