SQL Performance Pitfalls: Identification and Optimization Lab

This interactive workshop presents realistic examples of bad SQL programming that often lead to performance problems in practice. The aim of the session is to show participants how to identify, analyze and optimize inefficient SQL queries/SQL code in order to achieve significant performance improvements.
The workshop focuses on five to ten concrete, poorly implemented SQL coding scenarios. For each of these scenarios, the underlying problems are explained in detail and optimization approaches are then presented. Different techniques and best practices are presented that are essential for improving query speed and system efficiency.
With the help of practical lab exercises, load tests are generated to measurably compare performance before and after optimization. This allows participants to directly understand which measures lead to a significant reduction in response times and what impact they have on the overall system load.
At the end of the session, participants will have a better understanding of the most common pitfalls in SQL programming practices and will be able to perform in-depth performance analyses and make optimizations.

Mastering Ad-Hoc Queries in Microsoft SQL Server

Ad‑hoc queries are everywhere. From ORMs and reporting tools to quick‑and‑dirty admin scripts and user‑driven search interfaces. They are convenient, flexible, and often unavoidable.
But behind that convenience lurks a complex world of plan cache bloating, parameter sniffing chaos, memory pressure, and unpredictable performance. This session dives deep into the mechanics, the pitfalls, and the best practices of working with ad‑hoc workloads in SQL Server.

Intermediate to Advanced. Attendees should have a working understanding of SQL Server execution plans, indexing, and basic performance troubleshooting. No prior knowledge of plan cache internals is required.

Mastering DML Operations in SQL Server

INSERT, UPDATE, and DELETE operations look simple on the surface — but inside SQL Server, they trigger a complex chain of storage‑engine behaviors that can dramatically affect performance, concurrency, fragmentation, and long‑term maintainability. Whether your tables are heaps or clustered indexes, DML operations behave very differently under the hood, and understanding those differences is essential for building predictable, high‑performance systems.
This session takes you deep into the mechanics of DML in SQL Server. We’ll explore how INSERTs allocate pages and extents, why heaps generate forwarding records, and how clustered indexes maintain logical order through page splits and page merges. You’ll learn how updates can silently become deletes + inserts, why wide rows cause unexpected IO, and how delete patterns influence ghost records, version store usage, and index fragmentation.

Intermediate to Advanced. Attendees should be familiar with SQL Server storage structures, indexing fundamentals, and execution plans. No prior knowledge of allocation internals or log‑writing mechanics is required — these concepts will be introduced during the session.

Mastering Statistics in Microsoft SQL Server

Statistics are one of the most powerful — and most misunderstood — components of the SQL Server query optimizer. They drive cardinality estimation, influence join strategies, shape memory grants, and ultimately determine whether a query runs in milliseconds or minutes. When they’re fresh and accurate, statistics make SQL Server look brilliant. When they’re stale, incomplete, or misleading, they can bring even the best‑designed systems to their knees.
This session takes you deep into the world of SQL Server statistics: what they are, how they work, and why they matter. We’ll explore histogram structures, density vectors, cardinality estimation models, and how SQL Server uses statistics to choose execution plans. You’ll learn the difference between auto‑created and user‑created statistics, when filtered statistics shine, and how ascending keys, skewed data, and parameter sensitivity can sabotage even the most carefully indexed workloads.

Intermediate to Advanced. Attendees should have a solid understanding of indexing, query plans, and basic performance troubleshooting. No prior deep knowledge of histogram internals or cardinality estimation models is required — these concepts will be introduced and demystified during the session

Demystifying Clustered Indexes in SQL Server

Every SQL Server table tells a story—but whether that story is smooth and efficient or full of performance potholes often depends on a single design choice: should the table be a heap or should it have a clustered index?

In this session, we’ll peel back the layers of how SQL Server organizes data under the hood and shine a light on the differences between heaps and clustered indexes. You’ll see why the decision is more than just a checkbox, and how it can shape everything from query speed to storage efficiency.

We’ll compare the strengths and weaknesses of both approaches, uncover when heaps can actually be the better option, and show where clustered indexes truly deliver their value. Along the way, we’ll expose the hidden pitfalls—like forwarded records and fragmentation—that can quietly erode performance if left unchecked.

If you’ve ever wondered “What really happens to my data when I choose heap vs. clustered index?” this session is for you. Walk away with practical insights, clear models, and the confidence to make smarter choices when designing your SQL Server tables.

Your table design choice isn’t just technical—it’s transformational. In this session, we’ll unpack how SQL Server stores data, when heaps outperform expectations, and why clustered indexes often reign supreme. Expect real-world insights, performance tips, and a few surprises.

If you’ve ever asked “What really happens under the hood?”—this one’s for you.

The strengths of partitioning – practical examples

Partitioning is an advanced feature in Microsoft SQL Server that offers significant performance and manageability benefits. This session is tailored for experienced SQL Server DBAs looking to deepen their understanding of partitioning by exploring real-world showcases that highlight its transformative impact.

We will delve into two to four detailed case studies where partitioning has been successfully implemented to solve complex database challenges:

1. Accelerating a High Transactional OLTP production environment by up to 30% in a manufacturing company by partitioning machine data.

2. Optimizing the load of more than 10.000 cash register data into a big Data Warehouse of a retail company

3. recalculation of financial risks of a bank for daily reports for supervisory authorities

4. A financial services company leveraged partitioning to streamline their data archiving process, enabling faster data retrieval and compliance with regulatory requirements.

Throughout the session, we will discuss the technical implementation, performance metrics, and best practices for each showcase, providing attendees with actionable insights and strategies to apply partitioning in their own environments.

Partitioning in Microsoft SQL Server: A Beginner's Guide

Partitioning is a powerful feature in Microsoft SQL Server, designed to enhance manageability, and scalability of large datasets. This introductory session aims to demystify the concept of partitioning and provide beginners with a solid foundation to effectively leverage this feature in their database environments.

Participants will learn about the basic principles of partitioning and when to use each. The session will cover step-by-step instructions on how to create partitioned tables and indexes, along with practical tips for managing and maintaining partitions.

We'll also explore the performance benefits of partitioning, such as improved data loading, and efficient data archiving. Real-world examples and best practices will be shared to help attendees avoid common pitfalls and optimize their partitioning strategy.

Whether you're a novice DBA, a developer, or someone looking to optimize data management in SQL Server, this session provides the essential knowledge and tools to get started with partitioning.

Evolution of User-Defined Functions in Microsoft SQL Server

Since their introduction in SQL Server 2000, User-Defined Functions (UDFs) have become a cornerstone for many database developers seeking to encapsulate logic, improve code reusability, and enhance maintainability. This session embarks on a journey through the development and evolution of UDFs in Microsoft SQL Server, highlighting the pivotal updates, performance improvements, and new features introduced with each version.

Attendees will explore the foundational concepts of UDFs in SQL Server, understanding their initial design and limitations. The session will then navigate through subsequent versions, showcasing significant milestones such as inline table-valued functions, scalar UDF inlining, and enhancements in SQL Server 2019 and beyond. We will delve into practical examples, performance considerations, and best practices for optimizing UDFs in the modern SQL Server environment.

Whether you're a seasoned DBA, a developer, or someone looking to deepen your SQL Server expertise, this session provides valuable insights into leveraging UDFs to their fullest potential. Join us for a comprehensive overview and discover how UDFs have transformed and will continue to evolve to meet the needs of today's dynamic data-driven world