MS SQL server - internals & performance tuning

In MS SQL Server - Internals and Performance Tuning Training Course, Delegates will learn the internal architecture of SQL Server and the typical environment it operates in. This will enable them to better diagnose problems and improve the performance of their SQL Server environments. They will also dive through the various bottlenecks of SQL Server, learn how to optimize them, and troubleshoot common problems.

  • Basic knowledge of Microsoft Windows operating system and its core functionality
  • Working knowledge of database administration and maintenance
  • Working knowledge of Transact-SQL

  • SQL Server database administrators
  • Individuals responsible for writing queries against data that require optimal execution performance of workloads
  • Individuals who manage and maintain optimal performance of SQL Server instances
  • Application developers who deliver content from SQL Server databases

By attending MS SQL Server - Internals and Performance Tuning workshop, delegates will learn to:

  • Configure database physical storage and filegroups
  • Manage database compression
  • Troubleshoot common virtualization problems
  • Troubleshoot and optimize locking and concurrency issues
  • Optimize and maintain statistics
  • Read, understand and troubleshoot complex execution plans
  • Detect and optimize common poorly performing coding practices
  • Optimize data loading
  • Maintain databases for optimal performance

COURSE AGENDA

  • Review of locking in SQL Server
  • What is a deadlock (reminder)
  • Collecting deadlock graphs
  • Anatomy of a deadlock
  • Reading deadlock graphs
  • Resolving deadlocks
  • The Default Trace
  • Reading trace data using T-SQL
  • ReadTrace
  • SQLDiag
  • SQLNexus
  • Deadlock Analysis
  • 2012 Extended Events
  • Benchmark vs. baseline
  • Data collection methods and tools
  • Native tools:
  • Collector Sets and Counter Logs
  • PAL Tool (Performance Analysis of Logs)
  • SQL Trace
  • Introduction Distributed Replay Utility (DRU)
  • How to identify Memory issues (including how to distinguish them from other upstream issues)
  • Identify and troubleshoot Internal Memory pressure
  • Identify and troubleshoot External Memory pressure
  • Identify and troubleshoot Low Virtual Memory issues
  • All about Resource Semaphores
  • Introduction to memory internals changes in SQL Server
  • Understanding/analyzing plan cache
  • What's in the plan cache?
  • Understanding/optimizing stored procedures
  • A cautionary tale about scalar functions
  • Index analysis
  • Defining CPU performance issues
  • SQL Server vs. external CPU issues
  • Kernel vs. User time
  • Where to find the SQL Server CPU consumers
  • What to look for and how to remediate
  • Controlling CPU with Resource Governor
  • Why look at query plans?
  • How to capture query plans
  • How to analyze query plans
  • Understanding common operators
  • Know which plan patterns to watch for and investigate further
  • Methodology
  • The RUNNING-SUSPENDED-RUNNABLE-RUNNING cycle
  • Common wait types
  • Wait stats DMV usage
  • Extended events overview
  • Analyzing captured events
  • System health event session
  • Designing file/filegroup layout and partitioning
  • Data: logging, writing, scanning, restoring
  • Physical layout considerations
  • Tempdb
  • Server hardware and NUMA
  • CPU scheduling
  • Memory allocation
  • DMV monitoring and troubleshooting
  • Edge cases and "benchmark specials"
  • Defining storage terminology (IOPs, latency, etc.)
  • Magnetic drives and solid state drives
  • Building arrays with RAID
  • Mount points
  • Partition alignment
  • Testing with SQLIO and IOmeter
  • Direct attached storage including PCI-Express drives
  • SAN components
  • Shared vs. dedicated arrays
  • Pathing - Multipathing
  • Cache (on the drives, SAN controller, RAID controller, and Windows)
  • Snapshot backups (SAN, not SQL)
  • Clustering
  • SQL Server Extended Events UI
  • Extended Events advanced troubleshooting