Debugging without debugger - investigating SQL Server's internal structures
Channel: Hugo Kornelis
Date: 2021-05-11

Have you ever wanted to know exactly how SQL Server stores data for temporary structures, such as for instance the spooled data in a Table Spool or Index Spool operator? No? I don’t blame you. It’s a bit like wanting to know who wattered the rubber tree that produced the rubber your tires are made off before you get in the car to drive to work. You really don’t need it.
But you might still WANT to know. I did. And I figured it out.

Without ever touching the debugger (I’m a simple soul, that stuff is much too complex for me!), I figured out how I could look at the exact storage structures SQL Server creates and uses to store data in Table Spool and Index Spool operators. If you are willing to commit to a fast-paced high-level internals session that is guaranteed to teach you exactly zero actually useful information, then this is the session for you.


Recommended links:
* Slide deck and demo code: https://sqlserverfast.com/presentations/presentation-resources/
* SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
* SQLServerFast Execution Plan Video Training: https://sqlserverfast.com/video/


Sections:
0:00 Introduction
3:03 The Window Spool operator
13:43 Problem #1: Structures released when query finishes
22:48 Problem #2: Finding the object(s)
43:44 Problem #3: Figuring out the structure
49:52 Problem #4: Reading and interpreting the data
1:01:23 Conclusions