Full Day SQL Internals, Optimization, and Indexing Deep Dive Pre-con

Speakers:

Bradley Ball, a Microsoft SQL Server MVP with almost 15 years of IT experience. Bradley spent 8 years working as a Defense contractor for clients such as the U.S. Army, The Executive Office of the President of the United States, and Publix Supermarkets. Currently the Data Platform Management Lead for Pragmatic Works and Microsoft VTSP. He has presented at many SQL Saturdays, for PASS SSUG’s, SQL Rally, DevConnections, SQL Live 360, & the PASS Summit 2011 - 2014. Published Author, including the recent SQL Server 2014 Pro Administrators Guide. He can be found blogging on http://www.sqlballs.com about SQL Server or anything else that interests him.

Gareth Swanepoel, a sysadmin-turned-SQL Server DBA. He has been working in the IT industry doing support and administration for over 23 years. He enjoys solving the complex problems that his clients encounter when performance tuning SQL Server or when deploying SQL Server in data warehouse environments. He is originally from South Africa and currently lives in the quiet outskirts of Jacksonville, Florida. He is an accomplished author and speaker. Current projects include various speaking engagements at user groups, SQL Saturdays, the PASS Summit and other technical conferences. He currently serves as one of the PASS Regional Mentors for the US SouthEast region.

Session Details:

Congratulations you’re a SQL Server DBA/Developer/BI Developer. How does SQL Server work? We have tables, views, and indexes. We write queries. How do the queries and the tables work together? Have you ever wondered? Why should you? Understanding how SQL Server works internally allows you to know how and when to use SQL Server to solve business problems. We need to understand the relational engine, index, transaction log, data, and locking internals. We need to know how these apply to business decisions that we make every day! Most importantly we need DEMOS!!! Join SQL Server MVP Bradley Ball & Gareth Swanepoel as we dive into the SQL Server Internals spanning from the Query Processor, through the Storage Engine, the Plan Cache and in between.

Outline:

1. Inside the Query Optimizer

When it comes to making your queries run you need to understand how SQL Server reads a query and how the Optimizer works and return our physical data. How constraints, statistics, and simplification allow us to write better queries and use advanced techniques for solving every day issues.

· Lab: Fixing the terrible, rotten, no good RBAR query

2. Deep Dive Data Internals

As a DBA we learn backwards. Most of the time you get a DBA job and you have Databases, that have tables, and in those tables you have data. Nobody tells you that we have records, stored on Pages, grouped in Extents, assigned by Allocation Bitmaps, with IAM Pages and Allocation Units that span Partitions and Filegroups. Today is your lucky day, we will start at the bottom and build our way up. We'll cover byte swapping, bitmaps, and dive a little into decoding Hex & binary. Working to give you a full understanding of database internals, and how you use them every day! · Lab: To Corrupt and Fix a Database

3. Index Internals

Why does SQL Server use an index for some queries and not for others? What makes a good index? How many indexes should I have? Have you ever asked these questions? When you want to understand an application you look at its core architecture. Underneath the covers SQL Server is just a C++ application. Together we will discuss how the application architecture of SQL Server works, and how to apply this logic to building the best indexes for your queries. · Lab: Building A Better Index

Break for Lunch

4. Dive into the Transaction Log

One of the easiest areas to bottleneck the performance of a SQL Server instance is in the configuration of the transaction logs. In this section, we'll look at the internals for transaction logs and some of the important considerations that need to be taken into account while working with SQL Server. You'll learn how transaction logs work and their potential impact on performance. Lab: VLF Fragmentation, Finding it and Fixing it · Lab: VLF Fragmentation, Finding it and Fixing it

5. Deep Dive into Locking Internals & Latches

This topic sounds scary, but it’s not. Regardless of whether you have a small database with only a few users, or if you have a large multi-tenant system you have Locking, Blocking, Latches, and Spinlocks. Understanding what they are and how they work is the first step in understanding how to troubleshoot them. Deadlocks are a problem, but they are a problem we can fix. In this module we will explain and investigate how to find and fix the problems that we see in our database every day.

· Lab: Fixing a Deadlock, with Your Hands Tied Behind Your Back

Bonus Lab: (if there is time) – Fixing Unbalanced Parallelism 3 Different Ways (Using Internals)