5-Day (COURSE-DB-QUERYPERFANDADVTSQL)

Description

The course focuses on two areas; firstly it 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.

In this course you will learn the details and capabilities of T-SQL in the following areas: Query Tuning; Subqueries, Ranking Functions, Joins and Set Operations; Aggregating and Pivoting Data; TOP and APPLY; Data Modification; Data Type Related Problems; Programmable Objects (Dynamic SQL, Views, User Defined Functions, Stored Procedures, Triggers, Transactions and Concurrency.

During the course you will learn how to use T-SQL to solve practical problems such as: Relational Division, Ranking, Missing and Existing Values (Islands and Gaps), Separating Elements, Tie Breakers, Running Aggregations, Pivoting and Unpivoting, Custom Aggregations, Histograms, Dynamic Analysis of Grouping Sets, TOP Problems, Paging, Median, Handling Sequences, and more.

You will learn how to tune your queries, how to develop efficient routines including user defined functions, stored procedures and triggers, work in multi-user environments with transactions and isolation levels, and use dynamic SQL securely and efficiently. 

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.

With each querying/programming problem the discussions will revolve around logical aspects, set-based vs. procedural programming and optimization of the solutions.

For details on booking courses or seminars, please visit our booking page.

Author

This course was developed by Greg Low and Itzik Ben-Gan. Both are well-known mentors with SolidQ and authors of several books about T-SQL and SQL Server. Both write for SQL Server Magazine, and a regular speakers 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

5 days, 20 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

Day 3

 Module 09: Subqueries

• Nested and correlated subqueries
• Derived Tables
• Common Table Expressions
• LAB

 Module 10: Joins and Set Operations

• Joins
• Join Strategies
• Set Operations
• LAB

Module 11: Ranking and Aggregating Data

• Analytical Ranking Functions
• The OVER Clause
• Running and Sliding Aggregations
• LAB

Module 12: Pivoting Data

• Pivoting and Unpivoting Data
• Custom Aggregations
• Grouping Sets 
• LAB

Day 4

Module 13: TOP and APPLY

• TOP
• APPLY
• Techniques for APPLY
• LAB

Module 14: Data Modification

• Inserting, Updating, Deleting Data
• Merging Data
• OUTPUT Clause
• LAB

Module 15: Full Text Indexing

• Full Text Indexing Concepts
• Implementing Full Text Indexing
• Full Text Best Practices 
• LAB

Module 16: Datatype Related Problems

• Date and Time Data Types 
• Manipulating Dates
• Datatype Enhancements
• LAB

 Day 5

Module 17: Programmable Objects

• Dynamic SQL
• Views , Functions, Stored Procedures and Triggers
• Table Type and Table-Valued Parameters 
• LAB

 Module 18: SQL Server Spatial Data

• Spatial Applications
• Spatial Data Types
• Spatial Indexing and Tuning
• LAB

Module 19: SQL CLR Integration

• SQL CLR Concepts
• Implementing SQL CLR
•  Best practices for SQL CLR
• LAB

Module 20: Exception Handling

• T-SQL and Errors
• Error Handling - pre 2005
• TRY/CATCH and SQL Server 2005+
• Error Handling in SQL CLR Code
• Error Handling on Clients 
• LAB