SQL Server Internals and Architecture Overview

A Real World Introduction to SQL Server Internals
4.1 (25 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
224 students enrolled
Instructed by Mike West IT & Software / Other
$19
$20
5% off
Take This Course
  • Lectures 34
  • Length 1 hour
  • Skill Level Intermediate Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 2/2016 English

Course 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!!!

What are the 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.

What am I going to get from this course?

  • 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.

What is the target audience?

  • 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.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: An Introduction to Internals
01:35

What's this course about?

Let's find out in this intro.

01:11

Are you at the right place?

I want to make sure this course is right for you.

Article

Use the code provided in the course for the examples and demos throughout the course.

02:07

When storing data in SQL Server it's all about the page.

Let's learn about it in this lecture.

Row Structure
Preview
01:32
01:26

There are two kinds of system data types in SQL Server.

Let's define them in this lecture.

02: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 Overflow
00:55
03: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.

Article

Let's conclude what we've learned in this section.

Article

Not that many new terms but understanding what they do is critical to understanding the storage engine.

Let's go over these now.

Quiz
11 questions
Section 2: Core Internals
01:56

Page are grouped together into 8 contiguous rows called extents.

Let's learn about these extents in this lecture.

01:12

Pages move between the buffer pool and the disk subsystem.

Let's find out the specifics of how they move in this lecture.

01:48

In this lesson we will take a brief look at partitions

01:26

Let's demo partitions we learned about in the previous lesson.

01:22

What is an allocation unit? Let's find out in this lecture.

01:13

A heap is a table without a clustered index. Let's learn about it in this lesson

02:08

Let's do a demo on how allocations work in a heap.

01:44

Clustered indexes sort the data on disk. Let's learn about them in this lesson.

01:12

Let's take a brief look on clustered index allocations.

01:14

Let's complete a demo on clustered indexes.

01:47

Let's learn how non-clustered indexes work.

03:09

Let's look at the overhead caused when altering a table.

Article

Let's cover a few terms we learned in this section

Summary
Article
Quiz
10 questions
Section 3: Transaction Log Internals
05:18

Let's detail the parts of the transaction log.

01:24

What is an LSN and what are the three sections of numbers that comprise it?

Article

Transaction logs don't continue forever. Let's learn how the wrap around.

04:07

This is a great function that gives us critical insight into what's in the transaction log.

04:25

Truncate is indeed logged. Let's learn to what extent it's logged.

01:48

Let's add another function to find out who dropped an object.

03:46

Let's use a unknown function to dump the entire contents of a physical transaction log.

Summary
Article
Quiz
10 questions
Section 4: Conclusion
Final Thoughts and a Thank You!!!!!
Article

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Mike West, SQL Server Evangelist

I've been a production SQL Server DBA most of my career.

I've worked with databases for over two decades. I've worked for or consulted with over 50 different companies as a full time employee or consultant. Fortune 500 as well as several small to mid-size companies. Some include: Georgia Pacific, SunTrust, Reed Construction Data, Building Systems Design, NetCertainty, The Home Shopping Network, SwingVote, Atlanta Gas and Light and Northrup Grumman.

Experience, education and passion

I learn something almost every day. I work with insanely smart people. I'm a voracious learner of all things SQL Server and I'm passionate about sharing what I've learned. My area of concentration is performance tuning. SQL Server is like an exotic sports car, it will run just fine in anyone's hands but put it in the hands of skilled tuner and it will perform like a race car.

Certifications

Certifications are like college degrees, they are a great starting points to begin learning. I'm a Microsoft Certified Database Administrator (MCDBA), Microsoft Certified System Engineer (MCSE) and Microsoft Certified Trainer (MCT).

Personal

Born in Ohio, raised and educated in Pennsylvania, I currently reside in Atlanta with my wife and two children.

Ready to start learning?
Take This Course