Join me for a journey into the depths of the SQL Server storage engine. Through a series of lectures and demonstrations we'll look at the internals of pages, data types, indexes, heaps, extent & page allocation, allocation units, system views, base tables and nightmarish ways of data recovery. We'll look at several storage structures that are either completely undocumented or very scarcely so.  By the end of the day, not only will you know about the most important data structures in SQL Server, you'll also be able to parse every bit of a SQL Server data file yourself, using just a hex editor! There will be lots of hands on demos, a few of them performed in C# to demonstrate the parsing of data files outside of SQL Server.

 

Agenda

Introduction & expectations - I'll start out with an introduction of myself, the agenda and the project on which a lot of the content is based.
Pages

• We'll look the generic structure of pages, including an in-depth look at the page header structure. Then we'll continue with a detailed walkthrough of the common record as used on almost all page types.

Data types

• Once we know how records are stored, I'll go through a number of specific data type storage examples. We'll be looking at the storage format of several fixed length data types like bits, integers, dates & decimals. We'll followup with a look at LOB & SLOB data types, including a close look at row-overflow storage.

Indexes

• As indexes are one of the core storage structures, we'll spend some time looking at storage mechanisms like b-tree and linked lists. Using that as a foundation, I'll give a detailed overview of how clustered & nonclustered indexes are stored, modified and accessed.

Heaps

• Having looked at indexes, we'll dive into heaps - looking at the significant storage, modification and access differences between heaps and indexes. We'll get our hands dirty looking at forwarding of records, back pointers and RIDs and their impact on indexes.

Extents & page allocation

• With all the knowledge of pages, we're ready to look at how SQL Server tracks pages those pages. We'll look at how pages are linked to their owning objects and how SQL Server can quickly find out where to put new rows, empty pages, etc. We'll end it off by delving into allocation units and partitioning details.

System views & base tables

• At this point we're ready to take a stroll down dmv lane. We'll query the most important dmvs when it comes to internals, while connecting them to everything we've been through so far. Once we've got a comfortable overview of the dmvs, we'll look into where they get their data from - the internal base tables. From there we're going to take it up a notch and try to parse the base tables from scratch using just a boot page as a starting reference - simulating what SQL Server will do on startup.

Worst case data recovery

• Armed with the knowledge of the day, we're going to intentionally corrupt several databases and attempt to recover as much data as possible. Think worst case scenario - no backups, no logs. All we have is our knowledge of the storage details as well as a byte array.

Mark has worked extensively with SQL Server, primarily focused on performance optimization and internals. Besides SQL Server Mark is also proficient in the Microsoft .NET development stack with a decade of experience.

 

Fueled by his interest in the nitty-gritty details and feedback from an early presentation, Mark started the OrcaMDF open source project that aims to provide a completely standalone parser for SQL Server MDF files.

Mark is an active member of the Danish community, speaking avidly at local events & user groups and is recognized by Microsoft as a Microsoft Designated Information Provider. Furthermore he blogs actively at http://improve.dk.