Besides doing a full day training on the SQL Server Storage Engine and MDF File Internals, I’ve also submitted two related sessions that present a subset of the training day material, in a much more dense format. Both sessions are very technical deep dives on the storage internals. If you think they sound interesting, I’d appreciate a vote :)
Knowing The Internals, Who Needs SQL Server Anyway?
You’re stuck on a remote island with just a laptop and your main database .MDF file. The boss calls and asks you to retrieve some data, but alas, you forgot to install SQL Server on your laptop. Luckily you have a HEX editor by your side!
In this level 500 deep dive session we will go into the intimate details of the MDF file format. Think using DBCC Page is pushing it? Think again! As a learning experiment, I’ve created an open source parser for MDF files, called OrcaMDF. Using the OrcaMDF parser I’ll go through the primary storage structures, how to parse page headers, boot pages, internal system tables, data & index records, b-tree structures as well as the supporting IAM, GAM, SGAM & PFS pages.
Has your database suffered an unrecoverable disk corruption? This session might just be your way out! Using a corrupt & unattachable MDF file, I’ll demo how to recover as much data as possible. This session is not for the faint of heart, there will be bits & bytes.
Demystifying Database Metadata
You know how to query sys.tables, sys.columns and perhaps even sys.system_internals_allocation_units to discover the metadata contents of a database. But where are these views getting their data from, and how do the core system tables relate?
Based on my work with OrcaMDF, an open source C# parser for MDF files, I will demonstrate how to parse the internal system tables. Using just the boot page as origin, we’ll discover how to traverse the chain of references that ultimately end up in references to the actual system table data, from where we can parse the data records.
Once we’ve got the system table data, I’ll demonstrate how to correlate the different tables to end up with the data we see in common system views.
Having only 10 votes, it’s tough to pick out the top 10 sessions from a list of almost ~150 sessions. Though it was a struggle, I ended up with the following votes:
Performance tuning from the field by Simon Sabin
Transaction Log Performance and Troubleshooting – Deep Dive by Chirag Roy
SQL Server Denali – Always On Deep Dive by Bob Duffy
HA/DR – Focus on Options, Comparisons and Interoperability by Chirag Roy
READPAST & Furious: Transactions, Locking and Isolation by Mark Broadbent
Preparation for Disaster by Steve Jones
Busted! A journey into the most common TSQL & Tuning myths by David Morrison
Turbocharge Database Recoverability Performance by Chirag Roy
Implementing Real-Time Data Warehouse by Sutha Thiru
Replication – Best Practices, Troubleshooting & Performance by Neil Hambly