Tuning SQL

• Basic understanding of SQL
• Familiarity with relational databases
• Experience with query writing and execution

Audience

• Database Administrators
• SQL Developers
• Data Analysts

Tuning SQL is a focused training program designed to equip participants with advanced skills in optimizing SQL queries and performance.

This instructor-led, live training (online or onsite) is aimed at database administrators, SQL developers, and data analysts who wish to enhance their SQL tuning expertise.

By the end of this training, participants will be able to:

• Work with user-defined data types (UDDT) and user-defined types (UDT) to enhance data modeling.
• Manage hierarchical data effectively, considering structure elements, storage methods, indexing strategies, and methods.
• Implement ranking and analytical features using the OVER clause, window functions, and various operators like GROUPING SETS, GROUPING_ID, ROLLUP, CUBE, PIVOT, UNPIVOT.
• Utilize temporary data and other temporary facilities for efficient query processing.
• Improve query performance through advanced data indexing techniques, including index selectivity, server suggestions for missing indexes, and measuring execution time with and without indexes.
• Create and maintain statistics to optimize cardinality estimation and address potential errors.
• Analyze execution plans for SQL queries, understand query plan reading, parameter sniffing, and code recompilations.
• Control the execution of SQL queries using hints, Extended Events, Database Engine Tuning Advisor, Data Collector, and Query Store.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

1. YOUR OWN DATA TYPES

• UDDT (User-Defined Data Types)
• UDT (User-Defined Types)

2. HIERARCHICAL DATA TYPE

• Structure elements
• Storage methods
• Indexing strategies
• Methods

3. RANKING AND ANALYTICAL FEATURES

• OVER clause
• Window functions
• Frame clauses
• Operators: GROUPING SETS, GROUPING_ID, ROLLUP, CUBE, PIVOT, UNPIVOT

4. TEMPORARY DATA

• Other temporary facilities

5. IMPROVING QUERY PERFORMANCE THROUGH DATA INDEXING

• Index selectivity index
• Index selectivity index
• Server suggestions for missing indexes
• Measuring the execution time of operations with and without indexes

6. CREATING AND MAINTAINING STATISTICS

• The construction of statistics and the principle of their operation
• Monitoring and maintaining statistics
• Cardinality estimation errors and statistics update

7. ANALYSIS OF PLANS FOR IMPLEMENTING INQUIRIES

• Running and reading query plans
• SNIFFING PARAMETER
• Code recompilations

8. CONTROL OF THE EXECUTION OF SQL QUERIES

• Hints (tips, hints for the server)
• Extended Events - (EE)
• Database Engine Tuning Advisor - (DTA)
• Data Collector - (DC)
• Query Store - (QS)