|
Course Title:
|
End to End Performance Tuning Register Now!
|
|
Duration:
|
4 days
|
|
Overview:
|
This very pragmatic class focuses on teaching you the tools and techniques you need to solve performance issues. From indexing to blocking to query hints and denormalization, we cover the problems and solutions students will commonly see in production. This class is also one of the few that will train students how to work with developers to tune an entire application. Lots of hands on exercises and group discussion make sure the class is interesting and that the students leave knowing more than just theory.
|
|
Prerequisites:
|
Students should understand how to use TSQL to do standard data manipulation language (DML) operations, create stored procedures, and how to use the various join types. No previous experience with tuning or tuning tools is required.
|
|
Course Materials:
|
Students receive a printed copy of the course notes (more than 50 pages) and a copy of SQL Server Query Performance Tuning Distilled, Second Edition by Sajal Dam
|
|
Outline:
|
Indexing
Indexing
- How indexing works
- Single column vs multi column
- Asc vs desc index
- Clustered vs non clustered
- Index maintenance
- Index Tuning Wizard
- 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
- Forcing query plans
- Join types and hints
- Statistics
- Cursors
- Temp tables and table valued functions
- Nocount
- 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
- Replay
- 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, Fibre Channel, SAN
- 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
|