|
2-Day (COURSE-DB-QUERYPERFORMANCETUNING)
Description
The course is designed to provide highly practical advice on performance tuning of T-SQL queries, then it discusses how to write better queries using advanced T-SQL concepts. The focus of the materials is on the actual problems typically encountered by our mentors during their client engagements.
The course provides a dedicated 2 days focusing on indexing and query tuning. These two days cover internals and index tuning, index access methods, temporary tables, set vs. cursors, SQL Server Execution Plans and query tuning using query revisions. Moreover, query tuning is in the heart of this course and is incorporated in the different modules throughout the course.
For details on booking courses or seminars, please visit our booking page.
Author
This course was developed by Greg Low is a well-known mentor with SolidQ and authors of several books about T-SQL and SQL Server. He has written for SQL Server Magazine, and is a regular speaker in international SQL Server related events.
Audience
- T-SQL Programmers, DBAs, Architects, and Analysts.
- Those that need to write or review T-SQL code in SQL Server 2005 and 2008.
- SQL Server developers who are responsible for developing SQL Server queries and stored procedures.
Note: This course assumes at least one year's practical experience writing T- SQL.
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
- At least one year of T-SQL querying and programming experience in SQL Server
- Knowledge of SQL Server Performance Tuning concepts
Delivery Format
2 days, 8 modules, including exercises and labs.
Course Outline
Day 1
Module 1 SQL Server Indexing Core Concepts
- Core Indexing Concepts
- Table Structure
- Index Effectiveness
- LAB
Module 02: Designing Effective Indexes for SQL Server
- Indexing for Constraints
- Clustering Keys and Data Types
- Indexing Strategies
- LAB
Module 03: Working with SQL Server Execution Plans
- Core Execution Plan Concepts
- Common Execution Plan Elements
- Query, Table and Join Hints
- LAB
Module 04: Maintaining SQL Server Indexes and Statistics
§ Index and Heap Fragmentation
§ Index Maintenance
§ Optimizing Indexes
§ LAB
Day 2
Module 05: Tracing and Profiling SQL Server Queries
- SQL Server Profiler
- Query Tracing using SQL Trace
- Identifying Queries to Optimize
- LAB
Module 06: SQL Server Transactions and Concurrency
§ Transaction Isolation Levels
§ Lock Modes, Hints, Duration, and DMVs
§ Deadlocks
Row Version Tables
§ LAB
Module 07: Ensuring Effective Query Plan Re-Use
§ Query compilation and plan caching
§ Tools for viewing caching and recompiles
§ Recompilation triggers
Best practices
§ LAB
Module 08: SQL Server Transactions, concurrency and tempdb
- SQL Server Cursor Types
- Sets vs Cursors
- Temporary Tables and Table Variables
- LAB
|