SQL Server Internals and Architecture Overview

Name: SQL Server Internals and Architecture Overview
Rating: 4.2 (212 reviews)

A Real World Introduction to SQL Server Internals

Created byMike West

Last updated 2/2016

English

What you'll learn

At the conclusion of this course you'll understand how data is stored.
You'll also understand the anatomy of a Page. The fundamental unit of storage in SQL Server.
You'll learn how to correctly use several well known but undocumented commands to examine pages.

Course content

4 sections • 34 lectures • 1h 1m total length

Introduction1:35
What's this course about?

Let's find out in this intro.
Is this Course Right For You?1:11
Are you at the right place?

I want to make sure this course is right for you.
Course Downloads0:05
Use the code provided in the course for the examples and demos throughout the course.
Data Pages2:07
When storing data in SQL Server it's all about the page.

Let's learn about it in this lecture.
Row Structure1:32
The Two Core Data System Datatypes1:26
There are two kinds of system data types in SQL Server.

Let's define them in this lecture.
DBCC IND and DBCC PAGE2:23
We use two undocumented tools to get a forensic like look at our data structures.

Undocumented just means not supported, these are safe to run on any test box.
Row Overflow0:55
Does SELECT * really cause more IO?3:40
Let's take a look to see if SELECT * is really worse than Select column1, column1... etc.

I'll give you the tools in this lesson to find out either way.
Summary1:23
Let's conclude what we've learned in this section.
Terminology0:21
Not that many new terms but understanding what they do is critical to understanding the storage engine.

Let's go over these now.
Quiz

Extents and Allocation Map Pages1:56
Page are grouped together into 8 contiguous rows called extents.

Let's learn about these extents in this lecture.
How Pages Move1:12
Pages move between the buffer pool and the disk subsystem.

Let's find out the specifics of how they move in this lecture.
Partitions1:48
In this lesson we will take a brief look at partitions
Partition Example1:26
Let's demo partitions we learned about in the previous lesson.
Allocation Unit Detail1:22
What is an allocation unit? Let's find out in this lecture.
Heap Anatomy1:13
A heap is a table without a clustered index. Let's learn about it in this lesson
Heap Allocations2:08
Let's do a demo on how allocations work in a heap.
Clustered Index Anatomy1:44
Clustered indexes sort the data on disk. Let's learn about them in this lesson.
Clustered Index Allocations1:12
Let's take a brief look on clustered index allocations.
Clustered Index Demo1:14
Let's complete a demo on clustered indexes.
Non-Clustered Index Architecture1:47
Let's learn how non-clustered indexes work.
Alter Table Overhead3:09
Let's look at the overhead caused when altering a table.
Terminology0:49
Let's cover a few terms we learned in this section
Summary1:14
Quiz

Transaction Log Architecture Overview5:18
Let's detail the parts of the transaction log.
LSN Anatomy1:24
What is an LSN and what are the three sections of numbers that comprise it?
The Transaction Log Wraps Around1:05
Transaction logs don't continue forever. Let's learn how the wrap around.
An fn_dblog Overview4:07
This is a great function that gives us critical insight into what's in the transaction log.
Debunking Non-Logged Myths4:25
Truncate is indeed logged. Let's learn to what extent it's logged.
Simple Forensics Using fn_dblog1:48
Let's add another function to find out who dropped an object.
Dump the Contents of An Individual Transaction Log3:46
Let's use a unknown function to dump the entire contents of a physical transaction log.
Summary0:54
Quiz

Requirements

You'll need to download and install a copy of SQL Server 2012,2014 or 2016. The express edition will do.
You'll need a copy of the adventureworks database.
All though the tools we will be using are safe, you'll need an environment other than production to test on.

Description

SQL Server is a massive, complex product.

One DBCC command can have over one hundred thousand lines of code.

Once you’ve learned the basics… creating tables, inserting data, backing up databases… then what?

The daily routine of a database developer or DBA often doesn't involve internal structures.

I’ve been driving for 30 years but don’t know the parts to my car’s engine. Well, I’m not mechanic either.

If you want to understand how SQL Server really operates then you have to undertake a study of how the two core engines of SQL Server work with one another.

The storage engine stores data and the optimizer processes queries.

This course will provide you with a solid foundation in storage internals. We are going to cover how data is stored at a very low level.

You’ll learn the vernacular specific to the page and several new commands that will provide us with some deep insight into what any how SQL Server stores data.

We will cover the data row structure and the transaction log and by the end of this course you’ll understand how data moves in SQL Server.

This knowledge will help you troubleshoot SQL Server at a level most DBAs simply don’t grasp. We will look at examples and code syntax to determine what works and what doesn’t.

Any relational database is a collection of objects, the table being the most fundamental.

However, what they are and what they do are two different things. Relational databases have entities called transactions that transfer and manipulate data.

This course will focus on how and what these transactions are moving.

Thanks for you interest in SQL Server Internals.

I’ll see you in the course!!!

Who this course is for:

This is a mid level course for those with a solid foundation in SQL Server.
This is not an entry level course.
Everything you need for the course will be included in the course.

SQL Server Internals and Architecture Overview

What you'll learn

Explore related topics

Course content

An Introduction to Internals11 lectures • 17min

Core Internals14 lectures • 22min

Transaction Log Internals8 lectures • 23min

Conclusion1 lecture • 1min

Requirements

Description

Who this course is for: