- SQL Server Storage Engine
Description:
You've got your SQL Server under control. You know how to query critical dmvs when you're faced with a performance bottleneck. To get that extra bit of performance out of your database, you need to know not only what SQL Server presents to you, but also what's happening behind the curtains.
You might've seen DBCC IND presented before, and you might even have seen DBCC PAGE before. In this full day level 500 session we will look at those as well, but we'll take it much further. This session is not for the faint of heart, there will be bits & bytes!
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 a few demos performed in C# to demonstrate the parsing of data files outside of SQL Server.
Knowing the internals you'll be amazed at how many problems and questions you can answer by the use of deduction and logic.
A full day session
Note that this is a full day session. We will adhere to
the normal track schedule and go on break just as the other
sessions. However, I recommend that you stay on the track for the
duration of the day, to get the most from it. The individual slots
will not be isolated, meaning there will not be an introduction at
the beginning of each slot - they will build upon, and continue
from, the material presented in the previous slots. As such, you're
free to roam between the tracks, but you may find it difficult to
follow if you've missed some of the previous slots.
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.
- Special storage options
- I'll walk through the sparse column storage format - how it works internally, when you should use it as well as what limitations and gotchas you should be aware of.
- Next up I'll give you an introduction to the internals of row and page compression as well as tip on when and how to use it.
- 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.
Go to top