Tutorial

In this tutorial we'll create a database from scratch. We'll run DDL (Data Definition Language) SQL to create objects, then run DML (Data Manipulation Language) SQL to insert and modify data to see how we can view it and the effects on the database storage internals.

This tutorial will cover:

• Part 1 - Connecting and allocations

  • Connecting to a database
  • Viewing object allocations in the database
  • How to find object entry points

• Part 2 - Viewing pages

  • Viewing pages and records
  • The page header
  • How pages are linked together
  • Page types - Data, Index, LOB, and allocation pages

• Part 3 - Indexes

  • Navigating indexes with the Index view
  • Heaps, clustered indexes, and non-clustered indexes
  • Index root, leaf, and levels

• Part 4 - Query

  • Using the Query view - tracing a query and replaying it on the timeline
  • Views and layout - arranging the panes and watching the allocation map
  • The execution plan - the plan connected to the timeline
  • Scans vs seeks - the two access patterns on the Index view
  • Lookups - key lookups, covering indexes, and RID lookups
  • Joins - Nested Loops, Merge, and Hash compared
  • Log Records - tracing a delete down to the byte level

• Part 5 - LOB data

  • How VARCHAR(MAX) values are stored - in row, off page, and split
  • LOB pointers, roots, and data chunks

To follow along you'll need a SQL Server instance where you can create a new database, and permission to connect with the sysadmin role - see Permissions.

All of the SQL used in the tutorial is available as a single script: internals-viewer-tutorial.sql