Infrastructure at your Service

Steven Naudet

Virtual Symposium SQL Server & Azure SQL – Session: Debugging without debugger

As a consultant at dbi services we can dedicate a significant part of our time to training and I took the opportunity today to attend an online conference.
In this blog post, I will present this event and one of the sessions that particularly interested me.

The event I followed is the Virtual Symposium – SQL Server & Azure SQL organized by SQLServerGeeks.
It’s a free event that can easily be followed on Zoom but also on YouTube by going to the organization’s channel: DataPlatformGeeks & SQLServerGeeks.
The event has three editions to suit your time zone – Americas which took place yesterday, EMEA today & APAC tomorrow.
Here is today’s agenda.

The session I have been waiting for the most is “Debugging without debugger: investigating SQL Server’s internal structures” by Hugo Kornelis.

Hugo Kornelis is well known in the SQL Server community for the sessions he’s doing regularly at many events. He publishes a lot of resources related to Execution plans. You can have a look at some of his writing on his website and blog:

The goal of this presentation is to demonstrate how an internal worktable is stored on disk by SQL Server without using the Windows Debugger. The session is demo-oriented.

Before going to the main topics Hugo starts with a demo of such a worktable with the “Window Spool operator“.
This first demo shows the execution plan of a query using a SELECT – OVER clause. Hugo goes into detail over each operator and describes how they cooperate together to build the final result set.

In the second demo, Hugo demonstrates how he used the SQL Server blocking mechanism to keep the query running “forever” while he can investigate the Window Spool worktable.
This is a fairly straightforward demo but it is explained very clearly and I find it quite nice to see SQL Server locking presented like this.

The third demo is about actually locating the internal worktable.
Observing the Page Free Space page of the TempDB database using DBCC PAGE and using sys.dm_os_buffer_descriptors, Hugo is able to find a newly allocated object that is assumed to be used by the Window Spool operator.
I found this part very interesting. Hugo references a book he uses as reference material for decoding DBCC PAGE and investigating anything internal: Inside Microsoft SQL Server 2005: The Storage Engine by Kalen Delaney.

Although the book was published in 2005 it might indeed contain internal information that didn’t change much over time. Nowadays most of the books aimed at DBAs don’t go into such details because this kind of knowledge is not directly useful for day-to-day DBA tasks.

I don’t know about the 2005 book but I own a copy of Microsoft SQL Server 2012 Internals also by Kalen Delaney. It is my go-to reference to understand how SQL Server works. This is a book I’d recommend for any DBA looking for internal information.

I’ll not describe the rest of the session as it’s going into detail inside data rows structures. I suggest you watch the whole session online on the YouTube channel when it will be available.
I really enjoyed this session, Hugo Kornelis is a great speaker, he managed to make a great presentation on a highly technical subject.

This event has many great speakers and sessions. I would recommend anyone interested in SQL Server to attend.
If you have some free time tomorrow have a look at the agenda of the APAC sessions you might find something you want to watch. And it’s free.

I’m looking forward to the next event big event organized by DataPlatformGeeks which is the Data Platform Summit scheduled in September 2021. I expect a lot of great content again.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Steven Naudet
Steven Naudet

Consultant