Overview
Performance tuning in SQL Server requires a broad range of knowledge, from server configuration to index management to reading query plans - and a lot more. The goal of this class is to introduce all the major tuning concepts and give the student enough knowledge in each area to be able to return to the office and solve common performance issues. We will also explore a number of free tools that enhance the performance tuning process.
Prerequisites
Students should have a basic understanding of TSQL, including joins, but no other experience is required. This class is suitable for beginning to intermediate database administrators as well as developers or developer leads.
Duration
3 days
List Price
$1999
Course Outline
Indexing
Indexing
- How indexing works
- Single column vs multi column
- Asc vs desc index
- Clustered vs non clustered
- Index maintenance
- Indexed Views
- Computed Columns
- Data Types you should not index
Locking & Blocking
- Transaction isolation levels
- Concurrency
- Lock types
- Locking hints
- Detecting blocking
- Deadlocks
Query Plans & Tuning
- Options for seeing plans
- Understanding text and graphical plans
- Multi statement plans
- How Set options can affect your plan
- Status vs dynamic sql
- Stored procedures
- Recompiles
- Plan caching
- Dealing with bad query plans
- Plan Guides
- Join types and hints
- Statistics
- Cursors
- Temp tables and table valued functions
- Batches
Profiler
- Introduction to Profiler
- Server side or client side capture
- Capturing execution plans using Profiler
- Filtering results
- Reads, writes, cpu time
- Building trace templates
- Grouping
Perfmon/Sysmon
- Introduction to Perfmon
- Which performance counters should you look at first?
- Determining what the results mean
- Logging perfmon output
- Correlating perfmon output with Profiler (2005 only)
- User defined counters
Server Performance
- RAID
- Disk options: IDE, SATA, SCSI, SAS, Fibre Channel, SAN, SSD
- Disk speeds and sizes
- Block size, stripe size
- Read/write caching
- Testing disk performance
- Using more than 2g of memory/AWE/PAE
- Multiple CPU's/Hyperthreading
- Network IO
- Expanding data/log files
- Evaluating data access changes
Application Tuning
- Connection pooling
- Client server vs n-tier
- Counting round trips
- Benchmarking
- Mapping data access routes
- Looking for caching opportunities
- Handling deadlocks and timeouts in apps
|
|
|